CODEWITHSUNDEEP
visual-studiossis
SeanFlynn
SeanFlynn

Reputation: 453

Add datestamp to a .txt file in an SSIS Package?

I'm using Visual Studio 2005 to build a package that will deposit a flat file (.txt) into a specified location. The package will run every weekday. Currently, the file that gets deposited is just called summary.txt, and the file is simply overwritten each day for new content. What I'd like to do instead is change the name of the file to summarymmddyyy.txt, and just add a new file to the location everyday with the date of deposit. So, today's would be summary12062010.txt, tomorrow's would be summary12072010.txt, etc. Is there a way to do this within an SSIS package? Really, all I'm doing is a simple data flow that does a select statement from a SQL DB source to this flat file - nothing too fancy. Thanks!

Upvotes: 2

Views: 5269

Answers (1)

bobs
bobs

Reputation: 22184

You can use a variable that is evaluated as an expression to create the file name.

Add a new SSIS variable to your package. Select the new variable in the Variables window and view properties (press F4). Set the EvaluateAsExpression property to True and enter the following for the Expression property value

"summary" + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("mm", GETDATE())), 2) + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("dd", GETDATE())), 2) + (DT_STR, 4, 1252) (DATEPART("yyyy", GETDATE())) + ".txt"

This expression uses functions to extract the month, day and year values, and includes the values to create the name based on today's date.

Select the connection manager for your flat file. View properties (press F4). Select the Expressions property and click the elipsis (...) button. In the Property Expression Editor select the ConnectionString property. And enter the name of your new variable in the following format:

@[User::MyVariable]

If you need to include the path, create the expression like this:

"C:\MyFiles\" + @[User::MyVariable]

If you prefer, you can avoid the variable and put the entire expression in the ConnectionString property:

"C:\MyFiles\" + "summary" + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("mm", GETDATE())), 2) + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("dd", GETDATE())), 2) + (DT_STR, 4, 1252) (DATEPART("yyyy", GETDATE())) + ".txt"

Upvotes: 3

Related Questions