ʞᴉɯ
ʞᴉɯ

Reputation: 5594

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

Upvotes: 17

Views: 59586

Answers (4)

Vikramsinh Shinde
Vikramsinh Shinde

Reputation: 2878

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

Upvotes: 38

Rajeev Tiwari
Rajeev Tiwari

Reputation: 19

you can easily give the dynamic file name for flat or csv file in SSIS package like DataFeed_{yyyyMMdd} .txt eg. DataFeed_20181212.txt. simply go to flat file connection manager property and define the expression value and in another way you can define the package level variable and this variable you can use in expression and as per environment you can able to define folder location by using configuration file .dtsconfig file

more details @ ssis dynamic file name for flat file

Upvotes: 0

Kobbe
Kobbe

Reputation: 845

If you don't have SSDT and thus can't edit this with a GUI here is how you edit the SSIS package directly:

Before:

<DTS:ConnectionManager
      DTS:refId="Package.ConnectionManagers[DestinationConnectionFlatFile]"
      DTS:ObjectName="DestinationConnectionFlatFile"
      DTS:DTSID="{C69365C4-EF12-4606-980B-E8862EE997A4}"
      DTS:CreationName="FLATFILE">
      <DTS:ObjectData>

After:

<DTS:ConnectionManager
      DTS:refId="Package.ConnectionManagers[DestinationConnectionFlatFile]"
      DTS:CreationName="FLATFILE"
      DTS:DTSID="{C69365C4-EF12-4606-980B-E8862EE997A4}"
      DTS:ObjectName="DestinationConnectionFlatFile">
      <DTS:PropertyExpression
        DTS:Name="ConnectionString">"C:\\Exportdir\\Filename_"
 + (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"      
      </DTS:PropertyExpression>
        <DTS:ObjectData>

Upvotes: 1

Kritner
Kritner

Reputation: 13765

Select your file connection in the Connection Managers, go to the Properties and click on the (...) beside expressions.

In the editor select ConnectionString from the Property column. In the Expression text box, you can enter something like "rootNameOfFile" + (DT_WSTR, 50)(DT_DBDATE)GETDATE() + ".csv"

Evaluate your expression to make sure you're getting what you expect, and voila!

Upvotes: 2

Related Questions