SSIS Package and Variables

Introduction

One of my project team members came up with a requirement, wherein she was developing a SSIS package to generate text file as output. The source is a Microsoft-SQL Server 2005 database. She was using a T-SQL Query to read data from the data source. The text file should be saved in file system with dynamic name. For example, File<yyyymmdd>.txt.

Solution

The Package consists of DataFlow tasks to generate, Header, Body and Footer for the flat file (since the requirement demands the flat file to have 3 sections: header, body/details, footer with different set of data from the database) and a File System Task to set the name of the file dynamically.

Define a variable with the package scope. To create a variable, right click on the Control Flow workspace and click on Variable. It will open the variables tab and from there, we can create variables.
Go to the properties of the variable and click on the button beside the Expression property.

The Expression Builder dialog box opens up. Here we need to build the expression for the dynamic file name.

In the Expression Text Box, put in either of the following expressions:

"C:\\FlatFile\\File" + SUBSTRING( (DT_WSTR,30)GETDATE() , 1, 4 ) + SUBSTRING
( (DT_WSTR,30)GETDATE() , 6, 2 ) + SUBSTRING( (DT_WSTR,30)GETDATE() , 9, 2 ) + ".txt" 

OR

"C:\\FlatFile\\File" + SUBSTRING( (DT_WSTR,30)GETDATE() , 1, 4 ) + SUBSTRING
( (DT_WSTR,30)GETDATE() , 6, 2 ) + SUBSTRING( (DT_WSTR,30)GETDATE() , 9, 2 ) + ".txt" 

There is a button in the Expression Builder dialog box called “Evaluate Expression”. Click on the button to check the file name and to validate whether the expression is correct or not. And then, click on OK. Refer to the following screenshots:

Now finally, use the variable in the File System Task (Rename File). Double click on the File System Task in the Control Flow to open up the File System Task Editor and in the Destination Connection section, set IsDestinationPathVariable to True and select the defined variable corresponding to the DestinationVariable property. Refer to the following screenshot:

History

• 30^th July, 2009: Initial post