Click here to Skip to main content
13,449,326 members (45,877 online)
Click here to Skip to main content
Add your own
alternative version


18 bookmarked
Posted 30 Jul 2009

SSIS Package and Variables

, 30 Jul 2009
Rate this:
Please Sign up or sign in to vote.
Using Variables in SSIS Package to dynamically set flat file names


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.


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" 


"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:



  • 30th July, 2009: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

You may also be interested in...

Comments and Discussions

GeneralMy Vote of 5 Pin
johnclark6410-Aug-09 17:03
memberjohnclark6410-Aug-09 17:03 
GeneralRe: My Vote of 5 Pin
Robin_Roy17-Aug-09 20:55
memberRobin_Roy17-Aug-09 20:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03-2016 | 2.8.180318.3 | Last Updated 31 Jul 2009
Article Copyright 2009 by Robin_Roy
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid