SSIS packages are great ETL tools and can do just about anything you
need in terms of ETL. Most organizations start out creating SSIS package
one by one until they have dozens, hundreds, or even thousands of
packages. I have worked with one client that ran over 4,000 packages.
This can be a nightmare to maintain. You can save yourself a lot of work
by deciding upfront how to configure your packages using configuration
files or tables. We are going to discuss configuration tables in this
article.
We are going to look at a simple example of passing information to a package with a configuration table. Then we will go over using configuration tables on multiple packages. Imagine running dozens of packages that point to a server and the server name changes. If you have a configuration table that is feeding this server name to every package you can make a single change to the configuration table and all the packages are updated. This can reduce your maintenance time significantly.
We are going to build a couple of simple packages with connections. These packages will connect to the adventure works database which is freely available on codeplex. Since you will not being pulling any data you can use any database you would like:
1. Drag in an Execute SQL Task into a blank SSIS package.
2. Double click on the Execute SQL Task to edit it.
3. Click on the connection drop down menu and select New Connection.
4. Click New.
5. Enter in your server name and the adventure works database in the Connection Manager.
6. Click ok twice to get back to the Execute SQL Task.
7. Enter “Select 1” as the SQL statement
(This query will not pull any data. It is just used to test the connection)
8. Click ok and debug the package. The Execute SQL task should turn Green indicating success.
Now you will create a configuration table to feed the value of the connection to the package.
9. Stop the package from debugging.
10. Right click in the connection manager and select new OLEDB connection
11. Create a connection to a blank database. In this example we will use a database called Config.
12. Right click in the control flow and select Package Configurations
13. Place a check next to Enable Package Configurations.
14. Click add; (Click next if the welcome screen appears.)
15. Select SQL Server in the Configuration Type drop down menu.
16. Set the connection to the Config Database
17. Click the New button next to the Configuration Table Dropdown menu.
18. Click ok. This will create a table in the Adventure Works database.
19. In the configuration filter type Development.
+
20. Click next
21. Place a check next to the connection string property of the adventure works connection
22. Click Next, Finish, and Close.
23. Debug the package again, the Execute SQL Task will still turn Green
The Execute SQL task is not using the connection saved in the connection manager. It is using the value that was saved in the config database on the SSIS Configurations table. You do not have to leave the name of this table. In fact most businesses have a practice of no spaces in table names. So you could have created the table name SSISConfig or any name you prefer. You can test where the package is getting the adventure works connection string by changing the connection on the package to a database that does not exist.
24. Double click on the adventure works connection in the connection manager.
25. Change the database name to adventureworks1
26. Debug the package and you should see the Execute SQL task turn Green.
(There is a blue dot next to the Adventureworks2008 connection indicating the connection is fed from the configuration manger, this is a feature of because BI xPress.)
27. Open SQL Server Management Studio and view the values in the table.
28. Return to the SSIS package and make a copy of the package in the project. Click on the package in the Solution explorer and pres Ctrl+C then Ctrl+V. A copy of the package will appear below it in the project.
29. Double Click on the package
30. Debug this package.
Notice this new package runs successfully. It is using the same configuration table the first package is using. If you make a change to the table both packages will be updated. This is a major time saver in maintaining the packages in the future. Imagine having one hundred packages that have a single change like the name of a server or database name. Updating these packages becomes a one minute task instead of a one hour task. A proper configuration setup also saves you from having to redeploy the packages.
We are going to look at a simple example of passing information to a package with a configuration table. Then we will go over using configuration tables on multiple packages. Imagine running dozens of packages that point to a server and the server name changes. If you have a configuration table that is feeding this server name to every package you can make a single change to the configuration table and all the packages are updated. This can reduce your maintenance time significantly.
We are going to build a couple of simple packages with connections. These packages will connect to the adventure works database which is freely available on codeplex. Since you will not being pulling any data you can use any database you would like:
1. Drag in an Execute SQL Task into a blank SSIS package.
2. Double click on the Execute SQL Task to edit it.
3. Click on the connection drop down menu and select New Connection.
4. Click New.
5. Enter in your server name and the adventure works database in the Connection Manager.
6. Click ok twice to get back to the Execute SQL Task.
7. Enter “Select 1” as the SQL statement
(This query will not pull any data. It is just used to test the connection)
8. Click ok and debug the package. The Execute SQL task should turn Green indicating success.
Now you will create a configuration table to feed the value of the connection to the package.
9. Stop the package from debugging.
10. Right click in the connection manager and select new OLEDB connection
11. Create a connection to a blank database. In this example we will use a database called Config.
12. Right click in the control flow and select Package Configurations
13. Place a check next to Enable Package Configurations.
14. Click add; (Click next if the welcome screen appears.)
15. Select SQL Server in the Configuration Type drop down menu.
16. Set the connection to the Config Database
17. Click the New button next to the Configuration Table Dropdown menu.
18. Click ok. This will create a table in the Adventure Works database.
19. In the configuration filter type Development.
+20. Click next
21. Place a check next to the connection string property of the adventure works connection
22. Click Next, Finish, and Close.
23. Debug the package again, the Execute SQL Task will still turn Green
The Execute SQL task is not using the connection saved in the connection manager. It is using the value that was saved in the config database on the SSIS Configurations table. You do not have to leave the name of this table. In fact most businesses have a practice of no spaces in table names. So you could have created the table name SSISConfig or any name you prefer. You can test where the package is getting the adventure works connection string by changing the connection on the package to a database that does not exist.
24. Double click on the adventure works connection in the connection manager.
25. Change the database name to adventureworks1
26. Debug the package and you should see the Execute SQL task turn Green.
(There is a blue dot next to the Adventureworks2008 connection indicating the connection is fed from the configuration manger, this is a feature of because BI xPress.)
27. Open SQL Server Management Studio and view the values in the table.
28. Return to the SSIS package and make a copy of the package in the project. Click on the package in the Solution explorer and pres Ctrl+C then Ctrl+V. A copy of the package will appear below it in the project.
29. Double Click on the package
30. Debug this package.
Notice this new package runs successfully. It is using the same configuration table the first package is using. If you make a change to the table both packages will be updated. This is a major time saver in maintaining the packages in the future. Imagine having one hundred packages that have a single change like the name of a server or database name. Updating these packages becomes a one minute task instead of a one hour task. A proper configuration setup also saves you from having to redeploy the packages.
No comments:
Post a Comment