question

ask.sqlservercentral avatar image
ask.sqlservercentral 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.
ssis2012script-taskforeach-loop
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aRookieBIdev avatar image
aRookieBIdev answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ask.sqlservercentral avatar image
ask.sqlservercentral answered
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()); }
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.