Creating and Editing an XML Configuration File

As you've seen, creating and editing an XML configuration makes your package deployment more flexible. In the following procedures, creating an XML configuration file entails extracting data from a source table and then loading the data into a Microsoft Office Excel file. These transformation processes simulate the data-delivering routines that you might perform when working in a data warehouse or an enterprise environment.

Opening the SSIS Project and Executing the Package

You cannot run the Package Installation Wizard until you create an SSIS project. In this procedure, you'll create a new SSIS project to which you will add a data flow task.

Create a New SSIS Project

1. To open the SSIS project, in Windows Explorer, navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\.

2. Double-click  Chap08.sln. The project opens in Business Intelligence Development Studio (BIDS).

3. In BIDS, locate the Solution Explorer window on the right side of the design environment. Make sure this window is visible and not autohidden so you can see all of the files in the project.

4. Find the  NewProducts.dtsx file under the SSIS Packages folder. Double-click  NewProducts.dtsx to open this package in the designer.

   You have now opened the project. It's time to execute the project.

5. In Solution Explorer, right-click  NewProducts.dtsx, and then click Execute Package.

   Wait until the package has executed successfully before starting the next step.

   Notice that in the design window, as each task is completed successfully, the color of the task blocks changes from yellow to green. When the package has executed successfully, the task blocks should be green, and the bottom of the screen should read "Package execution completed. Click here to switch to design mode, or select Stop Debugging from the Debug menu." Your screen should resemble the following illustration:

   

   When a package is executed, it creates an output file.

6. To view the output file, switch to Windows Explorer.

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

8. Double-click  ProductsDestination.txt.

   Notice that there are 62 records in the QuickStartODS database ProductNames table.

9. Switch to BIDS.

10. On the Debug menu, click Stop Debugging.

Creating an XML Configuration File

In this procedure, you'll create an XML configuration file named  NewProducts.dtsConfig. You'll then configure the XML configuration file to store the ConnectionString property.

Create an XML Configuration File

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

2. In the Package Configurations Organizer, select the Enable Package Configurations check box.

   Note 

   Configurations can be enabled or disabled for a package configuration. If you disable a package configuration, the property values stored in the package are used, and the property values in the configurations are ignored.

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:

   1. In the Configuration Type drop-down list, select XML Configuration File.

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

   3. In the Configuration File Name text box, type C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\NewProducts.dtsConfig.

   Your screen should look similar to this:

   

   You have now selected the type of configuration to create.

6. Click Next. The Select Properties To Export page appears.

7. On the Select Properties To Export page:

   1. In the Objects pane, locate the Connection Managers folder, expand ProductsDestination, and then expand the Properties folder.

   2. Select the ConnectString check box.

   You have now selected the properties that will be exported to the configuration file.

   Your screen looks like this:

   

   Notice that in the Property Attributes pane, the Name, Type, and Value of the ProductsDestination connection manager connection string is displayed. These values will be written to the XML configuration file.

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

9. To specify the configuration name, on the Completing The Wizard page, in the Configuration Name text box, type DestinationFile.

10. Click Finish to close the Completing The Wizard page.

    

11. Click Close to close the Package Configurations Organizer.

Editing the XML Configuration File

In this procedure, you'll edit the XML configuration file by replacing the ProductDestination.txt file with the XMLProductDestination.txt file. This change will enable the package to write product records to the  XMLProductsDestination.txt file.

Edit the XML Configuration File

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

2. Right-click  NewProducts.dtsConfig, click Open With, select Microsoft Visual Studio Version Selector, and then click OK.

3. In SSIS, on the  NewProducts.dtsConfig tab, find <ConfiguredValue> in the XML file:

   1. On the Edit menu, click Find and Replace, and then click Quick Find.

   2. In the Find and Replace box, in the Find What box, type <ConfiguredValue>.

   3. Click Find Next.

   4. <ConfiguredValue> is highlighted in the XML file.

   Notice the values of the attributes contained in the <Configuration> tag.

4. Between the <ConfiguredValue> tags, locate the destination file named  ProductsDestination.txt.

5. Rename the destination file  ProductsDestination.txt  XMLProductsDestination.txt.

6. On the File menu, click Save  NewProducts.dtsConfig.

7. Close the SSIS file.

   Note 

   When this configuration is enabled, the package writes product records to the  XMLProductsDestination.txt file.

Testing the Package with the New Configuration

Now that you have created and edited the XML file, you need to test the package to ensure that it is functioning properly and that it created the correct .txt file.

Test the Package

1. Switch to BIDS.

2. In BIDS, in the Solution Explorer pane, right-click  NewProducts.dtsx, and then click Execute Package.

3. Wait until the package has executed successfully.

4. Switch to Windows Explorer.

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

6. In the Data folder, verify that the package created the  XMLProductsDestination.txt file.