Reading Data From Multiple Sources dynamically using SSIS Package

 

If the business is located across the geographical regions, we will be getting data in multiple languages from different sources. When we are dealing with the data warehouse, we have to read data from various sources and load into the targets.

In this case, creating multiple ETL jobs with different source systems is not a proper solution. So, the other way is to pass the connections dynamically by configuring them, read the data from respective sources and load the data into targets.

I am herewith explaining the same with sample data with the screen shots.

 Here are the connections I am going to use. We have to have a table to hold all the connections of the databases we are going to read dynamically.

 

Source Data from three different sources is as follows. For the explanation purpose I have given unique customer id’s in each file.

 

 

 

1. Create Execute SQL Task: This is to read the connections which are maintained in a table in SQL Server Database. With the help of Execute SQL, I am going to read the connections and pass them to the next stage which is for Each Loop container.

2. Create and rename the task

 

3. Create a variable to hold the connections from a table.

 

4. Create the variable of Object type, which holds all the values coming from Select Statement.

 

5. Set the result set to ‘Full Result Set’ to hold set of values in the general Properties.

 

 

6. Set the OKEDB connection which points to the database where we have the table with connection manager information.

 

 

7. Write the select Statement to get the connections from the table.

 

8. Go to result set properties and select the variable which supposed to hold the result.

 

 

 

9. Take For Each loop container to read connections one at a time from the object variable and assign to a local variable.

 

 

10. Rename the For Each Loop Container.

11. Choose proper Enumerator in For Each Loop Container Collection Properties. In current demo, we have to choose ForEach ADO Enumerator to read one value at a time from object variable.

 

 

12. Select Source variable of object from where we are going to read values.

 

 

13. Create a string variable to hold the connection string.

14. Go to variable mapping properties and select the variable of String type which holds the connection managers one at a time.

 

15. Create the data flow Task, rename the task with a proper name and complete the mapping with an oledb source and a destination.

 

16. Set the delay validation to TRUE in the data flow task properties.

 

 

17. Go to source connection manager properties and select the ellipses of Expression property to assign the FELC variable which is of string type that is hlding connetion manager values one at a time. This will be passed dynamically to the OLEDB source as a connection and SSIS engine will read the data from the respective sources  and load into the target.

18. Go to connection ellipses…

19. Setting the connection String property with FEC Variable….

 

 

20. Save and Execute the task. Here is the status of the task .

 

21. Output of the DFT is as follows. You can see data from all the three sources.

That’s all folks. Hope this is useful for you.

All the best. Happy Learning.

Please share your valuable comments and suggestions to help me in sharing my knowledge.