How to loop only through files that don't exist in destination using an SSIS package?

I have a folder on the network with files and I'm moving the files from one folder to another folder. But I only want to move new files, so only copy over files that do not exist in the destination folder. How? I already have the for each loop container and a file system task. I'm using variables. Right now it copies all files from one folder to the next everytime the package is executed. Is there some sort of conditional tool that I can stick in there? I'm not really good at writing scripts so if that is the only solution I may need your help.

-------------Problems Reply------------

Here is one possible option that you can achieve this using Foreach Loop Container, Script task and File system task. Following example shows how this can be done. The example was created using SSIS 2008 R2.

Step-by-step process:

  1. Create two folders named Source and Destination in the path C:\temp\ as shown in screenshot #1.
  2. Place a sample file named Sample_File_01.txt in the folder path C:\temp\Source\ and leave the other folder C:\temp\Destination\ empty. The SSIS package will copy files from the Source folder to Destination folder only if the file already doesn't exist. Refer screenshots #2 and #3.
  3. On the SSIS package, create 7 variables as shown in the screenshot #4. Set the variable DestinationFolder to the value C:\temp\Destination\. Set the variable SourceFolder to the value C:\temp\Source\. Set the variable FilePattern to the value *.*. You can change the values of these variables according to your requirements.
  4. Select the variable SourceFilePath and open the Properties window by pressing F4 button. Change the property EvaluateAsExpression to True and set the property Expression to the value @[User::SourceFolder] + @[User::FileName]. Refer screenshot #5.
  5. Select the variable DestinationFilePath and open the Properties window by pressing F4 button. Change the property EvaluateAsExpression to True and set the property Expression to the value @[User::DestinationFolder] + @[User::FileName]. Refer screenshot #6.
  6. On the SSIS package's C*ontrol Flow* tab, place a Foreach Loop container and configure the properties of the container as shown in screenshots #7 and #8. Make sure that you select the radio button Name and extension on the Collection section.
  7. Within the Foreach Loop container, place a Script Task. Double-click on the Script task and click on the Edit Script button. Replace the Main() method inside the script task with the code given under the Script Task Code section. This code checks if the destination file already exists or not and then populates the boolean variable DoesFileExist accordingly.
  8. Within the Foreach Loop container, place a File System Task below the Script Task. Connect the Script task's success green arrow to the File System Task. Configure the File System Task as shown in screenshot #9.
  9. We need the File System Task to execute only if the file doesn't 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 Edit as shown in screenshot #10.
  10. Configure the Precedence Constraint as shown in screenshot #11. This checks if the variable DoesFileExist contains the value False, which means the file was not found in the destination.
  11. Once configured, the SSIS package should be like as shown in screenshot #12.
  12. Screenshot #13 shows the first package execution. During this execution, there were no files in the destination path C:\temp\Destination\. After the execution, the file Sample_File_01.txt has been copied from C:\temp\Source\ to C:\temp\Destination\. Refer screenshot #14.
  13. Screenshot #15 shows the second package execution. During this execution, no files were copied to the destination path C:\temp\Destination\. As you can notice, that the File System Task didn't execute because the Precedence constraint failed.

Hope that helps.

Script task code:

C# code that can be used only 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:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #2:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #3:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #4:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #5:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #6:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #7:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #8:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #9:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #10:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #11:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #12:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #13:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #14:

How to loop only through files that don't exist in destination using an SSIS package?

Screenshot #15:

How to loop only through files that don't exist in destination using an SSIS package?

Here's a potential solution with two assumptions:

  1. All files in the destination folder were moved by this package.
  2. The file names are unique.

You could add a file name logging step to the ETL.

Create a new log table in the database with a column for the file name. On each loop of the package write the file name to this table. Then you can use that 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 & the conditional check (could be a conditional in data flow, done in script task, etc) will depend on the design of your package, but it should accomplish what you're looking for.

Category:file Views:1 Time:2011-07-06

Related post

  • How to open file which is stored under Miscellaneous folder of a SSIS package from a Script Task? 2011-02-08

    I added solution related miscellaneous files into the SSIS package under the Miscellaneous folder, e.g. XML, Excel files. I need to open and read those misc. files in a SSIS Script Task, but I can't find the way either to read or getting the path of

  • Execute SQL files from SSIS package 2011-04-05

    I have a lot of *.sql script files and i want to incorporate them to my SSIS package. Is it possible to use these *.sql files as the source of Select/Insert/Update/Create/Alter/Drop statements on my package? The goal is to dynamically alter the scrip

  • How to move a text file after importing the data using SSIS package? 2011-11-22

    I have an SSIS (dtsx) package that imports data from a text file. How can I move the text file as a step within the SSIS package after importing the data? Is there a command line utility, or do I have to write a program to do this? --------------Solu

  • how to find a loop in the file system? 2009-03-22

    how to find a loop in the file system in Linux? i am indexing all files for making search fast(O(1))... i am using c programming language to implement by using the library functions in dir.h.... I can scan through entire file system but it goes in a

  • How can I loop through multiple files and process them using SSIS package? 2009-03-23

    We have a situation where we are importing a number of files with the same format, using the same SSIS package. The package is being loaded and executed from a .net assembly. Currently the package is being reloaded for each file. I would like to load

  • How do I loop through all files in a folder using C? 2009-08-13

    I want to remove a particular substring from all the file names in a directory: -- like 'XYZ.com' from 'Futurama s1e20 - [XYZ.com].avi' -- So basically I need to provide the method with a desired substring, and it has to loop through all file names a

  • In C, How to exit a loop based on file contents 2010-09-29

    I'm attempting to exit a while loop, based on file contents. File exists and is empty, or garbage filled at initialization of the program. I externally modify the file, and would like the loop to exit upon this happening based on how the file was mod

  • Add files to classpath or loops in batch files? 2010-12-14

    I have this script in BASH (linux) shells script that adds all *.jar files into the classpath automatically. Is there a way to do the in window's batch file or do I have to add all the files manually? CLASSPATH=. for file in ./libs/*.jar; do CLASSPAT

  • How do I loop through two files and combine the same? 2011-02-06

    I do have two text files and want to loop through both files then combine both line (line 1 of first test file and line1 of second text file. like that for thousands of lines) and do some function I am familiar with loop through one file and for that

  • Looping an audio file 2011-04-27

    I'm trying to generate and play a square wave. I'm generating the signal and then using track = new AudioTrack(AudioManager.STREAM_MUSIC, sampleRate, AudioFormat.CHANNEL_CONFIGURATION_MONO, AudioFormat.ENCODING_PCM_16BIT, minSize, AudioTrack.MODE_STR

  • How to change flat file source using foreach loop container in an SSIS package? 2011-08-02

    How can i change Flat file path using foreach loop container in SSIS I'm having flat file which is under c:\sourcefile name of the file is "Flat(Date)" for e.g Flat01aug2011.csv and every day it is generated by recent date so i need to picked up new

  • With R, loop over two files at a time 2011-10-14

    Hello my favourite coding experts, I am trying to loop through two files at a time in R: i.e. take one 'case' file and another 'control' file, create a graph and dump it into a pdf, then take another set of 2 files and do the same and so on. I have a

  • Bash while loop that reads file line by line 2011-12-20

    There are two ways of reading a file line by line that I want to discuss here: #!/bin/bash while read line do echo-e "$ line \ n" done <file.txt and #!/bin/bash exec 3<file.txt while read line do echo-e "$ line \ n" done So first version works

  • how to loop list of file names returned by find 2012-03-08

    x=$(find . -name "*.txt") echo $x if I run the above piece of code in bash shell, what I get is a string contains sever file names separated by blank, not a list. off course, I can further separate it by blank to get a list, but I'm sure there is a b

  • combine results from loop in one file in R (some results were missing) 2012-03-23

    I want to combine the results from a for loop into 1 txt file and I have written my code based on suggestion from this link combine results from a loop in one file There is one problem. I am supposed to get 8 results (row) but I only ended with only

  • Looping through a file and removing certain lines 2013-07-05

    I want to loop through a file and remove certain lines. Example file: test.txt a b c d What I have: FILE=/tmp/test.txt while read FILE do # if the line starts with b delete it otherwise leave it there ? done --------------Solutions------------- This

  • Write output of for loop to multiple files 2014-06-09

    I am trying to read each line of a txt file and print out each line in a different file. Suppose, I have a file with text like this: How are you? I am good. Wow, that's great. This is a text file. ...... Now, I want filename1.txt to have the followin

  • Loop through all files in a directory and its sub directory using Unix 2014-12-15

    I have to loop through all files in a directory using Unix and find out the files that are created from a timerange.This is the command I am trying . But somehow that does't list out all files in the directory. `hadoop fs -ls $a$slash$star | awk '$6F

  • Loop through all files in a directory? 2015-01-07

    FILES="$DIRECTORY"/* for f in $FILES do echo "file $f: " #ls "$f" done I'm trying to loop through a file to get each files information to be displayed separately. e.g. first file name size date edited option to go to next file. second file name size

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.137 (s). 11 q(s)