Dynamic Flat File Connection

Question

Dynamic Flat File Connection

hugoscp

SSC Enthusiast

Points: 165
More actions
March 12, 2010 at 5:48 am

#212377

Hello, I´m using ssis 2005 and, i would like to read a table in sql server 2005 that contains the path for the file that i´m going to use and used it in the expression of the flat file connection.
I already created a variable with that sql query but that didn´t work.
Can someone help me, please

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 1

Create an Execute SQL Task that reads your table with the filepath. Store the result in a string variable using ResultSet -> Single Row. Connect the SQL Task with the dataflow that contains the flat file source with a precedence constraint.

Then, use this variable in the expression of the connection string of your flat file source.

Another option is that, if you can choose the lay-out of the table that contains the paths, that you use this table as a configuration table. Set the value of the variable through the package configurations (right-click on your control flow, choose package configurations.).

If you need help setting up a configuration table, let me know.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

hugoscp SSC Enthusiast Points: 165 More actions · Answer 2

Thank you, the problem is that when i put my variable @[User::path] in the connection string of the flat file connection that contain select NOME as resultado from dbo.teste i get this error:

[Flat File Destination [1040]] Error: Cannot open the datafile "".

I can´t see anything in file name

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 3

That is because the variable will be filled in when your package is running, not when you are designing it.

You can solve this by putting the correct value in the variable, hard-coded. This will erase possible warnings or errors and it doesn't matter that it is hard-coded, because the value of the variable will be overwritten at run-time.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

hugoscp SSC Enthusiast Points: 165 More actions · Answer 4

it workedddddd. thank you, you were correct, the problem was that i was running the task separeted ways.

Thank you

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 5

Just to make sure, put another (correct) filepath in your variable (pointing to another file).

Then run your package so you can be sure that SSIS takes the correct file at run-time.

You never know...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP