SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - Flat File Manager help


SSIS - Flat File Manager help

Author
Message
Tava
Tava
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 792
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
Kenneth.Fisher
Kenneth.Fisher
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4374 Visits: 2034
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 Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
DBA328
DBA328
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 1123
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

[li][/li]
Tava
Tava
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 792
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search