Creating Multiple Configuration Files


In the following procedures, you'll create SQL Server configurations. To use SQL Server configurations, first you must designate a SQL Server database to contain the configurations table. After you have created the database, you'll use environment variable configurations to designate this database. Finally, you'll create parent package variable configurations.

Creating the Database and the OLE DB Connection Manager

In this procedure, you'll create a new database named ConfigDB, and then create a connection manager named LocalHost.ConfigDB.

Create a New Database
  1. In SQL Server Management Studio, create a new database named ConfigDB:

    1. In Object Explorer, right-click the Databases folder, and then click New Database.

    2. In the New Database dialog box, in the Database Name text box, type ConfigDB.

      image from book

      Notice that in the New Database dialog box, in the Database Files pane, the new database ConfigDB is listed.

    3. Click OK to close the New Database dialog box.

  2. In BIDS, create a connection manager named LocalHost.ConfigDB:

    1. In Solution Explorer, double-click image from book NewProducts.dtsx to open the package designer.

    2. In the design environment, in the Connection Managers pane, right-click anywhere, and then click New OLE DB Connection. The Configure OLE DB Connection Manager dialog box appears.

    3. In the Configure OLE DB Connection Manager dialog box, click New. The Connection Manager dialog box appears.

    4. In the Connection Manager dialog box, in the Server Name drop-down list, type LocalHost.

    5. In the Connect To A Database frame, ensure that the Select Or Enter A Database Name option is selected, and then select configDB.

      image from book

    6. Click OK to close the Connection Manager dialog box.

    7. In the Configure OLE DB Connection Manager dialog box, click OK.

    8. Notice that LocalHost.ConfigDB appears on the Connection Managers pane in BIDS.

Creating the Environment Variable

In this procedure, you'll create a database environment variable named ConfigDBName.

Create a Database Environment
  1. Click Start, click My Computer, right-click anywhere in the window, and then click Properties. The System Properties dialog box opens.

  2. In the System Properties dialog box, at the bottom of the Advanced tab, click Environment Variables. The Environment Variables dialog box opens.

  3. In the Environment Variables dialog box, in the System Variables frame, click New. The New System Variable dialog box opens.

  4. In the New System Variable dialog box, in the Variable name box, type ConfigDBName.

  5. In the Variable value box, type ConfigDB.

    Your screen should look like this:

    image from book

  6. Click OK to close the New System Variable dialog box.

  7. Click OK to close the Environment Variables dialog box.

  8. Click OK to close the System Properties dialog box.

    Tip 

    You can verify that you have successfully created the environment variable by running the SET command in a Windows Command Prompt window. This command enables you to view all the Windows environment variables and their values.

  9. In BIDS, on the File menu, click Save All, and then close BIDS.

  10. In Windows Explorer, go to C:\Document and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08 and double-click image from book Chap08.sln to restart BIDS. BIDS opens.

  11. In Solution Explorer, double-click image from book NewProducts.dtsx to open the package designer.

    Tip 

    Restarting refreshes the list of environment variables in BIDS.

Creating the Environment Variable Configuration

In this procedure, you'll configure the environment variable named ConfigDBName that you created in the previous procedure.

Configure the Environment Variable
  1. In BIDS, on the SSIS menu, click Package Configurations. The Package Configurations Organizer opens.

  2. In the Package Configurations Organizer, ensure that the Enable Package Configurations check box is selected.

  3. Click Add to start the Package Configuration Wizard. The Welcome To The Package Configuration Wizard page appears.

  4. On the Welcome To The Package Configuration Wizard page, click Next. The Select Configuration Type page appears.

  5. On the Select Configuration Type page, in the Configuration Type drop-down list, select Environment Variable.

  6. In the Environment Variable drop-down list, select ConfigDBName.

    Your screen should look like this:

    image from book

  7. Click Next. The Select Target Property page appears.

  8. On the Select Target Property page, in the Objects pane, locate the Connection Managers folder, expand LocalHost.ConfigDB, expand the Properties folder, and then click InitialCatalog.

    Notice that on the right side of the screen, in the Property Attributes pane, the Name, Type, and Value of the LocalHost.ConfigDB connection manager InitialCatalog (database name) property is displayed.

  9. Click Next. The Completing The Wizard page appears.

  10. On the Completing The Wizard page, in the Configuration Name text box, type ConfigDBName.

    image from book

  11. Click Finish to close the wizard.

  12. Leave the Package Configurations Organizer open.

Creating the SQL Server Configuration

In this procedure, you'll create a SQL Server table in the ConfigDB database SSIS configurations table, and then change image from book NewProducts.txt to image from book NewProductsShortList.txt.

Create a SQL Server Table
  1. In the Package Configurations Organizer, ensure that the Enable Package Configurations check box is selected.

  2. Click Add to start the Package Configuration Wizard. The Welcome To The Package Configuration Wizard page appears.

  3. On the Welcome To The Package Configuration Wizard page, click Next. The Select Configuration Type page appears.

  4. On the Select Configuration Type page, in the Configuration Type drop-down list, select SQL Server.

  5. Ensure that the Specify Configuration Settings Directly option is selected.

  6. In the Connection drop-down list, select LocalHost.ConfigDB.

  7. Next to the Configuration Table drop-down list, click New. The Create Table box opens.

    Your screen should look similar to this:

    image from book

  8. Click OK. The Package Configuration Wizard page appears.

  9. In the Package Configuration Wizard, verify that a table named [dbo].[SSIS Configurations] was created in the ConfigDB database and listed in the Configuration Table drop-down list.

  10. In the Configuration Filter drop-down list, type ProductsShortListFilter, and then click Next. The Select Properties To Export page appears.

  11. On the Select Properties To Export page, in the Objects pane, locate the Connection Managers folder, expand NewProducts, and then expand the Properties folder.

  12. Select the ConnectionString check box, and then click Next. The Completing The Wizard page appears.

  13. On the Completing The Wizard page, in the Configuration Name text box, type ProductsShortList.

  14. Click Finish to close the wizard.

  15. Close the Package Configurations Organizer.

  16. In SQL Server Management Studio, open the ConfigDB database SSIS Configurations table:

    1. On the left side of the screen, in Object Explorer, right-click the Databases folder, and then click Refresh.

    2. Expand the configDB folder, expand the Tables folder, right-click dbo.SSIS Configurations, and then click Open Table.

    Notice that the tab in the middle of the screen changes to read dbo.SSIS Configurations.

  17. On the Table - dbo.SSIS Configurations tab, in the ConfiguredValue column, click image from book NewProducts.txt, and then change the name to image from book NewProductsShortList.txt.

  18. Click the next record to save your changes.

Testing the Package with the New Configuration

Now that you have created and configured the package, it's time to test the new configuration.

Test the New Configuration
  1. In Windows Explorer, delete the file named C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\Data\XMLProductsDestination.txt.

  2. In BIDS, in the Connection Managers pane, right-click the LocalHost.ConfigDB connection manager, and then click Edit. The Connection Manager dialog box appears.

  3. In the Connect To A Database frame, in the Select Or Enter A Database Name drop-down list, select is2005sbsDW, and then click OK.

    The LocalHost.ConfigDB connection manager now points to the wrong database. When the package runs, the ConfigDBName configuration will change the LocalHost.ConfigDB connection manager so that it points to the ConfigDB database.

  4. In Solution Explorer, right-click image from book NewProducts.dtsx, and then select Execute Package. Wait until the package has executed successfully.

  5. On the Debug menu, click Stop Debugging.

  6. Switch to SQL Server Management Studio.

  7. In SQL Server Management Studio, in Object Explorer, expand QuickStartODS, expand Tables, and then right-click dbo.ProductNames and click Open table.

    Notice that the tab in the middle of the screen changes to read dbo.ProductNames.

  8. On the toolbar, click the red exclamation mark (!) (Execute SQL).

    On the Table - dbo.ProductNames tab, SQL populates the table with data.

  9. Verify that the table contains only 42 records, as shown in the following screen shot.

    image from book

  10. Switch to Windows Explorer.

  11. Navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\Data.

  12. Ensure that the file named image from book XMLProductsDestination.txt was created in the Data folder.

Exploring the Parent Package

Before you create the parent package variable configuration, it's important to view the image from book ParentPackage.dtsx package, which contains an Execute Package task that runs the image from book NewProducts.dtsx package.

View the ParentPackage.dtsx Package
  1. In BIDS, in Solution Explorer, double-click the image from book ParentPackage.dtsx package to open the package designer.

  2. In the Connection Managers pane, right-click image from book NewProducts.dtsx, and then click Edit. The File Connection Manager Editor box appears.

  3. In the File Connection Manager Editor box, view the Usage Type and File properties of NewProducts.dstx.

    The image from book NewProducts.dtsx file connection manager references the image from book NewProducts.dtsx package.

  4. Click OK to close the File Connection Manager Editor box.

  5. In the package designer, right-click Execute Package Task - NewProducts, and then click Edit. The Execute Package Task Editor appears.

  6. In the Execute Package Task Editor box, view its properties, such as General, Package, and Expressions.

    The Execute Package Task - NewProducts task executes the package referenced by the image from book NewProducts.dtsx connection manager.

  7. Click OK to close the Execute Package Task Editor.

  8. Right-click anywhere in the package designer, click Variables, and then view the ParentProductsDestination variable properties.

  9. The ParentProductsDestination variable contains a fully qualified file name. You might need to expand the Variables pane to view the variable properties.

Creating the Parent Package Variable Configuration

In this procedure, you'll create the ParentProductsDestination parent package variable configuration.

Create the ParentProductsDestination Parent Package
  1. In BIDS, in Solution Explorer, double-click the image from book NewProducts.dtsx package to open the package designer.

  2. On the SSIS menu, click Package Configurations. The Package Configurations Organizer opens.

  3. In the Package Configurations Organizer, ensure that the Enable Package Configurations check box is selected.

  4. Click Add to start the Package Configuration Wizard. The Welcome To The Package Configuration Wizard appears.

  5. On the Welcome To The Package Configuration Wizard page, click Next. The Select Configuration Type page appears.

  6. On the Select Configuration Type page, in the Configuration Type drop-down list, click Parent Package Variable.

  7. Ensure that the Specify Configuration Settings Directly option is selected.

  8. In the Parent Variable text box, type ParentProductsDestination.

  9. Click Next. The Select Target Property page appears.

  10. On the Select Target Property page, in the Objects pane, expand the Connection Managers folder, expand ProductsDestination, expand the Properties folder, and then click ConnectionString.

  11. Click Next. The Completing The Wizard page appears.

  12. On the Completing the Wizard page, in the Configuration name box, type ParentDestinationFile.

    Your screen should look similar to this:

    image from book

  13. Click Finish to close the Package Configuration Wizard.

    The package now has two configurations that modify the destination file name. The last configuration to modify a property is applied to the package.

  14. Click Close to close the Package Configurations Organizer.

  15. On the File menu, click Save All.

Test the Package with the New Configuration
  1. In Windows Explorer, delete the file named C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\Data\XMLProductsDestination.txt.

  2. In BIDS, in Solution Explorer, right-click image from book ParentPackage.dtsx, and then click Execute Package. Wait until the package has executed successfully.

  3. In Windows Explorer, navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\Data.

  4. In the Data folder, verify that the package created the image from book ParentProductsDestination.txt file.

  5. In BIDS, on the Debug menu, click Stop Debugging.




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net