February 11, 2014 at 12:09 pm
I have two different packages I am trying to run. One imports from an excel file the other a .csv file. The files are downloaded every 2 weeks and the date is appended to the last characters of file name of the excel file. The .csv file increments according to week number. My examples are below. How can I use a variable as the connection Manager to dynamically change these files each time? I am still very new to SSIS so any help is greatly appreciated.
ReportPPE1.10.14.xls
ReportPPE1.24.14.xls
MDRMC01P.csv
MDRMC03P.csv
MDRMC05P.csv
February 11, 2014 at 12:53 pm
jameslauf (2/11/2014)
I have two different packages I am trying to run. One imports from an excel file the other a .csv file. The files are downloaded every 2 weeks and the date is appended to the last characters of file name of the excel file. The .csv file increments according to week number. My examples are below. How can I use a variable as the connection Manager to dynamically change these files each time? I am still very new to SSIS so any help is greatly appreciated.ReportPPE1.10.14.xls
ReportPPE1.24.14.xls
MDRMC01P.csv
MDRMC03P.csv
MDRMC05P.csv
Let me lay out the flow for this. You will need to populate an SSIS variable. Then you set the ConnectionString for the Excel connection manager and the Flat File connection manager to use the full file path based on that variable. If you look at http://sqlserverrider.wordpress.com/2013/01/07/dynamic-file-name-for-excel-connection-manager-ssis/ you will see a way of doing this. Step 4 in this blog shows you the steps for mapping the variable value to the connectionstring.
This blog also shows how you can read all of the columns in a folder, which in my opinion is the easiest way to set the value for that variable. You just put the renamed file into a folder and SSIS will read your file with minimal configuration.
If you are comfortable with .Net programming, you can add a script task that can calculate the expected file name. It isn't too difficult, but does require some code.
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply