SSIS Dynamic Connection String from SQL Table

Question

I have to connect to many Foxpro databases that have a connection string like: Data Source=\\All Users\\DB0009\db1.dbc;User ID=xxx;Password=yyy;Provider=VFPOLEDB.1;Persist Security Info=True;

where the folder DB0009 can be any integer from 0000 to 0100 and the db1.dbc can be either db1.dbc or db2.dbc for each folder.

For each connection string, I need to build a simple SQL statement that is identical for every database except for a hardcoded year. So the sql is: select *, '2012' from table

I'd like to be able to store both the connection string AND the year for each connection string in a sql table that can be looked up at run time.

I am using SSIS 2008. I'm guessing based on what i've seen that i can use the foreach loop with the enumerator set as the ADO Enumerator (though i wouldn't mind confirmation there), but how can i pull the year off of that same table and put it into the sql query i have inside a data flow task?

Thanks for any help! Kary

The desire is to create an authoritative list of all the available connection strings and associate a value with that connection string? — billinkc
– billinkc, Commented Feb 1, 2012 at 15:50

mellowgeek · Accepted Answer · 2012-02-01 17:59:28Z

2

You could do the following steps:

Create the foreach loop to get the connection string and year values into variables.
create a connection manager and, using an expression, set the connection string property to your connection string variable.
Create another variable with an expression that creates your SQL command with the year variable (dynamic SQL command string).
use the execute SQL task to execute this within your foreach.

Here's a link to a blog on mapping the parameter inside the Execute SQL Task that should be helpful. http://www.rafael-salas.com/2007/11/ssis-mapping-parameter-inside-of.html

edited Feb 1, 2012 at 17:59

answered Feb 1, 2012 at 17:02

mellowgeek

3051 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SSIS Dynamic Connection String from SQL Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related