How to set Connection Manager dynamically

Question

How to set Connection Manager dynamically

Balaji L

Hall of Fame

Points: 3067
More actions
June 17, 2009 at 9:22 am

#135214

Hi,
I am pulling data from DB2(source server) to SQL Server database. We have nearly 90 DB2 servers. I need to pull all the data from 90 servers. The table structure is similar in all the DB2 database.
I created one package to pull the data from one DB2 server. I want to execute the same package for different source DB2 server. How to change the connection string dynamically?
Also I need to execute the package to all server at one go. How to ahieve this.
Thanks,
Balaji L

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

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

Bueyes SSC Eights! Points: 871 More actions · Answer 1

Hi

As you know the connection has a property named Expressions, you can opened and select the connection string, then this can be assigned to a variable, so you can read the server names stored into a table or file, then throw a loop structure can read each server name assign this a variable for connection string and then the server must be selected. May be the best solution is using to packages, one for the main loop and assign the variable for the next level package, that contain the connection to the server.

I hope this solution can help you.

Regards

Balaji L Hall of Fame Points: 3067 More actions · Answer 2

Hi Julio,

The idea is great. Could you please explain further how to implement the logic.

I created Variable in the package called "SourceConString".

Name : SourceConString

Scope : Package1

Data type : String

Value : Provider=SQLOLEDB;Data Source=Server1;database=db1;uid=sa;pwd=pwd123 --The value should be change dynamically.

Also created a table called "ConfigTable". It has Id and ConnectionString.

Id ConString

1 Provider=SQLOLEDB;Data Source=Server1;database=db1;uid=sa;pwd=pwd123

2 Provider=SQLOLEDB;Data Source=Server1;database=db2;uid=sa;pwd=pwd123

In my package I created for loop container in the control flow task.

InitExpression : @Id = 1

EvalExpression :@Id < 2

Inside for loop I have dataflow task.

Data flow task has

OLEDB Source

OLEDB Destination

How should I fetch the ConString from my table "ConfigTable" using the loop iteration.

How should I pass the ConString value to Variable value.

Thanks,

Balaji L

Balaji L Hall of Fame Points: 3067 More actions · Answer 3

Hi Julio,

I got the solution and now its working fine.

Inside the for loop, used execute sql task.

From execute sql task am getting "ConString" value and assigned this to the global variable.

Thanks for your help.

Thanks,

Balaji L

Bueyes SSC Eights! Points: 871 More actions · Answer 4

Hi,

is good to know that this solution work great for you.

Thanks for your answer.

Regards

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 5

Hey Julio,

Saw this thread and it pretty much matches what i need.

I am setting 3 variable:

source_server

dest_server

current_server

current_server 'should' be which ever server the package is being deployed on (this i am currently setting through XML config file). I also have 'current_server' connection manager...

What i would ideally like to do is set the connection manager to automatically change its 'servername' property to the value of the 'current_server' variable.

The idea here is that once complete the package can be deployed to any evorinment and all that needs to be adjusted is the XML and not the package!

Appreciate any ideas. 🙂

Is that possible?

Adam Zacks-------------------------------------------Be Nice, Or Leave

pradeep.bohra SSC Enthusiast Points: 162 More actions · Answer 6

HI,

http://www.sqlservercentral.com/articles/Integration+Services/61621/

You can refer this article.

Also for your source , use XML Taks - Last Item in Control Flow Items.

Bueyes SSC Eights! Points: 871 More actions · Answer 7

Hi,

I think that the article has the best solution

Regards

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 8

Thanks guys. I actually used to use this solution before i rewrote in VB.

Didnt really do what i wanted but the prompt about setting the connection manager expression was just the ticket.

All sussed now with one very intelligent login synchroniser up and running 😉

Adam Zacks-------------------------------------------Be Nice, Or Leave