Looping through SQL Servers using SSIS or Dynamically build connection to multiple SQL Servers | SansSQL

Sunday, August 14, 2011

Looping through SQL Servers using SSIS or Dynamically build connection to multiple SQL Servers

Consider you are giver a task of gathering information from multiple servers. What are the different ways you can automate this?
  1. Create linked servers in each server pointing to your central repository
  2. Create Separate SSIS package in each servers which loads data to your central repository
  3. Create one SSIS package with multiple data sources and duplicate the tasks for each data source.
  4. Create one SSIS package which dynamically builds connection to each server and does the data loading tasks.
If you choose an option between 1 and 3 , then  you will have to do some extra work for gathering the server information when there are new servers added to your environment.
If you choose the 4th option then also you will have some extra work but it is very small and easy when compared to the first 3 options. Here you need to just add the new Server Names to the table and that is all and everything else will be taken care by the SSIS package.
In this post I am going to explain how to create a SSIS package which loops through multiple SQL server and gathers information  by building the connection to those servers dynamically.
  1. Open “Microsoft Visual Studio”
  2. Create a new “Integration Services Project”
  3. Create a SSIS Package
  4. Create 2 Connection Managers, 1 for the source (which will be set dynamically for each iteration of the loop)  and the other for destination (which will remain constant, in other terms your Central Repository)
  5. Store All your Server Names in a table preferably in the Central Repository (Destination Connection)
  6. Add 2 variables
  7.     a. Name: ConnectionVariable (A name of your choice)
            Scope: Package
            Type: Object
            Value: System.Object
        b. Name: ServerName  (A name of your choice)
            Scope: Package
            Type: String
            Value: A Valid Server Name

  8. Add an "Execute SQL Task" with SQL Statement like "select FullName from dba..tbl_ListOfServers".
  9. Set the result set to Full Result Set.
  10. On the Result Set page, Add a Result 
  11. Set Result Name "0" and assign it to your Object variable (In our case it is “ConnectionVariable”).
  12. Now add a “Foreach Loop Container” 
  13. Connect it from the “Execute SQL Task”
  14. Inside the “Foreach Loop Container” , add the required “Data Flow Task”
  15. Now Right-Click on the “Foreach Loop Container” and click on “Edit”
  16. Now Go to “Collection” page and Set “Enumerator” to “Foreach ADO Enumerator” and “Enumerator Configuration ” to the Object Variable(In Our Case “ConnectionVariable”) and Set “Enumeration Mode” to “Rows in the First Table”
  17. Now go to the “Variable Mapping” page and Choose the “String Variable” (In our case it is “ServerName”) and set the Index=0
  18. Now, in the “Data Flow Task” add a “OLE DB Source” (Dynamic Connection) and connect it down to a “OLE DB Destination” (which will be your Central Repository).
  19. Now Select the Source Connection Manager (In Our Case it is “Source”) and Right-Click on this Source and choose “Properties”.
  20. Expand the “Expressions” Option and click on the browse button (…)
  21. Now in the “Property Expression Editor”, Choose the property “ServerName” and click on browse button (…). Now choose the String Variable (in our case “ServerName”) and Drag and Drop this variable into the “Expression” box and click “OK” and “OK”.


Now when the package runs, the “Execute SQL Task” will read the list of servers and stored in the table you specified and the “Foreach loop” will iterate over each record in that table, running the “Data Flow Task” each time while each time, the ServerName property of the Source Connection in the data flow will get a new value.

This article is also available in pdf format for downloading.
Please Click here to get your copy.

21 comments:

freehansje said...

Very nice article, I was looking for something like that. One question though: why not use SELECT name FROM sysservers to get all the names of your linked servers? You may need the name of the host-server as well, but that's easy.

Sandesh Segu said...

Hi,

Since we are trying to create a central repository, I think Linked Servers will not server our purpose. :)

BekrenevSS said...

Thanks for the article, but there is an error of 18 part. (In Our Case it is “Source”)

Sandesh Segu said...

Hi BekrenevSS,

Could you tell me what is the error ur getting?

Regards,
Sandesh Segu

BekrenevSS said...

I think this is a misprint.
In you article you write "18. Now Select ...(In Our Case it is “ListOfServers”)..."
Must be "18. Now Select ...(In Our Case it is “Source”)..."

Sandesh Segu said...

Hi BekrenevSS,

Yes, you are correct. It was a typo and thanks for pointing the mistake. I have corrected it now.

Regards,
Sandesh Segu

Anonymous said...

Hi, I'm a DBA newbie, I've been looking for something like this and am really excited to try it out. There is just one part I don't understand. In step 17 you have us adding 2 "Data Flow Task's" could you go a little further explaining how you set them up? As I understand it the source task will connect to the source connection and the destination will connect to the listofservers connection. But what about the internal set up of the task, like the data access mode and what should follow depending on what access mode is selected. Thanks for the brilliant article

Sandesh Segu said...

Based on the type of data(using tables or using SP) you are importing, you can choose the data access mode.

Anonymous said...

In Step 4 can you tell me what type of connection source your selecting for the Source and ListofServers? I can't figure out how to get a dynamic source that defined that doesn't require a server name as I define it.

M said...

Hi Sandesh,

Thanks a lot for your post. I have no background in SSIS and I am trying to do the exact thing which you have explained. I still cannot connect the result set from the Execute SQL task to the source of the data flow task. And you please guide me.

Appreciate your help.

-M

Anonymous said...

the tutorial is retarded ! tell a beginner to follow your steps and trust me, they wont be able to understand whats happening. If you want to make a tutorial, make it well. otherwise, spare us the trouble of going through this crap.

Anonymous said...

This tutorial looks good, but many steps are missing. Can you show us each step in the process, so that a person who is not even aware of ETL can do the job by simply following the steps ?
Thanks.

Anonymous said...

Hello, please help, I am getting this error -

The connection manager "Source" will not acquire a connection because the connection manager OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.

Anonymous said...

Sandesh, how was the 'Source' connection set up?

Anonymous said...

Nice article as a new bie! Still i need to deal with permission on database for all servers...

thanks

Anonymous said...

Nice post. I learn something new and challenging on blogs I stumbleupon on a daily basis.

It's always interesting to read through articles from
other authors and use something from other websites.


Here is my web site Watch The Wolf of Wall Street online free (marcondesviana.blogspot.se)

Anonymous said...

It is not my first time to pay a quick visit this web page, i am visiting this website dailly and take
fastidious information from here daily.

Here is my weblog ... best binary options bot

Anonymous said...

This blog was... how do you say it? Relevant!!
Finally I have found something that helped me. Appreciate it!


Also visit mmy homepage optionBot2 ORG

Todd Harmon said...

I have a free utility for dba's to help them monitor their servers that illustrates these ideas: https://harmontodd.wordpress.com/2015/09/12/mmoap-lives/

sp_who said...

I am pretty new to SSIS. I need to loop through Oracle instances. Will that process work?

Unknown said...

Excellent staff. You are the man!

Post a Comment