How to Connect Multiple Datasources in Different Domain through SSIS

How to connect multiple datasources in different domain via SSIS?

Example:

Let us consider I have an excel source and I need to load the data in excel sheet 1 to 3 different databases (two in same domain A and 1 in different domain B). In this case we can use Multicast transformations to load data to all domain. But we need to create connection string for all databases which may become complicated when the number of dataources are more (e.g., 50 connection instead of 3). To avoid this we can use For Each Loop container.

Follow the below steps:

1.      Create a table “dbo.Loop” and save the details of the data sources:

 

2.      Create the following variables as shown below:

3.      Create the following connection managers as shown below:

·        Sql Server connection LoadSourceDetails: To get details of databases to which data has to be loaded.

·         Sql Server connection Datasource: Connectuion manager configured with one of the destination datasources.

In this connection manager go to Expression Properties and config the below value:

"Provider=SQLNCLI10.1;Data Source="+ @[User::LoopServer] +";Persist Security Info=True;Password="+ @[User::Password] +";User ID="+ @[User::Username] +";Initial Catalog="+ @[User::LoopDB] +";"

4.       Create a package as shown below:

5.       In Load DatasourceDetails config like below:

SELECT        ServerDW, DatabaseDW, Username, Password

FROM            dbo.LOOP

WHERE        (IsActive = 1)

6.       In ResultSet map the User::LoadConnection variable:

 

7.       In For Each Loop container perform the below configurations:

In Variable mappings:

 

8.       In Data flow task create a simple flow tasks as shown below:

 

Now execute the package data will be loaded to all databases present in “dbo.Loop” table.