store string values into object and use later

Question

I would like to store file names that are processing into variable? and then later using this variable (file names) to move these files into proccessed folder. I am using foreach loop where dataflow task process each file and storing file names into object in script task with in loop.

string filename = Dts.Variables["User::FILE_NAME"].Value.ToString();
object proccessed_file_name;
proccessed_file_name += filename;
Dts.Variables["User::FILE_NAME"].Value = proccessed_file_name;

Pls help me store string values into object and use later in the package.

score 0 · Answer 1 · answered Jun 19 '16 at 16:16

You can do it without the script task and only 1 variable User::FILE_NAME.

Use the foreach loop on a directory, populate the FILE_NAME from that task. Process the file via your Data Flow Transformation. Then move the file. Then simply let your foreach loop continue. By moving the files incrementally you will know exactly what has succeeded and what has failed and will be able to restart your process without having to rollback the files that were processed correctly and starting from scratch.

If you want to wait to move everything, you could do a 2 stage move. 1 in the same logic as above that simply renames to .processed or something at the original directory. Then after that loop finishes add another foreach filtering to processed files then use same FILE_NAME variable and move your files.

No need for a script task in either situation.

If you really want to use a script task. Put the script task after your DFT in your foreach loop. Have another variable that is object type which will hold a datatable or something. Populate that DataTable with the file name etc. Then after your foreach you will need another script task that will load the table and get the file name you want to process. Honestly at that last step it is easiest to use system.io and move the files within the script.

How would you use the Variable that is a datatable something like (and I am writing this freely so this will likely need additional steps and error checking.)

To Populate File Names into DataTable, make File_Name variable readable, and DataTableVariableName read/writeable to the script task then start with something like:

you may actually have to test for null of user variable.value before this line I am not positive.

system.data.datatable dt = (system.data.datatable)Dts.Variables["User::DataTableVariableName"].Value;

if (dt == null)
{
   Write code to create your datatable
}
Add new DataRow to table -- lots of results on web about this and creating datatable.

Now you have your datatable. After foreach loop add a script task to move the processed files.

Again start with retrieving the table from variable

system.data.datatable dt = (system.data.datatable)Dts.Variables["User::DataTableVariableName"].Value;

then loop through datatable and I would suggest move the files here as well.

foreach (dt datarow in dt.rows)
{
  typically I like to suggest copying and then deleting the source file depending on how you over write. so you would do something like.
If (system.io.file.Exists(dr.["FileNameRowLable"].ToString())
{
   system.io.file.copy(source, destination);
   if (file.exists(destination))
   {
      file.delete(source);
   }
}
}

I think you will be happier sticking with moving the file in your foreach loop so you wont need the script task.

store string values into object and use later

1 Answers1