SSIS – Generate dynamic flat files using Variables and Expressions
In this article we will learn how to generate dynamic flat files in SSIS.
Using Variables and Expressions, we can generate dynamic Flat files in a particular format. We can use this concept when there is a requirement of generating files in a regular interval without overwriting the existing files.
Lets understand the requirement and its solution.
Requirement:
• Dynamically generate flat files with "FileName_YYYYMMDD_HHMM" format from a table source location.
• It should not overwrite the existing file and always generate a new file in a folder with the above format.
Solution
Step 1
Open BIDS and create new project.
Create new project in BIDS choosing Integration Services and give project name as shown in figure.
Step 2
Add Data flow task from SSIS Toolbox in control flow surface as shown in figure
Step 3
Double click on Data Flow Task which will take you to the data flow design window.
Now, add OLE DB Source from SSIS Toolbox and configure it with appropriate database connections.
We can select specific columns from the table from columns tab and see the preview of data as shown in figure.
Step 4
In order to generate the dynamic file in a specific format, we need to create variables and expressions.
Lets create two Variable as shown in figure below.
• @FolderPath
• @FileName
To open variable window:
View Menu ---> Other Window ---> Variables
SSIS Menu ---> Variable
Note:
While creating the variable, the scope of the variable should be Package Name as shown in figure
Give the appropriate folder path to @FolderPath variable value.
Now, Follow the steps to give expression to FileName variable.
1. Click on the Expression 
button and it will take you to expression window where you need to give the expression to generate the file name in appropriate format.
We need to write the expression to generate the file in “FileName_YYYYMMDD_HHMM" format.
Write the following expression in expression window@[User::FolderPath]+"Test_"+(DT_WSTR, 2) MONTH( GETDATE() ) + "-" +
(DT_WSTR, 2) DAY(GETDATE()) + "-" +
(DT_WSTR, 4) YEAR(GETDATE()) + "-" + (DT_WSTR, 2) DATEPART("hh", GETDATE()) + "-" +
(DT_WSTR, 2) DATEPART("mi", GETDATE()) +".csv"
2.You can evaluate your expression to check whether the file name is generating in proper format or not. You can edit the expression as per your need.
Step 4
Now add Flat file destination from SSIS toolbox and configure it as shown in figure.
Double click on flat file destination and click New to create flat file connection.
Choose the format of the destination flat file.
Give Flat file connection manager name and click on Browse as shown in figure below.
Select proper Folder location for storing output flat files. Refer image below.
Give appropriate file name and select file type as “.CSV"
Select the check box to set first column as header as shown in figure.
Go to flat file manager properties shown below
select expression from properties window
Select Connection string as property and give @FileName variable value to the expression for the same.
Refer image.
You can drag and drop the FileName variable into expression window.
Click OK and go to Control Flow Window
Run the Package and see the file generated in specified folder location in proper format.
Check the destination folder and files that are generated in a required format.
Files are generated in “FileName_YYYYMMDD_HHMM" format.
Hence, Everytime when the package runs, it will generate a new file with specific File name format and it will not overwrite the existing file.
This approach can be used when you need to generate the files in a regular interval without overwriting the existing files.
Points to remember
• While creating the Variables, you need to check the scope of the variable.
• Scope can be entire package or it can be restricted for a particular Task as well.
• Connections should be given properly.
• You can use any other transformation tasks in between the source and destination as per your need.