September 14, 2011 at 3:26 pm
Hi,
We receive multiple .csv file from our vendor each day, each with a different type of data in it and the rows are identified by a type id in the database table upon insert.
I have an SSIS package that will be loading each of these .csv file into a database table. The package is very vanilla.
I created the SSIS package that does the following:
1) Script Task - For defining the package variables that i use for example
providing the type id to use upon insert for identifying the rows
of the current file in the target table.
2) Execute SQL Task - Truncates the target table for rows with the specified type
id value that i pass in.
3) Data Flow Task
a) A Flat File Source that connects to the .csv file
b) Derived Columns Task that includes the variable values that i pass in for
example for the type id.
c) An OLE DB Destination for loading the file data plus the variable values into
the target table.
That's the process intro. Now the issue is the file name of the .csv file is in the format XX_YYYYMMDD.csv so the filename changes every day.
I have a variable that includes the full path for the file. the flat file connection in the data flow task is dependent on the variable value (using expressions).
How do i make the file name dynamic so that when i run the package, the local variable contains the correct file name so that the flat file connection in the data flow task picks up the correct file name from the variable and loads it into the table.
Ex: If i run the package today, the filename would be XX_20110914.csv
If i run the package the next day, the filename would be XX_20110915.csv
I would like the package to automatically do this internally, especially the variable, so that at runtime the package is automatically looking for the correct file.
If the filename was static, ex: XX.csv then obviously it wouldn't be an issue at all but since it is not, i would like the package to automatically calculate the variable value for the filename or in my case the complete file path and then use it to connect to the file and load it inside the package.
So far, i have done something like the following:
The target file path for the connection variable is: folderpath\filename.fileformat
I have created 3 package variables.
1) variable1 is a string type and i specified the folder path only.
ex: e:\vendor_apps\datafeed\gps_feeds\us_
2) variable2 is a string type and i specified the .fileformat value
ex: .csv
3) variable3 is a string type and i am using the expression builder to build the value as
ex: e:\vendor_apps\datafeed\gps_feeds\us_20110914.csv
the date part of the filename must be generated in the expression itself so that the variable 3 contains the complete file path when the flat file connection reads from it.
Please suggest.
Thanks.
September 15, 2011 at 8:13 am
I think that you're approaching this backwards. Instead of starting from the filename and getting the file that matches it, you should start from the file and get the filename. This is fairly straightforward to do using a ForEach File Loop.
It's also more robust. If your process doesn't run for some reason, it's easy to go back and process previous days' files using the ForEach File Loop, but it's much harder to do when you are pre-calculating the filename.
You may need to adjust your process to archive previously processed files.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply