Microsoft KB Archive/903138

= Update to the Integration Services Tutorial from SQL Server 2005 CTP Books Online =

Article ID: 903138

Article Last Modified on 11/1/2005

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition Community Technology Preview
 * Microsoft SQL Server 2005 Developer x64 Edition Community Technology Preview
 * Microsoft SQL Server 2005 Enterprise Edition Community Technology Preview
 * Microsoft SQL Server 2005 Standard Edition Community Technology Preview
 * Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems Community Technology Preview
 * Microsoft SQL Server 2005 Standard x64 Edition Community Technology Preview

-





Beta Information
This article discusses a Beta release of a Microsoft product. The information in this article is provided as-is and is subject to change without notice.

No formal product support is available from Microsoft for this Beta product. For information about how to obtain support for a Beta release, see the documentation that is included with the Beta product files, or check the Web location where you downloaded the release.



SUMMARY
''An issue in the Community Technology Preview (CTP) 15 release of Microsoft SQL Server 2005 Integration Services prevents the package configuration from working the way that it is described in the Integration Services tutorial in Books Online. When you do the tutorial, use the modified steps that are described in this article instead of the steps that are included in the CTP release of Books Online.''



Lesson 3, Adding Package Configurations
In this lesson, modify the simple Integration Services package that you created in the &quot;Lesson 2: Adding Looping&quot; lesson to take advantage of package configurations. To do this, follow these steps:
 * 1) Create a package-level variable that is mapped to the Directory property.
 * 2) Use the Package Configuration Wizard to create an XML configuration that updates the Directory property of the Foreach Loop container.
 * 3) Modify the value of the variable from outside the development environment, and then point the modified property to a new sample data folder.
 * 4) Run the package again. The configuration file populates the value of the variable, and the variable then updates the Directory property. Therefore, the package iterates through the files that are in the new data folder instead of iterating through the files that are in the original hard-coded file location.

Lesson 3, Enabling and Configuring Package Configurations
In this task, you will create a package-level variable to update the package. Then, you use the Package Configuration Wizard to generate an XML configuration file that contains configuration settings for the Directory property of the Foreach Loop container. The value of the Directory property is supplied by the package-level variable that you created. Finally, you populate a new sample data folder to use during testing.

To create a new package-level variable mapped to the Directory property

 * 1) Click anywhere in the design surface of the Control Flow tab. Do this to set the scope to the package for the variable that you will create.
 * 2) On the SSIS menu, click Variables.
 * 3) In the Variables window, click Add Variable.
 * 4) In the Name box, type varFolderName.
 * 5) Verify that Scope shows the name of the package.
 * 6) Set the Data Type of the varFolderName variable to String.
 * 7) On the Control Flow tab, double-click the Foreach File in Folder container.
 * 8) On the Collection dialog box, click Expressions.
 * 9) In the Property Expressions Editor, click Directory in the Property list.
 * 10) In the Expression box, click the empty cell, and then click the ellipsis button (…).
 * 11) In the Expression Builder dialog box, expand the Variables folder, and then drag the User:varFolderName variable to the Expression box.
 * 12) Click OK.

To enable package configurations

 * 1) On the design surface, click any empty area.
 * 2) On the SSIS menu, click Package Configurations.
 * 3) In the Package Configurations Organizer dialog box, click to select the Enable Package Configurations check box, and then click Add.
 * 4) In the Package Configuration Wizard, click Next.
 * 5) On the Select Configuration Type page, verify that the Configuration type is set to XML configuration file.
 * 6) On the Select Configuration Type page, click Browse.
 * 7) By default, the Select Configuration File Location dialog box will open to the project folder.
 * 8) In the Select Configuration File Location dialog box, type SSISTutorial, and then click Save.
 * 9) On the Select Configuration Type page, click Next.
 * 10) On the Select Properties to Export page, in the Objects pane, expand Variables, expand varFolderName, expand Properties, and then click Value.
 * 11) On the Select Properties to Export page, click Next.
 * 12) On the Completing the Wizard page, type a configuration name for the configuration, such as SSIS Tutorial Directory configuration . This name is the configuration name that is displayed in the Package Configuration Organizer dialog box.
 * 13) Click Finish.
 * 14) Click Close.

The wizard creates a configuration file that is named SSISTutorial.dtsConfig. The SSISTutorial.dtsConfig file contains configuration settings for the value of the variable. This value sets the Directory property of the enumerator.

Note Typically, a configuration file contains complex information about the package properties. However, for this tutorial, the only information that is in the file should be [User::varFolderName].Properties[Value].

To create and populate a new sample data folder

 * 1) In Windows Explorer, create a new folder that is named New Sample Data at the root of the hard disk drive. For example, create the C:\New Sample Data folder.
 * 2) Open the C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data folder.
 * 3) Copy three of the sample files in the C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data folder.
 * 4) In the C:\New Sample Data folder, paste the copied files.

Lesson 3, Modifying the Directory Property Configuration Value
In this task, you will modify the configuration setting for the Value property of the package-level variable User::varFolderName. This setting is stored in the SSISTutorial.dtsConfig file. The variable updates the Directory property of the Foreach Loop container. The modified value will point to the New Sample Data folder that you created in the &quot;Create and populate a new sample data folder&quot; section. After you modify the configuration setting and then run the package, the Directory property will be updated by the variable, by using the value that was populated from the configuration file instead of using the directory value that was originally configured in the package.

To modify the configuration setting of the Directory property

 * 1) In Notepad or in another text editor, open the SSISTutorial.dtsConfig configuration file that you created by using the Package Configuration Wizard in the &quot;Enable package configurations&quot; section.
 * 2) Change the value of the ConfiguredValue element to match the path of the New Sample Data folder that you created in the &quot;Create and populate a new sample data folder&quot; section. Do not enclose the path in quotation marks.
 * 3) Save the change, and then close the text editor.

Lesson 4, Copying the Lesson 3 Package
In this task, you will create a copy of the Lesson 3.dtsx package within the SSIS Tutorial project. You will use this new package copy throughout the rest of Lesson 4. Because package configuration information is copied together with the package itself, you must also modify the package configuration to reverse one of the changes that were made in the previous lesson. Then, you must point the ForEach loop back to the original Sample Data folder.

To modify the package configuration

 * 1) In Notepad or in another text editor, open the SSISTutorial.dtsConfig configuration file that you created by using the Package Configuration Wizard in the &quot;Enable package configurations&quot; section.
 * 2) Change the value of the ConfiguredValue element back to the original sample data folder. By default, the sample data is installed in the C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data folder.
 * 3) Save the change, and then close the text editor.

Note You do not have to enclose the path with quotation marks in the XML configuration file.

