SSIS: Dynamic File Name for Flat File Destination

This blog will demonstrates how to give the dynamic file name for flat or csv  file in SSIS package  like DataFeed_{yyyyMMdd} .txt eg. DataFeed_20181212.txt

Data Flow

• Configure the Flat File Destination with dummy file name.

Flat File Connection Manager

• Go to Connection Manager and right click on Flat File Connection Manager

1. Click on Property
2. Set DelayValidation = True, by default it is False

Flat File Connection Manager Property

• Click on Expression Property and Set Expression Value for ConnectionString 

Expression Builder

Expression:

"C:\\temp\\dataFeed_"+(DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() )) +(DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() )) +(DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))+".txt"

·   (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() )) – returns Month

·   (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() )) – returns day

·   (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) – returns Year

  

The above expression generates file name dataFeed_MMDDYYYY.txt in C:\temp location.

Thanks for visiting and please leave your comment if it helps you