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