Loop through all the databases in a server by using SSIS

As a database developer or administrator you may need to loop through all the databases in a server and perform various tasks for each database. There are various methods you can perform this in T-SQL. However here I’m presenting a simple solution by using an SSIS package.Let me go through the solution and explain how it is implemented.

Below are the steps;

1. Start SQL Server Business Intelligence Development Studio.
2. Create an Integration Project. Name the directory and project name.
3. Rename the package to EachDatabase.dtsx by using solution explorer.

4. Create an OLE DB connection in Connection Manager to the target server. Set the RetainSameConnection property to True in connection manager's property window.
5. Add two Execute SQL Tasks, Script Task and For Loop Container to the Control Flow section.
6. Connect them as shown below;

7. I have given meaningful names for each component.
8. You may be getting errors and warnings in Execute SQL Tasks and ignore them for the movement.
9. You need to add four variables to the package. Scope of the variables will be different according to the place where they use.
10. See the diagram below for the variables.

11. Max_ID and Min_ID variables have the package scope while “database_name” and “loop” are having For Loop Container scope.
12. Now let’s configure Execute SQL Tasks, For Loop Container and Script Task to get the final output.

Configure “Get all databases” Execute SQL Task

Change the properties mentioned below.

1. Name, Description (You can give any name)
2. ResultSet: Single row
3. ConnectionType: OLE DB
4. Connection: Select the connection you’ve created in the beginning
5. SQLSourceType: Direct input
6. SQL Statement: Add the SQL statement mentioned below;

7. Set the variables “Result Set”. This is the place where you initialize Max_ID and Min_ID variables. Max_ID and Min_ID are not the database id rather it is an identity value of the temp table.

Configure For Loop Container

1. Double click on the container and you will get the For Loop Editor as shown below;

2. Set the For Loop Properties like above.

Configure “Get database name” Execute SQL Task

1. Double click on “Get database name” Execute SQL Task.
2. Set the property values;
1. ResultSet: Single row
2. ConnectionType: OLE DB
3. Connection: (The connection you’ve created)
4. SQLSourceType: Direct input
5. SQLStatement: [select dbname from #databases where id=?]

3. Set the parameter mapping. Variable “loop” will be the input and by using that value you get the database name.

4. Setting Result Set. Assigning database name to the variable, “User::database_name”

Configure “Display database name” Script Task

1. Double click on the Script Task.
2. Select Script tab in the left pan.
3. Enter User::database_name in ReadWriteVariables section.
4. Click on Design Script button.
5. Add the VB.NET code mentioned below;

Dim db_name As String

db_name = Dts.Variables("User::database_name").Value.ToString()

MsgBox(db_name)

6. Save and exit.

Now the package is all set for the final execution. Start the package and see its execution. Database name will be displayed in a message box and when you click on “OK” the next one will be displayed.

The advantage of this solution is to define the databases which you are going to loop through from the beginning.

I have used SQL Server 2005 BIDS (Business Intelligence Development Manager) to demonstrate the solution.