How to loop only through files that do not exist at the destination using the SSIS package?

Question

I have a folder on the network with files, and I move files from one folder to another. But I only want to move the new files, so copy only the files that do not exist in the destination folder. How? I already have a for each loop container and a file system task. I use variables. Now it copies all the files from one folder to another every time the package is executed. Is there any conditional tool that I can insert there? I am not very good at scripting, so if this is the only solution, I may need your help.

Answer 1

Here is one of the options you can achieve using the Loop Container , Script, and File System Task . The following example shows how to do this. An example was created using SSIS 2008 R2.

Step by step:

• Create two folders named Source and Destination in the path C:\temp\ , as shown in screenshot # 1 .

• Place the sample file named Sample_File_01.txt in the path to the folder C:\temp\Source\ and leave another empty folder C:\temp\Destination\ . The SSIS package copies files from the Source folder to the Destination folder only if the file no longer exists. See Screenshots # 2 and # 3 .

• In the SSIS package, create 7 variables, as shown in screenshot # 4 . Set the DestinationFolder variable to C:\temp\Destination\ . Set the SourceFolder variable for the value C:\temp\Source\ . Set the FilePattern variable to *.* . You can change the values ​​of these variables to suit your requirements.

• Select the SourceFilePath variable and open the Properties window by pressing the F4 button. Change the EvaluateAsExpression27> property to True and set the Expression property to @[User::SourceFolder] + @[User::FileName] . See screenshot # 5 .

• Select the DestinationFilePath variable and open the Properties window by pressing the F4 button. Change the EvaluateAsExpression27> property to True and set the Expression property to @[User::DestinationFolder] + @[User::FileName] . See Screenshot # 6 .

• On the tab of the ISIS package C ontrol Flow, place the Foreach Loop container and configure the properties of the container, as shown in screenshots # 7 and # 8 . Make sure you select the Name and extension radio button in the Collection section.

• In the Loose Loop container, place the Script Task . Double-click the Script task and click the Edit Script button. Replace the Main () method inside the Script task with the code specified in the Script section of the Task Code . This code checks if the target file exists or not, and then populates the DoesFileExist boolean .

• In the Loop Loope container, place the File System Task below the Script Task. Connect the green arrow for Script success to the File System Task. Configure the file system task as shown in screenshot # 9 .

• We need a file system task to execute only if the file does not exist in the destination path. So, we need to change the connector between the Script Task and the File System Task. Right-click on the green connector and select "Modify" as shown in screenshot < 10 .

• Set the priority restriction as shown in screenshot # 11 . This checks if the DoesFileExist variable contains False , which means that the file was not found at the destination.

• After configuration, the SSIS package should look like the screenshot # 12 .

• Screenshot # 13 shows the execution of the package first . During this execution, there were no files on the destination path C:\temp\Destination\ . After execution, the Sample_File_01.txt file was copied from C:\temp\Source\ to C:\temp\Destination\ . See Screenshot # 14 .

• Screenshot # 15 shows the execution of the second package. During this execution, the files were not copied to the destination path C:\temp\Destination\ . As you can see, the File System Task not executed because the use-case restriction failed.

Hope this helps.

Script task code:

C # , which can only be used in SSIS 2008 and above .

 public void Main() { Variables varCollection = null; Dts.VariableDispenser.LockForRead("User::DestinationFilePath"); Dts.VariableDispenser.LockForWrite("User::DoesFileExist"); Dts.VariableDispenser.GetVariables(ref varCollection); varCollection["User::DoesFileExist"].Value = Convert.ToBoolean(System.IO.File.Exists(varCollection["User::DestinationFilePath"].Value.ToString())); Dts.TaskResult = (int)ScriptResults.Success; } 

Screenshot # 1:

Screenshot 2:

Screenshot 3:

Screenshot 4:

Screenshot No. 5:

Screenshot No. 6:

Screenshot No. 7:

Screenshot # 8:

Screenshot No. 9:

Screenshot No. 10:

Screenshot No. 11:

Screenshot No. 12:

<T411>

Screenshot No. 13:

Screenshot No. 14:

Screenshot No. 15:

Answer 2

Here's a potential solution with two assumptions:

• All files in the destination folder were moved by this package.
• File names are unique.

You can add a file name registration step to the ETL log.

Create a new log table in the database with a column for the file name. In each package loop, write the file name to this table. You can then use this as a simple lookup table that is used by some conditional logic in the ETL to check if the file name being processed matches an existing file.

Exactly how you implement the logging step and conditional check (may be conditional in the data stream executed in the script task, etc.) will depend on the design of your package, but it should fulfill what you are looking for.