sql-serverssissql-server-2012

Sql Server SSIS package Flat File Destination file name pattern (date, time or similar)?

I'm scheduling a SSIS package for exporting data to flat file.

But i want to generate file names with some date information, such as foo_20140606.csv

Is it possible?

Thanks

Solution

  • With the help of expressions you can make connection dynamic.

    Select your flat file connection from Connection Managers pane.

    enter image description here

    In Properties pane (after clicking the flat file connection manager once, press F4 or go to the pane on the right side), click on Expressions(...).

    enter image description here

    Then choose ConnectionString Property from drop down list and in Expressions(...) put your expression and evaluate it.

    Expression build -

    For day : (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() ))
For month: (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() ))
For Year:  (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))

    Example expression(you need to tweak as per your requirement) -

    "E:\\Backup\\EmployeeCount_"+(DT_STR,4,1252)DATEPART( "yyyy" , getdate() ) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2) +".txt"

    which is giving E:\Backup\EmployeeCount_20140627.txt as value.

    Please note - You need a working flat file connection so first create flat file connection whose connectionString property is then going to be replaced automatically by expression.

    You can follow these step by step articles as well.

    enter image description here

    You need to escape the backslash by doubling each backslash in the path:

    enter image description here

    Instead of filling the ConnectionString from an expression in the Properties menu, you could and often should fill it from a variable instead so that anything you hardcode or automate by expression is in one overview under "Variables".

    Go to "Variables" in the SSIS menu tab:

    enter image description here

    and make a new user variable:

    enter image description here

    that you take as the input for the ConnectionString expression:

    enter image description here