September 18, 2012 at 7:11 pm
Hi Guys,
I'm new to SSIS & I need some assitance around the Flat File Manager concept.
I've designed my whole package and it works successfully; however, I found out when run it as a Job task it fails - I found the issue and it's because in the "Flat File Connection Manager Editor" the filename points to a hardcoded path & file to be inserted into our tables.
e.g. "C:\Datafeeds\df0919.csv
Therefore everyday that filename will change depending on Month & date so tomorrow it will be df.0920.csv .... df0921.csv.... df1212.csv & so on.
Is there any way i can have the filename changed/updated automatically to new filename through a variable or something so it gets picked up.
I've checked online & can't find exactly what I need for my scenario - there are similar ones but when i tried they didnt work.
Thanks
Tava
September 18, 2012 at 8:21 pm
Look into Expressions. They will do exactly what you need. Select your connection manager and about half way down the list of properties you will see "Expressions". Select it then click the ellipsis (...) next to it. From there you can set any property to an expression that will be evaluated at run time. In this case you want the ConnectionString property.
Note this is just one possible way of handling this. Another is Package Configurations. And for that matter you can pass properties for connections by using the /CONNECTION flag on dtexec.exe. If you want to go that route I recommend running dtexecui.exe and filling in the connection information (Connection Managers tab) then going to the Command Line tab to get the parameters to pass in.
I've used all of the above and none are terribly hard. Personally from what you said I would give Expressions a shot first though.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 18, 2012 at 9:19 pm
You can use Foreach Loop Container enumerate these files and save file path to Variable then deal with these file. Remember to move these files to other folder after these files are handled.
Adding and Configuring the Foreach Loop Container:http://msdn.microsoft.com/en-us/library/ms169800(v=sql.105).aspx
September 19, 2012 at 12:23 am
Thanks for the reply guys, i went with the expression as it was the easiest way to do & also understand.
just made a new variable and passed it all through.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply