SSIS – Archiving a file with a variable name

1. Setup the variables

  1. An archive path variable of data type string. Use text below as the Expression. Modify the text to your desired folder location and your desired file name.

"\\\\dwctSRV01\\c$\\Corefocus\\IMISTEST\\IMISTESTARCHIVE\\Integration_" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + "_" + RIGHT("0" + (DT_WSTR,2)DATEPART("hh",GetDate()),2) + RIGHT("0" + (DT_WSTR,2)DATEPART("mi",GetDate()),2) + ".csv"

2.  A source directory variable of data type string. The expression should contain the starting location of the file.

"\\\\dwctSRV01\\c$\\Corefocus\\IMISTEST\\"

3.  A source file name variable of data type string. The expression should be blank with the value as any string as it will be overwritten later.

4. A variable that will combine the directory and file name as data type string. An example expression is below.

@[User::Source_Dir]+ @[User::Source_FileName]

 

2. Setup the design space

  1. Drag in a For Loop Container into the design space.

      2.  Double click the For Loop Container and go to Collections.
a. Create an Expression of property Directory with the expression of your source directory variable.

b. Add the folder the file will start in and add what you want to pick up. The below example will pick up anything ending in .csv

c.  Click Variable mappings. Choose your source file name variable and make sure the Index is 0.

3.  Drag a Flat File source into the For Loop Container.


4. Double click the Flat File Source and create a new Flat file connection manager.
a. Give it a suitable name.
b. Add anything to the file name value (If it does not let you add something here change the ConnectionString property in the connection manager properties directly)
c. Set up the format on how your file is separated.

 

5. Add a new File System Task and link the Flat File Source to it. Double click the File System Task.
a. Set IsDestinationPathVariable to True
b. Set DestinationVariable to your Archive Path variable
c. Give the task a name
d. Give the task a description
e. Set the operation to Rename file
f. Set IsSourcePathVariable to True
g. Set SourceVariable to your Source File Name variable

 

6. Create another File System Task and link it to the previous one. Double Click the File System Task
a. Set the Operation to Delete file
b. Give the task a name
c. Give the task a description
d. Set IsSourcePathVariable to True
e. Set SourceVariable to your Source File Name variable

 

 

If you have any questions please comment below.

Leave a Reply

Your email address will not be published. Required fields are marked *