August 25, 2009 at 10:44 am
HI,
I have one table which consits of conection strings to different db servers.
By using Data flow task i am getting those values into record destionation
Later using foreach container i am exporting required tabels from the database to csv files
I created a script task to dynamically bind server name, uid, pwd etc to oledb connection manager
my problem is it is executing only once. later its not executing i am getting error that the connection not found
Please help me to iron out the problem
August 25, 2009 at 11:46 am
I use expressions to change the connection string.
It's super easy with a forEach loop.
Simply assign your resultset to an object variable, loop through that recordset object and assign
each row/column to a var called strConnectionString
after that use the properties dialog box on your datasource to create an expression for ConnectionString using the user::strConnectionString variable.
Make sense?
~BOT
Craig Outcalt
August 25, 2009 at 11:52 am
First of all thanks for your quick reply.
Exactly now i am trying like that
I have few quries in this
if we give connection string in expressions
by default what connection we need give to oledb in this case,
Any valid connection will do i guess
please correct me if i am wrong
August 25, 2009 at 3:14 pm
BOT is on the right track here. In addition, you don't want to use a data flow task to read your connection parameters into a recordset destination. Instead, take this approach:
1. Execute SQL Task - this task should query your table and return database connection information. Configure the Result Set to Full Result Set and use the Result Set screen to map the result set of zero to an object type variable (create one if you don't already have one). Use this ADO object variable instead of a recordset destination.
2. For each Loop Container. Configure the Loop to iterate through your object variable. Using the Variable Mappings to assign the columns from the recordset to local variables.
3. Configure your Connection Manager connections to use the variables inside expressions to configure the connection string parameter.
August 25, 2009 at 3:32 pm
surya (8/25/2009)
First of all thanks for your quick reply.Exactly now i am trying like that
I have few quries in this
if we give connection string in expressions
by default what connection we need give to oledb in this case,
Any valid connection will do i guess
please correct me if i am wrong
you are correct
~bot
Craig Outcalt
August 26, 2009 at 9:20 am
Thanks for SQLBOT and John Rowan Its working fine now
Surya
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply