Section 7

Configuring Packages

  • Lesson 44: Easing Deployment with Configuration Tables
  • Lesson 45: Easing Deployment with Configuration Files
  • Lesson 46: Configuring Child Packages

Chapter 44

Easing Deployment with Configuration Tables

Once you have completed a set of packages, the challenge is to deploy those packages to a production environment without having to manually reconfigure them for that environment. For example, you may have to get source files from a different directory or the source databases may be on a different server than your production environment.

NOTE New project deployment options in SSIS 2012 use parameters and environments. These are covered in Lessons 33, 53, 54, and 57. This chapter and the next cover configuration tables and configuration files, which are still supported in the current release. You must decide if the packages in your project will use the new project deployment model or the package deployment model.

Configuration tables help automate migration and reduce the risk of deployment-related errors. You can use a configuration table that contains the ConnectionString property value for each connection. Each package that uses the connection can obtain the ConnectionString from the configuration table. As part of the initial deployment to production, you create another configuration table, like the one used in development. You then copy the rows from the development configuration table to the production configuration table, changing the values for the ConnectionStrings to point to production sources and destinations. When the packages are moved to production, you can change the configuration table’s server name from the development server to the production server.

This capability is not limited to connection strings. The SSIS Package Configuration option enables you to store any SSIS property for the package, connection, container, variable, or any task into a table. You will have a configuration table in the development environment that contains values for all of the properties that need to be changed for production. A similar configuration table will exist in production, but you will set values of the properties appropriately for production.

For example, say your source database server name in the development environment is TestSQL and in production it is Prod. Your configuration table in both environments would contain a row that defines the connection property of the source database connection manager. However, the values contained in the row in the development environment refer to TestSQL, and the production row refers to Prod. While the package is in development, the development configuration table is used. When the package is moved to the production server, it is configured to use the production configuration table. Once set up, this process makes deployment easier, repeatable, and less error-prone.

Here is an example where you use a configuration table to set the connection string for your source database.

First, create a project and an empty package. Add a connection manager to the package. For this example, you can use any database on any server. After you have created the connection manager, change its name to SourceDatabase.

To use tables for configuration, your project must use the package deployment model. Because this is not the default, you must convert your project. Right-click your project and choose Convert to Package Deployment Model. You can see how to do this in Figure 44-1.

Figure 44-1

c44f001.tif

SSIS performs a check to ensure the packages in your project are compatible with package deployment and provides a report, as in Figure 44-2. Click OK. All of the packages within this project will be converted.

Figure 44-2

c44f002.tif

Create another connection manager that points to the development server where you will place the configuration table. Name this configuration manager ConfigurationDatabase.

NOTE It is a best practice to use a separate connection manager for the configuration database and to use this connection manager only for configuration. This is true even if the configuration table exists in a database that is also used as a source database.

First you create the configuration table and then set the connection property for the SourceDatabase Connection Manager.

To create the configuration table for a package, right-click in the blank area of the package in the Control Flow window and choose Package Configurations, as shown in Figure 44-3.

Figure 44-3

c44f003.tif

This action opens the Package Configurations Organizer. This window enables you to create, edit, and delete package configurations. Check the Enable package configurations check box, as shown in Figure 44-4.

Figure 44-4

c44f004.tif

To create the connection to the configuration table, click the Add button at the bottom of the Package Configurations Organizer. This starts the Package Configuration Wizard. The first time you add a configuration table, you see a welcome screen; you can check the option to not show this page again if you prefer. Click Next to move on.

On the next screen, select SQL Server from the Configuration Type drop-down menu. With SQL Server as the type, you can create your connection table in any SQL Server database. You can click the New button to the right of the Connection drop-down to create an OLE DB connection to a SQL Server instance. Choose the ConfigurationDatabase Connection Manager you created earlier. This connection is strictly for the package’s connection to the configuration table. Data in this table contains connection strings and other configuration property values. You will store the connection string for the SourceDatabase in this table.

After you have selected the SQL connection, select the table to use. There is a default table named SSIS_Configurations. You can choose a preexisting configuration table in the database, if one exists. You can also create a new table. To create a new table, click the New button next to the Configuration Table drop-down menu. This opens the SQL query that creates the table.

Many packages may use the same configuration table. You can group collections of properties together and give them a name called a filter name. A package may request a particular set of configuration rows by using the filter name. You can choose a previously defined collection by choosing the filter from the drop-down, or you can type in a new filter name. You need to make this name broad yet descriptive. You will see this filter listed when you set up configuration rows in this table for other packages. In this example, this group of configurations contains only the connection string for your source database, which is your Enterprise Resource Planning (ERP) system. Therefore, you will create a filter called “ERPSource.” Once you select the SQL Server and the table, the window should look like Figure 44-5.

Figure 44-5

c44f005.tif

After you click Next, you see the list of objects in the package and the attributes for those objects. You can place a check next to each attribute you want to include for this filter in the objects list in the left pane. In the right pane, type the value that will be stored in the configuration table for the selected property. Check the ConnectionString property for the SourceDatabase connection. Then you can set the value for this property in the right pane, as in Figure 44-6.

Figure 44-6

c44f006.tif

Now you can click Next and go to the final configuration window, where you can name the configuration. This name is used for reference in this package only. You also see the configuration type, connection name, table, filter, and the target property (Figure 44-7).

Figure 44-7

c44f007.tif

Click Finish to return to the Package Configurations Organizer. You can now see the new configuration listed in this window, as shown in Figure 44-8. Your configuration contains the connection string that is used to connect to the source database for your extract process. The Add, Edit, and Remove buttons at the bottom of this window enable you to alter or remove the existing configuration tables and add new ones.

Figure 44-8

c44f008.tif

Once you have a configuration defined for a package, the package will use the property values from the configuration table instead of the design-time values stored in the package.

NOTE A common issue in troubleshooting occurs when you have a problem with a connection. It seems as if the data is coming from or going to the wrong place. You change the connection string in the package, but the behavior remains unchanged. You may have forgotten that the connection information in the configuration table is overriding the settings in the package. If you find yourself troubleshooting a connection, it is a good idea to disable package configurations first.

If some properties included in the filter group do not exist in the package, they are simply ignored. For instance, if your filtered collection contains some properties for a connection named DestinationDB and that connection does not exist in your package, there is no error, and those configuration rows are not used for this package.

Try It

In this Try It, you learn how to create a configuration table and use the data from the configuration table. After this lesson, you should understand how configuration tables are used to pass information into a package.

The completed packages for this lesson are available for download on the book’s website at www.wrox.com.

Lesson Requirements

In this lesson, you create a simple package with a Script Task that pops up a message with the configuration value instead of the value saved in the package. Then you create a configuration table and run the package to see the value in the configuration table.

Hints

  • You need only a Script Task.
  • The value of the string is the value used in the configuration table.

Step-by-Step

1. Create a package named Lesson44ScriptTask. Create a String variable named strConfigTest in a new package. The package must be contained in a project that has been converted to the package deployment model, described earlier in the lesson.
2. Choose String as the data type and set the value of the variable to HardCoded.
3. Drag a Script Task into the Control Flow and double-click it.
4. Select Microsoft Visual Basic 2010 as the script language for the task.
5. Select the variable you just created as a read-write variable in the ReadWriteVariable field.
6. Click the Edit Script button and type the following code in the Script Task where it states “Add your Code here”: 
MsgBox(Dts.Variables(“strConfigTest”).Value)
7. Close the Script Task Editor and click OK in the Script Task.
8. Right-click in the Control Flow and select Package Configurations.
9. Check Enable Package Configurations. Click Add. Click Next in the wizard welcome screen (if it appears).
10. Set the Configuration Type as SQL Server.
11. Set the SQL Connection to AdventureWorks2012. You may have to create a new connection.
12. Select the SSIS_Configurations table (click New and create it if does not exist).
13. Set the Configuration Filter to strConfigTestVariable. Click Next.
14. Place a check in the Value attribute of the strConfigTest variable, as in Figure 44-9. (Do not provide an actual value.)
15. Click Next.

Figure 44-9

c44f009.tif
16. Name the configuration Config Variable. Click Finish.
17. Click Close in the Package Configurations Organizer.
18. Run the package and a popup appears with the text “HardCoded.” Click OK to close the popup. Switch back to design mode.
19. Open SQL Server Management Studio.
20. Navigate to the SSIS_Configurations table in the AdventureWorks2012 database. Right-click the table and select Edit Top 200 Rows.
21. Change the Configured Value from HardCoded to Config Data. Press Enter.
22. Run the package again, and you should see a popup box with the text “Config Data.”

Final Deployment

In most cases, the connections you need will be different in each environment. For example, in development, you may have a SQL server named Dev. However, in production the server is named Prod. As you move your package from development to production, the connections will have to change. To reduce the possibility of newly introduced errors, you should be able to change the configuration from development to production without having to open, edit, and re-save your package. This is how you handle the final deployment to production.

After you have created the configuration tables in both development and production and set the values appropriately, you have one more thing to do. The connection string that points to the configuration table will need to be different in production. You can set this in several ways:

  • Provide the connection string in SQL Agent.
  • Provide the connection string on the command line when using DTExec or DTExecUI.
  • Create an environment variable with the same name on both the development and production servers. The value of this variable is the connection string that points to the configuration table. Then you can create another package configuration that sets the configuration table connection string from the value obtained from the environment variable.

You can learn more about deployment and scheduling of packages in Lessons 53 and 59.

Please select Lesson 44 on the DVD, or online at www.wrox.com/go/ssis2012video, to view the video that accompanies this lesson.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset