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; } }