Friday, July 9, 2010

Dynamic Database Connection using SSIS ForEach Loop Container

Did you ever come across a requirement where you have to run the same SQL statement(s) in multiple database server/instances using SSIS?

Many of my friends have asked about how to connect through multiple Databases from different Server using single Dynamic Connection. I want to explain this feature in this article. Basically, I want to execute one query (to calculate Record Counts for a given table) on a set of servers (which can be Dev, Test,UAT, PreProduction and Production servers). In my example, I am using ForEach Loop to connect to the servers one by one--> Execute the Query --> Fetch and store the data.

So here is the approach:
  • Create a Table in your local database (whatever DB you want) and load all the connection strings. Within SSIS package, use Execute SQL Task to query all the connection strings and store the result-set in a variable of object type.
  • Use ForEach Loop container to shred the content of the object variable and iterate through each of the connection strings.
  • Place an Execute SQL task inside ForEach Loop container with the SQL statements you have to run in all the DB instances. You can use Script Task to modify your query as per your need.



Below is the details with an example:

STEP1:
To begin, Create two tables as shown below in on of the environment:

-- Table to store list of Sources
CREATE TABLE SourceList (
   ID [smallint],
   ServerName [varchar](128),
   DatabaseName [varchar](128),
   TableName [varchar](128),
   ConnString [nvarchar](255)
)

GO

-- Local Table to store Results
CREATE TABLE Results(
   TableName  [varchar](128),
   ConnString [nvarchar](255),
   RecordCount[int],
   ActionTime [datetime]
)
GO

STEP 2:
Insert all connection strings in SourceList table using below script:
INSERT INTO SourceList

SELECT 1 ID,
'(local)' ServerName, --Define required Server
'TestHN' DatabaseName,--Define DB Name
'TestTable' TableName,
'Data Source=(local);Initial Catalog=TestHN;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;' ConnString
 
Insert as many connections as you want.
 
STEP 3:
Add new package in your project and rename it with ForEachLoopMultipleServers.dtsx. Add following variable:
 
VariableTypeValuePurpose
ConnStringStringData Source=(local);
Initial Catalog=TestHN;
Provider=SQLNCLI10.1;
Integrated Security=SSPI;
Auto Translate=False;
To store default connection string
QueryStringSELECT '' TableName,
N'' ConnString,
0 RecordCount,
GETDATE() ActionTime
Default SQL Query string.
This can be modified at runtime based on other variables
SourceListObjectSystem.ObjectTo store the list of connection strings
SourceTableStringAny Table Name.
It can be blank.
To store the table name of current connection string.
This table will be queried at run time

STEP 4:
Create two connection managers as shown below:


Local.TestHN: For local database which has table SourceList. Also this will be used to store the result in Results table.
DynamicConnection: This connection will be used for setting up dynamic connection with multiple servers.
Now click on DynamicConnection in connection manager and click on ellipse to set up dynamic connection string. Map connection String with variable User::ConnString.

STEP 5:
Drag and drop Execute SQL Task and rename with "Execute SQL Task - Get List of Connection Strings". Now click on properties and set following values as shown in snapshot:
Result Set: Full Result Set
Connection: Local.TestHN
ConnectionType: Direct Input
SQL Statement: SELECT ConnString,TableName FROM SourceList

Now click on Result Set to store the result of SQL Task in variable User::SourceList.

STEP 6:
Drag and drop ForEach Loop container from toolbox and rename with "Foreach Loop Container - DB Tables". Double click on ForEach Loop container to open Foreach Loop Editor. Click on Collection  and select Foreach ADO Enumerator as Enumerator. In Enumerator configuration, select User::SourceList as ADO object source variable as shown below:

STEP 7: Drag and drop Script Task inside ForEach Loop container and double click on it to open Script Task Editor. Select User::ConnString,User::SourceTable as ReadOnlyVariables and User::Query as ReadWriteVariables. Now click on Edit Script button and write following code in Main function:

public void Main()

{
   try
   {
      String Table = Dts.Variables["User::SourceTable"].Value.ToString();
      String ConnString = Dts.Variables["User::ConnString"].Value.ToString();
      MessageBox.Show("SourceTable = " + Table + "\nCurrentConnString = " + ConnString);
      //SELECT '' TableName,N'' ConnString,0 RecordCount,GETDATE() ActionTime
      string SQL = "SELECT '" + Table + "' AS TableName, N'" + ConnString + "' AS ConnString, COUNT (*) AS RecordCount, GETDATE() AS ActionTime FROM " + Dts.Variables["User::SourceTable"].Value.ToString() + " (NOLOCK)";

      Dts.Variables["User::Query"].Value = SQL;
      Dts.TaskResult = (int)ScriptResults.Success;
   }
   catch (Exception e)
   {
      Dts.Log(e.Message, 0, null);
   }
}
 
STEP 8:
Drag and drop Data Flow Task and double click on it to open Data Flow tab. Add OLE DB Source and Destination. Double click on OLE DB Source to configure the properties. Select DynamicConnection as OLE DB connection manager and SQL command from variable as Data access mode. Select variable name as User::Query. Now click on columns to genertae meta data.

Double click on OLE DB Destination to configure the properties. Select Local.TestHN as OLE DB connection manager and Table or view - fast load as Data access mode. Select [dbo].[Results] as Name of the table or the view. now click on Mappings to map the columns from source. Click OK and save changes.
Finally DFT will look like below snapshot:

STEP 9: We are done with package development and its time to test the package.
Right click on the package in Solution Explorer and select execute. The message box will display you the current connection string.
 Once you click OK, it will execute Data Flow Task and load the record count in Results table. This will be iterative process untill all the connection are done. Finally package will execute successfully.


You can check the data in results table:
Here is the result:

SELECT * FROM SourceList




SELECT * FROM Results

36 comments:

  1. I was looking for the same approach... Thanks a lot Hari.

    ReplyDelete
  2. This is great article. I had this question for a while and this helps. Thanks

    ReplyDelete
  3. Thanks folks.... I am happy to help you!

    ReplyDelete
  4. can i have dtsx file to download it.
    Thaks for the help.

    ReplyDelete
  5. Nice article. Could you expand this article to:
    Given variables are: SourceServer,SourceDatabase, SourceTable, DestinationServer, DestinationDatabase,DestinationTable. if we have the same tablename in destinationDatabase, rename it with current date(talename+date) and import the new table.
    all source and destination variables are dynamic.

    ReplyDelete
  6. Follow this link to find out dynamic connection in SSIS
    http://www.abhyast.com/abhyastcom/post/How-to-connect-to-multiple-servers-dynamically-and-collect-information-using-SSIS.aspx

    ReplyDelete
  7. It helps me alot. Thank you!

    After retrieving data from OLEDB Datasource, I got all Strings returned with limit size =1. And an error of truncate raised.

    Please help me to fix it.

    ReplyDelete
  8. How have you taken "DynamicConection" Connection manager

    ReplyDelete
  9. Azar, I am using a table (SourceList) to loop through server details. Please go through STEP 3 and let me know if you still have questions..

    ReplyDelete
  10. How do you get the foreach loop to change the connstring variable in each iteration? I only get execution against the default connection.

    ReplyDelete
  11. Hi Hari, Just wondering if package configuration would achieve the same goal?

    ReplyDelete
  12. also hari, did you use windows authentication or sql server authentication? I did not find the step which you supplied user id and password to the soucelist.

    Thanks

    ReplyDelete
  13. Didn't see the Parameter Mapping in the ForEachLoop container to map the Connection String sitting in the object to the USER::ConnString variable. Without it it will continue to use the default connection string for each loop instead of changing it.

    ReplyDelete
    Replies
    1. Yes, this is a missing step in the tutorial. You must use the Variable Mappings section to map the User::ConnString to Index 0 of the results.

      Delete
  14. I m using for Flat file
    while setting variable to connection string validation getting fail , so am not able to move forward
    is it possible in case of flat files ?

    ReplyDelete
  15. Sorry for the slow response Jeff, but I did not get your question. Point 4 clearly defines how to configure connection manager called DynamicConnection.

    ReplyDelete
    Replies
    1. Hi. I am confused about this also. What selections do you make to create the DynamicConnection in the first place? I do not have an option to create a Dynamic Connection, only connections of types, e.g. OLE, Excel etc. If I select OLEDB, I need to define the connection. I do not know where the connectionstring property you have highlighted is. Is it part of the Connection object or some other property?

      Delete
    2. Actually, I just worked it out (I think). I just created a dummy OLEDB connection to a dummy server called DYNAMIC. I can then save the conection and see the properties

      Delete
  16. SaS, Are you looking for a solution something like this:
    http://sql-bi-dev.blogspot.com/2010/06/foreach-loop-container-in-ssis.html

    ReplyDelete
  17. Its good to read , Nice explanation .


    Read here about - for-each loop limitations Three scenarios where you can not use for each loop

    http://efectivejava.blogspot.in/2013/07/for-each-loop-limitations.html

    ReplyDelete
  18. fantastic post ... i will definately go throygh it ... also if i have multipul import sources (xls,txt.acces) is it possible to import these files of simular data to an existing table in sql2008.

    thanks

    ReplyDelete
  19. How do you test the connection to see if it works before applying the query? I need to do that so that if it doesn't work it doesn't fail but just loops to the next connection. I haven't found a solution that doesn't cause an error yet. Is there one? Thanks.

    ReplyDelete
  20. Hi everyone, Just a little tip for begginers just like me, there is an extra step 6.1 you need to do the variable mapping to (user::variables). so it would update the variables on each iteration, if you dont it would not work properly.

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. Your post is very interesting.Thank you for sharing. Are you looking for remote DBA support services by smart computing concept? Then Check out ==>> DBA Metrix Solutions

    ReplyDelete
  23. This helped me a lot ! Thanks for sharing !!!

    ReplyDelete
  24. Thanks... please help me how to avoid failing the package when any DB server not able to connect with Dynamic String Connection ... in my case I have 40 servers when any of server not running my package giving me error and getting failed... is there any way to bypass db connection string when the server down/offline..

    ReplyDelete
  25. I have a requirement to fetch data sources from different sources like sql, oracle and oData. I need to pass connection strings based on source type. Any suggestions??

    ReplyDelete
  26. The best technique to Solve MS SQL Connection Error to Android with Online MS SQL Server Support
    Right when any customer endeavoring to relate neighborhood MS SQL from android, meanwhile they getting the screw up message like that "Mix-up in regards to SQL server" if you have a comparative kind of issue by then quickly interface with Remote Infrastructure Management Support for Microsoft SQL Server or Microsoft SQL Server Support. If you require any kind of help or help as for MS SQL Server by then please contact to Cognegic's SQL Server Database Support and draw out the best help.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  27. Get Fast MS SQL Server Connection Help with Online MS SQL Server Support
    In the event that any of the client who utilize MS SQL Server for their work and if standing up to any issue or have some question in regards to this at that point connect with Remote Infrastructure Management Support for Microsoft SQL Server or Microsoft SQL Server Support. With the commitment of most skilled and experienced experts we encourage the best help to our clients. In the event that any kind of issue you may experience at that point contact to our SQL Server Database Support.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  28. Thank you for your guide to with upgrade information.
    Sql server DBA Online Training India

    ReplyDelete
  29. Rapidly Configure the Database Server Connection with Cognegic's DB Configuration Support
    It is safe to say that you are searching for any expert help to design the database server association? In the event that yes, at that point your pursuit closes here, in light of the fact that we at Cognegic give finish setup and establishment bolster for those clients who have any kind of issue with respect to the database. Our specialists manage a significant number of the databases by giving undeniable help, for example, MySQL, Oracle Database, MS SQL Server, Postgres Database, and Cassandra et cetera. In any of the circumstance in the event that you confront the issue at that point contact to Cognegic's DB Installation and Configuration Support or DB Installation Support.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  30. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.


    AWS Training in Velachery | Best AWS Course in Velachery,Chennai

    Best AWS Training in Chennai | AWS Training Institutes |Chennai,Velachery

    Amazon Web Services Training in Anna Nagar, Chennai |Best AWS Training in Anna Nagar, Chennai

    Amazon Web Services Training in OMR , Chennai | Best AWS Training in OMR,Chennai

    ReplyDelete

Here are few FREE resources you may find helpful.