SSIS Package to Create a Tab Delimited Flat File with current Date Time from SQL Server Table

How to create Dynamically a Tab Delimited Flat File with current Date Time from SQL Server Table in SSIS
Scenario :
Suppose we have a Data in the SQL Server Table which will update frequently. This data needs to be export to Flat File in Tab Delimited Format with Current Date and Time when ever we run a Package.

Source Data :
The source data in the Table as follows..

This data needs to be export to Tab Delimited Flat files as follows..
Output(Destination Data ) :

Now we have to design the SSIS Package to accomplish the above scenario as follows :
Phase - 1 : Create the Required Variables :
We have to Create the four variable as follows..
DestFolderPath = This is a Static variable to which we assign the path of the Destination Folder where we create the FlatFile from SQL Table.
Eg : T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\

DestFileName = This is a Static variable to which we assign a sample File Name, the name pattern by which we are creating the File Names. This name will Change by Full Name in RunTime.
Eg : EmpDetails

DateTime : This is Dynamic variable where we calculating the Date Time value string which we assign to the File Name. This can be created using the following Expression.

REPLACE(REPLACE( REPLACE(SUBSTRING((DT_WSTR,50)GETDATE(),1,19),"-",""),":","")," ","_")
Eg : 20171115_163356

DestFileFullPath : This is Dynamic variable where we calculating Full File Name with Path which we use as a Connection String to the Flat File Name. This can be created using the following Expression.

@[User::DestFolderPath]+ @[User::DestFileName]+"_"+ @[User::DateTime]+".txt"
Eg : EmpDetails_20171115_163356

Phase - 2 : Design the Data Flow Task :

1. Add the Execute SQL Task  inside the DataFlow and Connect to Source Table

2. Next add the Flat File Destination then Connect to Execute SQL Task

3. Next set the Flat File Properties as follows
A) Right Click on Flat File Destination > then go to Edit

B) Next on Flat File Destination Editor>Connection Manager click New

C) Next select the Flat File Format as "Delimited"

D) Next click on the "Browse" to select the Destination FileFolder

From the Browse Window we don't select any File but we will give some Name(Eg. EmpDetails) and say open from the Destination Folder.

Now the General Tab properties of the Connection Manger as follows.
Here the FileName will change in the Run Time.

E) Next select "Column Delimiter" as "Tab {t}" in Connection Manager >Columns tab

F) Next from Connection Manager >Advanced Tab, You can rename the Columns if you want to display as you wish
Here I have changed source colulmn "Emp_Name" to "EName" to display in Destination.

G) Next do the Column mapping in the Flat File Connection Editor > Mappings
Please note that the same Column names will be mapped automatically.

Now the Flat File Connection is Ready

H) Next we have to make the FlatFileDest Connction String dynamic using Expression.

This is a very important to save the Destination File with Current Date Time.

FlatFileDest>Properties>Expressions>ConnectionString=@[User::DestFileFullPath]

4. Finally Run the SSIS Package

--------------------------------------------------------------------------------------------------------

Output :

Now the Tab Delimited Output files will be created with Date Time name suffix as follows :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM , Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------