SSIS Tutorial - Package configuration problem

Integration Services Tutorial

An issue in the CTP 15 release of SQL Server 2005 Integration Services prevents the package configuration from working the way it is described in the Integration Services tutorial in Books Online. When you do the tutorial, please use the modified steps described here instead of the steps included in the CTP release of Books Online.

In the Lesson 3: Adding Package Configurations topic

In this lesson, you will modify the simple Integration Services package that you created in Lesson 2: Adding Looping to take advantage of package configurations. The basic steps are as follows:

1. Create a package-level variable mapped to the Directory property.
2. Using the Package Configuration Wizard, create an XML configuration that updates the Directory property of the Foreach Loop container.
3. Modify the value of the variable from outside of the development environment and 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 in turn updates the Directory property. As a result, the package iterates through the files in the new data folder, rather than iterating through the files in the original hard-coded file location.

In Lesson 3, the Enabling and Configuring Package Configurations topic

In this task, you will create a package-level variable for use in updating the package, You then 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. This sets the scope to the package for the variable you will create.
2. On the SSIS menu, click Variables.
3. In the Variables window, click the Add Variable icon.
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. Return to the Control Flow tab and double-click the Foreach File in Folder container.
8. On the Collection page, click Expressions.
9. In the Property Expressions Editor, in the Property list, select Directory.
10. In the Expression box, click the empty cell and then click the ellipsis button (…).
11. In the Expression Builder, expand the Variables folder, and drag the variable User:varFolderName 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, select Enable Package Configurations, and then click Add.
4. On the welcome page of 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 select 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 is the configuration name that is displayed in the Package Configuration Organizer.
13. Click Finish.
14. Click Close.
15. The wizard creates a configuration file, named SSISTutorial.dtsConfig, that contains configuration settings for the value of the variable that in turn sets the Directory property of the enumerator.

To create and populate a new sample data folder

1. In Windows Explorer, at the root level of your drive (for example, C:\), create a new folder named New Sample Data.
2. Open the c:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data folder and then copy three of the sample files from the folder.
3. In the New Sample Data folder, paste the copied files.

In Lesson 3: the Modifying the Directory Property Configuration Value topic

In this task, you will modify the configuration setting, stored in the SSISTutorial.dtsConfig file, for the Value property of the package-level variable User::varFolderName. 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 previous task. After you modify the configuration setting and run the package, the Directory property will be updated by the variable, using the value populated from the configuration file instead of the directory value originally configured in the package.

To modify the configuration setting of the Directory property

1. In Notepad or any other text editor, locate and open the SSISTutorial.dtsConfig configuration file that you created by using the Package Configuration Wizard in the previous task.
2. Change the value of the ConfiguredValue element to match the path of the New Sample Data folder that you created in the previous task. Do not surround the path in quotes.
3. Save the change, and then close the text editor.

In Lesson 4: Copying the Lesson 3 Package topic

In this task, you will create a copy of the Lesson 3.dtsx package within the SSIS Tutorial project. You will use this new copy throughout the rest of Lesson 4.

Because package configuration information is copied along with the package itself, you must also modify the package configuration to reverse one of the changes made in the preceding lesson, and point the ForEach loop back to the original Sample Data folder.

To modify the package configuration

1. In Notepad or any other text editor, locate and open the SSISTutorial.dtsConfig configuration file that you created by using the Package Configuration Wizard in the previous lesson.
2. Change the value of the ConfiguredValue element back to the original sample data folder. By default, the sample data is installed to 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.

Marianne at 2007-9-8 >

Hello,

Others have reported difficulties with this as well. We have added the following information to the tutorial topic named Modifying the Directory Property Configuration Value.

Change the value of the ConfiguredValue element to match the path of the New Sample Data folder that you created in the previous task. Do not surround the path in quotes. If the New Sample Data folder is at the root level of your drive (for example, C:\), the updated XML should be similar to the following sample:

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="Domain\UserName" GeneratedFromPackageName="Lesson 3" GeneratedFromPackageID="{99396D72-2F8D-4A37-8362-96346AD53334}" GeneratedDate="11/12/2005 12:46:13 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Variables[User::varFolderName].Properties[Value]" ValueType="String"><ConfiguredValue>C:\New Sample Data</ConfiguredValue></Configuration></DTSConfiguration>

The updated version will be available in the Spring release of Books Online.

Hope this information helps you out.

Marianne

SQL Server User Education

Marianne at 2007-9-8 >

After following the suggestions on this document, I still have a problem on reading the Currency_*.txt files included in the C:\New Data Sample path.

When I run the SSIS package for Lesson 3 in debug mode I'm getting the following error:

[Extract Sample Currency Data [1]] Information: The processing of file "C:\New Sample Data" has started.

[Extract Sample Currency Data [1]] Warning: Access is denied.

[Extract Sample Currency Data [1]] Error: Cannot open the datafile "C:\New Sample Data".

Does anybody have a clue why ? I'm stuck, I've tryed several different options (changing the file name, granting full auth to the folder) with no luck.

Any help will be really appreciated.

Thanks

WWally at 2007-9-8 >