Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS - Flat File Manager help Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 7:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
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
Post #1361077
Posted Tuesday, September 18, 2012 8:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:22 PM
Points: 3,466, Visits: 1,819
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
Post #1361096
Posted Tuesday, September 18, 2012 9:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:47 AM
Points: 126, Visits: 1,084
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]
Post #1361114
Posted Wednesday, September 19, 2012 12:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
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.
Post #1361147
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse