Dynamic Flat File Connections in SQL Server Integration Services

By:   |   Comments (22)   |   Related: More > Integration Services Connection Managers


In SQL Server 2000 DTS, creating a connection to an object is relatively straightforward, but limited. Making a connection to a file, particularly if you need a dynamic connection string, likely requires a global variable, a dynamic properties task, and ActiveX scripting. Using ActiveX scripts in DTS packages tends to slow the package down because the code needs to be compiled at run-time. In SQL Server 2005 SSIS a connection to a flat file is much easier and makes use of new programming techniques, making the package run more efficiently and smoothly.


Connection Manager is a way of communicating with a variety of interfaces. It is located on the bottom portion of the Designer window after opening a new or existing package. You create flat file connections by right-clicking the Connection Manager area and choosing New Flat File Connection:

Options for new connection in Connection Manager
A new screen opens where you enter the information about the flat file you want to connect to (most of the options are self-explanatory):
Flat File connection properties

When you click on the Columns section you should see the actual data from the file (as well as in the Preview section):

Columns section of Flat File Connection

The Advanced section is the area where you can rename the incoming column, change the data type and length of string:

Advanced section of Flat File Connection

In addition to making a static connection, you can also create a dynamic connection using Expressions. In SQL Server 2000 DTS you had to create a global variable, use the Dynamic Properties task to get the value, and ActiveX scripting to assign the value to the connection.

Here is an example of creating a dynamic flat file connection in SSIS. Let's say that every morning we load a textfile from the Receiving Department's network share into a database (for this we will use C:\backups\). The file is always processed the day after the receiving process and is named "DataLoad"+"month"+ "day"+"year.txt" (i.e., DataLoad10112006.txt). We are setting up an SSIS package that retrieves the data from the file and moves it to the database.

There are a couple of ways of doing this, but we decide to create a package variable called "DataLoadDir" to hold the folder location. We open the Variables window and click on Add Variable. The variables window may have to be expanded by dragging the right side of it out. We change the Data Type to String, then type in the string value "C:\backups\":

Variables window

Right-click the new Flat File connection and choose Properties. The Properties window on the right side will open. There you will see an area called Expressions. Click the ellipse on the side and it will open the Property Expressions Editor:

Expression Builder in SSIS

Select ConnectionString in the Property area and click the ellipse at the end of the row and the Expression Builder opens. You can drag expressions from the right side to the Expression textbox. The expression can be previewed once built by clicking Evaluate Expression:

With the Expression Builder open again, we will assign the variable for the ConnectionString property. We first add the package variable DataLoadDir by expanding the Variables tree on the left and then do a drag and drop into the Expression textbox. Then we had a + sign to concatenate. We add the string "DataLoad" in double quotes and another + sign. The next three phrases capture yesterday's date:

  • (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() )) gets the month
  • (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() )) gets the day
  • (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) gets the year

The above statements can either be typed in or dragged down from their respective location on the right side. In the case of DATEADD statements, when you drag and drop the statement into the text file it appears in the following way: "DATEADD( źdatepart╗, źnumber╗, źdate╗ )". We merely replace the various unknowns with the information we want.The last part is to add the extension ".txt" to the end of the string. Once we have everything in place we can click Evaluate Expression to see the results:


To save the Expression, click on OK and this expression will now be saved with your connectionString property and be resolved automatically each time it is run.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author MSSQLTips MSSQLTips.com was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, October 8, 2014 - 2:32:12 AM - Nagarjuna Back To Top (34876)

Hi , i have a set of flt files in one folder my task is i want to full all files in sql server how to achive this please provide complete solution with screen shots , your so greatful if you provide solution 

Wednesday, May 21, 2014 - 12:50:04 PM - Manoj Back To Top (30876)

Thank You VFRDavid.

Monday, January 13, 2014 - 2:56:55 PM - Diego Grigol Back To Top (28043)

Very helpful article. Thank you for this. Although I'm used to use expressions, had no idea about how to use them in Flat File Connection Managers. 

I've used a variable mapped in foreach loop and it worked fine.


Wednesday, January 8, 2014 - 10:52:22 AM - Anirban Back To Top (27992)

Hi ,


if the file name  like "Sent_November" is the below query is correct?

@[User::SENTFile]+"Sent_" + [DT_STR,4,1252]MonthName((DateAdd("m", -1, Now())).Month) + ".csv"


if not then please suggest me with the correct one.




Wednesday, September 18, 2013 - 8:44:09 AM - Greg Robidoux Back To Top (26841)

@Vijay - take a look at this tip


Wednesday, September 18, 2013 - 1:37:18 AM - vijay Back To Top (26838)


   i am beginner to sql server .i have more than twenty text files and i want to import all those into newly created database with respecting to textfile names as their tablesnames. if possible please let me know the steps 




Monday, August 19, 2013 - 3:07:20 AM - BOND Back To Top (26373)

Hi, How to create dynamic text file in SSIS in this format. I need a code.

·         Extraction Date in this format(YYMMJJ) time(HHMMSS).txt


Tuesday, April 16, 2013 - 2:37:15 AM - Fred Back To Top (23366)


greate article, I really need this.





Thursday, September 13, 2012 - 4:44:32 AM - kapil Dev Back To Top (19500)


i am trying excute the Stored procedure from Sybase in SSIS, i can able to preview the data, but columns are not displaying.

can you please let me know, how do i load the data from stored procedure to table or flatfile.




Saturday, August 25, 2012 - 9:14:35 AM - Prasad Back To Top (19223)
Hi Guys, Please anybody can help on this I have an issue with all file connection managers. using variables i have tried to concatenate the existing file name with date as Employee_MMDDYYYY.txt, but flatfile connection manager had created new file with above format. instead of loading in to existing file.

Thursday, May 24, 2012 - 7:01:00 AM - venkat Back To Top (17632)


 Iam New To Sql server. can  anybody tell me why we use (dt_str,4,1252)?what is the use of it?

Pls Post the answer If Anybody knows.





Sunday, May 6, 2012 - 10:03:02 AM - Chedy Back To Top (17288)

Thanks por posting the article, it's really helpful.

I have to import from FoxPro tables from different folders. Each folder contails the same set of tables, so I know the names. What I have to pass the SSIS package is the folder pathname. Is there a way todo so?

Thak you.

Friday, March 23, 2012 - 2:42:27 AM - Ashok Pandey Back To Top (16586)

@ Quintin  , go through the website it has a lot as per your expectaion



Monday, February 20, 2012 - 1:23:30 PM - Quintin Mohale Back To Top (16098)


I am new to SQL server 2008 (I know 2012 is on the way but our company just moved to 2008, sad by its true! :-)) I was hoping perhaps you can direct me to interesting websites on Expressions and Tricks using Script Task??? Any reply on this question will be greatly appreciated! thank you!



Monday, February 20, 2012 - 1:19:34 PM - Quintin Mohale Back To Top (16097)


to get the format c:\BackUp\TestFile_20120220

Use the below code

the variable [User::DataDir] is a string which contains the foldername C:\BackUp\

@[User::DataDir]+"TestTextFile_"+SUBSTRING((DT_WSTR, 30)(DT_DBDATE)

Try it out!!!!

Friday, October 15, 2010 - 1:07:29 AM - vinothraj Back To Top (10273)
Hi all,

This is very great and use full artical, this is same way how can i use in excel integration, i can use this steps for excel but experssion evaluvated. but showing error. pls explain how to use excel files for dynamic





i hope some one help me out this

Friday, September 17, 2010 - 4:55:06 AM - Sacchi Back To Top (10169)

  Great article. I would require this way.


   But only require to look for D:\test\filename.


Tuesday, August 18, 2009 - 7:05:47 AM - ramdabburi Back To Top (3904)

really helpful...but how to create a time stamp..

Saturday, July 25, 2009 - 8:13:38 PM - ghantabro Back To Top (3778)

Great this is a great I was able to use variables for flat files and tables names; however, I have one issue though..

my flat file keeps changing the fields... it is a csv file with embedded comma. Variable for file and table worked fine but as soon as I changed the fields in the file and the staging table it did not work correctly as the mapping was dynamic? Any work around to import flat file with comma as delimiters and Quotes around fields that has embedded comma... Wish I could have used OpenRowSet, but it cannot be used in 64 bit machine. I am sure SSIS should handle dynamic mapping as well... any suggestion is appreciated. Thanks a lot. 


Wednesday, November 5, 2008 - 10:57:10 AM - PatMundy Back To Top (2149)

 VFRDavid, Thanks for posting this comment because I needed the exact same thing!  However, I point out for future readers that at least as my system is configured, the "dateadd("dd", 0, getdate()) part of this is superfluous.  The code works just as well without it as far as I can tell, hence: 

@[User::FileDestination] +  (DT_STR, 4, 1252) YEAR (getdate()) + RIGHT ("0" + LTRIM ((DT_STR, 4, 1252) MONTH (getdate())), 2) + RIGHT ("0" + LTRIM ((DT_STR, 4, 1252) DAY (getdate())), 2) + ".TXT"

I am guessing you left the dateadds in there since you, like me, may have tried the "dd" thing (which I also tried with DatePart) to get the leading zero.  After that failed, you added the successful "right("0"+val, 2) logic, and then just kept the whole thing, since it does, after all, work.



Monday, October 20, 2008 - 7:08:07 PM - Webman Back To Top (2021)

This is a great recomendation, was lookign for this just the other day. My script appears to be broken to where the file cant be located? I keep getting I cant find the file error when the file is in this directory.

Thursday, September 11, 2008 - 9:39:08 PM - VFRDavid Back To Top (1802)

Very helpful code!  However, I needed to create the filename with the date always 8 bytes.  The sample chose 10/11 for a reason  :-), if you use the code from January to September, and/or from the 1st to the 9th of the month, it either shortens the file name, or embeds spaces (for example - today is 2008911.txt, but I want 20080911.txt).

 Anyway, I do not know how to make the expressions automatically return leading zeroes, so I modified the sample code with the following:

@[User::FileDestination] +  (DT_STR, 4, 1252) YEAR (DATEADD ( "dd", 0, getdate())) + RIGHT ("0" + LTRIM ((DT_STR, 4, 1252) MONTH (DATEADD ("dd", 0, getdate()))), 2) + RIGHT ("0" + LTRIM ((DT_STR, 4, 1252) DAY (DATEADD ("dd", 0, getdate()))), 2) + ".TXT"

The basic logic is, take the RIGHT two characters of the resulting expression.  When the month is 10 - 12, you'd get 010, and the RIGHT two of that is still "10".  If the month is " 9", the result is "09" and you still get what you want.

 If anyone has an easier way, please let me know.  I thought that I could just use the following:

CONVERT(CHAR(8), GETDATE(), 112)  but the TransactSQL expressions do not work in the SSIS Expressions builder.


get free sql tips
agree to terms