asked
ForEachLoop in SSIS and bad file list
In SSIS package I am traversing the directory and scanning the files using the for each loop task.Wrote a script task to capture bad file but it is being overwritten every time in the loop than appending to the variable.How to get a list of bad file after traversing the For Each Loop.Like declare a variable in the beginning and retaining it.
declare an object variable .. and with in the for each loop use a c# script to add the bad files names to the object as a datatable or list. after the foreach loop read the object and write to a table / file or anywhere you need.
As you suggested the script tasks are given below still it is storing last value in the object variable and getting 'Object is not an ADODB.RecordSet or an ADODB.Record.' error For loop script task: public void Main() { // TODO: Add your code here //Dts.TaskResult = (int)ScriptResults.Success; String fileName = Dts.Variables["User::varFileName"].Value.ToString(); List fileNames = new List (); fileNames.Add(fileName); MessageBox.Show(fileName); Dts.Variables["User::varBadFilesList"].Value = fileNames; //MessageBox.Show(Dts.Variables["User::varBadFilesList"].); } After for loop script task: public void Main() { // TODO: Add your code here //Dts.TaskResult = (int)ScriptResults.Success; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(); DataTable dt = new DataTable(); List filenames = (List )Dts.Variables["User::varBadFilesList"].Value; da.Fill(dt, Dts.Variables["User::varBadFilesList"].Value.ToString()); //da.Fill(dt, (List )Dts.Variables["User::varBadFilesList"].Value.ToString()); // MessageBox.Show(dt.Rows.Count.ToString()); }
18 People are following this question.
