Power shell - save sql query result to file

 1. Simple script to run query and save it to file

$fileName = 'FILE_PATH'

$SQLServer = "SERVER_NAME"  

$SQLDBName = "DATABASE_NAME"  

$userid ="USER_NAME"  

$password = "PASSWORD"   

$delimiter = ";"

$SqlQuery = "SELECT * FROM TABLE_NAME";

#SQL Query 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection  

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $userid; Password = $password;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand  

$SqlCmd.CommandText = $SqlQuery  

$SqlCmd.Connection = $SqlConnection  

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  

$SqlAdapter.SelectCommand = $SqlCmd   

#Dataset  and save to file

$DataSet = New-Object System.Data.DataSet  

$SqlAdapter.Fill($DataSet)  

$DataSet.Tables[0] | export-csv -Delimiter $delimiter -Path $fileName -NoTypeInformation

SSIS SQL Server Integration Services - execute query based on flat file csv datasource

SSIS SQL Server Integration Services - execute oracle query based on flat file csv datasource

1. Create variable to store file path

f.e. 

FilePath with value C:\file.txt

File.txt will be ";" separeted flat file. I will use second column of file with material numbers.

2. Create variable to store query you wish to execute

f.e Query which will later be referenced as User::Query

3. Create script task to read file contents and prepare query

4. Create Execute SQL Task to run query created inside previous script task, using variable User::Query

// Script

public void Main() { // TODO: Add your code here //get file path string FilePath = Dts.Variables["User::FilePath"].Value.ToString(); string temp; string email = "przemyslaw.wawrzyczek@valeo.com"; string content = ""; int i = 0; bool contentExists = false ; // Open file reader StreamReader sr = new StreamReader(FilePath); // Loop while end of file, read line while ((temp = sr.ReadLine()) != null) { // Split into columns string[] columns = temp.Split(';'); // Skip header if (i > 0) { //Get distinct values if (!content.Contains(columns[1])) { // add value content += "'" + columns[1] + "',"; email = columns[12].ToString(); contentExists = true; } } else { // open bracket content = "("; } i++; } //remove last coma content = content.TrimEnd(','); //close bracket content += ")"; if (contentExists) { // create query and pass it into ssis variable Dts.Variables["User::Query"].Value = "BEGIN DELETE FROM TABLE WHERE material_number in " + content + "; COMMIT; END;"; Dts.Variables["User::Email"].Value = email; content = content.Replace("'", ""); Dts.Variables["User::EmailSubject"].Value += " " + content; Dts.TaskResult = (int)ScriptResults.Success; } else { Dts.Variables["User::Query"].Value = ""; Dts.TaskResult = (int)ScriptResults.Failure; } }

SSIS SQL Server Integration Services - limit number of rows

SSIS SQL Server Integration Services - limit number of rows

SSIS Microsoft Sql server

1. Use Row Sampling component from toolbox

2. Double click at Row Sampling component to set number of rows

 3. Set Sampling selected output to redirect selected number of rows

4. Set Sampling unselected output to redirect all rows available