CODEWITHSUNDEEP
ssis
crswit
crswit

Reputation: 11

SSIS: Overwite the data source/server in a connection string read from a package configuration value

I have looked at other posts and questions but I couldn't find what I needed.

I am relatively new to SSIS Package creation so bear with me please.

Basically, I need the package to connect to multiple servers based on a list of IPs read from a table. I have a connection string that I am reading from a config table. the connection string is generic in that the datasource is simply 255.255.255.255 and I want to replace the datasource with the IPs read from the table as I loop through during package execution.

I am using IPs since the servers I am connecting to are not on our domain. I have set up the server name as a variable within the connection manager expressions. Thus what I am hoping is that the pkg config is read to obtain the entire connection string. Then as I loop through the IPs, the server name variable will be dynamically substituted into the data source value as it loops thorugh. I hope this makes sense.

So the connstring is: (generic within config table)

Data Source=255.255.255.255,65000;User ID=test;Password=test;Initial Catalog=myDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-myApp;

Then as I obtain the list of IPs I want it to change to and then obviously connect as:

Data Source=1.1.1.1,1000;User ID=test;Password=test;Initial Catalog=myDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-myApp;

Then the next IP and connect as:

Data Source=2.2.2.2,1000;User ID=test;Password=test;Initial Catalog=myDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-myApp;

How can I do this using SSIS?

Upvotes: 1

Views: 4102

Answers (1)

David Benham
David Benham

Reputation: 1174

  • Create a connection manager connection to a valid database.
  • Right click on the newly created connection manager connection, select to Properties and copy the value for the connection string
  • Create a string variable and paste the connection string in your clip board in the value for the newly created variable

  • Add an Execute SQL Task with a statement similar to this:

    SELECT TOP 1 'Data Source=' + [IPAddress] + ';User ID='+[Username] + ... FROM dbo.IPTable

  • Pass the result set to the string variable you previously created

  • Right click on your connection manager connection and click the ellipsis next to Expressions
  • In the dialog that pops out, under Property, select ConnectionString and click the ellipsis next to the blank value for Expression.
  • In the Expression Builder, add the variable name to the variable you created. Ex: @[User::CreatedVariableName]

And you're finished. This a basic concept and you can tweak from there..

Upvotes: 2

Related Questions