Click here to Skip to main content
Click here to Skip to main content
Go to top

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

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:

P1.jpg

P2.jpg

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:

P3.jpg

History

  • 30th July, 2009: Initial post

License

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

Share

About the Author

Robin_Roy
Other Brilliance Information Sdn Bhd
Malaysia Malaysia
Working as a Senior Consultant with Brilliance MSC, Malaysia.
Love to evaluate new technologies and implement the same.
Believe in sharing knowledge.

Comments and Discussions

 
QuestionDeleting the path from the fileName PinmemberMember 102352932-Sep-13 7:41 
GeneralMy Vote of 5 Pinmemberjohnclark6410-Aug-09 17:03 
GeneralRe: My Vote of 5 PinmemberRobin_Roy17-Aug-09 20:55 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140921.1 | Last Updated 31 Jul 2009
Article Copyright 2009 by Robin_Roy
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid