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.

create package

Step 2
Add Data flow task from SSIS Toolbox in control flow surface as shown in figure

DFT

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.

Source connection

We can select specific columns from the table from columns tab and see the preview of data as shown in figure.

prev

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

var1

Give the appropriate folder path to @FolderPath variable value.

Now, Follow the steps to give expression to FileName variable.

1. Click on the Expression exp button and it will take you to expression window where you need to give the expression to generate the file name in appropriate format.

var2

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"


var exp

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.

flat1

Choose the format of the destination flat file.

flat2

Give Flat file connection manager name and click on Browse as shown in figure below.

flat3

Select proper Folder location for storing output flat files. Refer image below.

flat4
Give appropriate file name and select file type as “.CSV”
Select the check box to set first column as header as shown in figure.

flat5
Go to flat file manager properties shown below

flat prop

select expression from properties window

prop1
Select Connection string as property and give @FileName variable value to the expression for the same.
Refer image.

connection string

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.

run

Check the destination folder and files that are generated in a required format.

output

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.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: