Can I change a flat file connection manager's source at runtime?

Question

Can I change a flat file connection manager's source at runtime?

Pete T-366679

SSCrazy

Points: 2065
More actions
October 28, 2009 at 8:21 am

#206080

I have a flat file connection manager, set to a test file, and a flat file source that reads this file into a database.
What I want to do is query a database table first and get the path and filename of the text file I want to load; then I want to set the connection manager connectionstring/source to the filename I just read from the database. It sounds like I can programmatically access the flat file connection manager, but I don't see where - my flat file source feeds into a script component, and at that time it's too late to modify the source. Is there a code behind or "on start" even or something somewhere in the SSIS package? In the event handlers, I only have OnError listed for my dataflow objects.
I could probably add a new data flow step that loads a variable with the filename, but I still don't see where I could read that variable in and set the connection manager source before it proceeds through the rest of the data flow. Help?

Viewing 9 posts - 1 through 8 (of 8 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Pete T-366679 (10/28/2009)
I have a flat file connection manager, set to a test file, and a flat file source that reads this file into a database.
What I want to do is query a database table first and get the path and filename of the text file I want to load; then I want to set the connection manager connectionstring/source to the filename I just read from the database. It sounds like I can programmatically access the flat file connection manager, but I don't see where - my flat file source feeds into a script component, and at that time it's too late to modify the source. Is there a code behind or "on start" even or something somewhere in the SSIS package? In the event handlers, I only have OnError listed for my dataflow objects.
I could probably add a new data flow step that loads a variable with the filename, but I still don't see where I could read that variable in and set the connection manager source before it proceeds through the rest of the data flow. Help?

Pete,

What you have to do is setup the values you load from the database into package variables. And then you have to use these variables in expression, which sets the flat file connection manager properties dynamically.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 2

Nice, I'll try that out. What is the expression syntax? I would just integrate that into the source dialog in the connection manager properties?

I was just starting to try a Script Task prior to my data flow that would select from the db and either set the connection property directly, or like you suggest, set a package variable.

Thanks!

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

Pete T-366679 (10/28/2009)
Nice, I'll try that out. What is the expression syntax? I would just integrate that into the source dialog in the connection manager properties?
I was just starting to try a Script Task prior to my data flow that would select from the db and either set the connection property directly, or like you suggest, set a package variable.
Thanks!

I would recommend you check this introduction to expressions by Andy Leonard. As always I would recommend you get a good book about SSIS. These two are some of the best:

Expert SQL Server 2005 Integration Services

Professional Microsoft SQL Server 2008 Integration Services

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

CozyRoc (10/28/2009)
...
These two are one of the best:
...

Hmmm, you might be pretty good at SSIS, but I'm not so sure about your English! 😀

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

Phil Parkin (10/28/2009)
CozyRoc (10/28/2009)
...
These two are one of the best:
...
Hmmm, you might be pretty good at SSIS, but I'm not so sure about your English! 😀

Thank you for the friendly correction Phil 😉 As English is not my native language, I'm doing my best.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

I hope you did not take offence - none was intended. My comment was meant in fun.

I never even suspected that English was not your native language - you write so fluently.

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

Phil Parkin (10/28/2009)
I hope you did not take offence - none was intended. My comment was meant in fun.
I never even suspected that English was not your native language - you write so fluently.
Phil

Thank you for the kind words. And of course I did understand your intention. I know you are a nice guy.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 8

Thanks everyone for all the help! That actually seems to work quite well, and I've got my package working the way I need it. Thanks again!