How to add dates dynamically to a text file with SSIS

Sometimes it's important to create text files and label them with a date at the end. Or you import text files and they contain dates in them. In DTS you would use global variables, and then dynamic properties to do this. However that has all changed in SSIS. There are no more dynamic variables properties in SSIS.

Case Example: You have a sales report which you send off to a client on a daily basis. In order for the client to know which file is for what day, he/she wishes that you label the text files with the date in the file name.

Example :Rpt_Product_Sales_yyyymmdd.csv so it can be something like Rpt_Product_Sales_20070107.csv

1) You must first create a variable. You need to declare it as a string. Here I created a variable called 'path' and declared it a string data type.

This is the place where you want to enter the path \\server1\file\location\Rpt_Product_Sales_










2) Set up your Data Flow in the Control Flow. In this example, I will be taking data from my DB and dumping it into a text file.


3) Next you will want to right click on your flat file connection and goto properties. After that on the properties box, you will need to locate 'Expressions' and hit the box to the right of it.




4) When you have click on the box to the right of 'Expressions' in the properties of the Flat file connection, you should get the screen below.




5) You will want to select 'ConnectionString' from the property side.


6) After that, you need to click on the box that is to the right of 'ConnectionString', under 'Expressions'. The expression builder box will show up. Here is where you will do the scripting to add dates in the file name.

First find the variable which you created in step 1. Drag that into the Expression builder workspace. That's the path and the file name which we created in the Flat File Connection Manager. After you dragged the variable down, you can hit 'Evaluate Expression' and it will show you the path and name. In our example it's c:\Rpt_Product_Sales_




Here is where we add the code.

@[User::path] +
(DT_STR,4,1252) DatePart("yyyy",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + ".csv"

This is what I entered into the work space. I wanted the file name to be Rpt_Product_Sales_yyyymmdd.csv. The above accomplishes the task. You will notice that I'm padding the left side of Month and Day with a leading '0' (zero) then using Right function to take 2 characters. I'm also using the DatePart function as well to get the year, month, and day from the getdate() function.

Enter the info and then click on 'Evaluate Expression' and if you have done it correctly you will have your dynamic file name with a date.

Hit okay and debug your SSIS package.
www.sqlnerd.com
If you have any questions, feel free to contact me via email www.sqlnerd.com colin@sqlnerd.com