SSIS Package - Flat File Source with Different File Names?
-
I am using MS Visual Studio 2008 to create an SSIS package. I have created a package that loads and processes a text file with a "fixed" filename and folder location stored in the flat file connection manager object. The name of the files are consistent except for the date portion (ex: ABCD09142011.txt) When a new text file is downloaded for processing, I manually modify the filename by changing the date portion of the filename in the flat file connection manager and execute the package.
Is there a way to have the user execute the package, be prompted for the date, so that the flat file connection manager object is updated with the new text file name, and then have the package continue to run? If not, any alternatives would be appreciated.
-
I'm sure there is a way you could do this with a variable, but when faced with a similar challenge, I took the easy way out and called a batch file which renamed the file using a wildcard to ignore the date (e.g. rename filename*.csv processme.csv)
-
Use ForEach Loop, put the filename into a variable, then, perform the rest as you do now.
-
Follow Wildcats advice. You need to use ForEachLoop with variables.
Thank You,
Best Regards,
SQLBuddy
-
As Wildcat mentioned, you want a ForEach Loop object in the Control Flow. It accepts wildcards, so you'll be searching for files in the path with ABCD*.txt. That goes to a package variable.
What was neglected to be mentioned is the easiest way to deal with that variable is using expressions on the data source object. In there, you'll find a 'connection string'. For Flat Files, this is merely the pathing to the file, nothing else. So, you'll want to build an expression using the pathing and the variable there, and then your package will automatically set the data source as it loops through the files with the correct name.
You will also want to probably build an archiver into the loop, so that the same files aren't picked up on multiple runs. The easiest way I've found to do that is to use a script task to set package variables with the full pathing (in particular for the archive), and then use the"File System Task Object", plugging in the variables where appropriate. Make sure to include this at the end of the ForEach loop tasks.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Thanks for your suggestion, the batch file worked perfectly!
-
I want to follow-up on my last post to say thanks to all of you who responded. I will use the batch process that was suggested for now as I am only processing one file at a time. However, the ForEachLoop container will be of great use to me in situations where multiple files are being processed. This was my first experience with SQLServerCentral forums and all I can say is you folks ROCK!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply