Step 2: Enabling and Configuring Package Configurations
Applies To: SQL Server 2016
In this task, you will convert the project to the Package Deployment Model and enable package configurations using the Package Configuration Wizard. You will use this 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 a new package-level variable that you can update at run time. Additionally, you will populate a new sample data folder to use during testing.
Click the background of the Control Flow tab in SSIS Designer. This sets the scope for the variable you will create to the package.
On the SSIS menu, select Variables.
In the Variables window, click the Add Variable icon.
In the Name box, type varFolderName.
Variable names are case sensitive.
Verify that the Scope box shows the name of the package, Lesson 5.
Set the value of the Data Type box of the
varFolderNamevariable to String.
Return to the Control Flow tab and double-click the Foreach File in Folder container.
On the Collection page of the Foreach Loop Editor, click Expressions, and then click the ellipsis button (…).
In the Property Expressions Editor, click in the Property list, and select Directory.
In the Expression box, click the ellipsis button(…).
In the Expression Builder, expand the Variables folder, and drag the variable User::varFolderName to the Expression box.
Click OK to exit the Expression Builder.
Click OK to exit the Property Expressions Editor.
Click OK to exit the Foreach Loop Editor.
On the Project Menu, click Convert to Package Deployment Model.
Click OK on the warning prompt and, once the conversion is complete, click OK on the Convert to Package Deployment Model dialog.
Click the background of the Control Flow tab in SSIS Designer.
On the SSIS menu, click Package Configurations.
In the Package Configurations Organizer dialog box, select Enable Package Configurations, and then click Add.
On the welcome page of the Package Configuration Wizard, click Next.
On the Select Configuration Type page, verify that the Configuration type is set to XML configuration file.
On the Select Configuration Type page, click Browse.
By default, the Select Configuration File Location dialog box will open to the project folder.
In the Select Configuration File Location dialog box, for File name type SSISTutorial, and then click Save.
On the Select Configuration Type page, click Next.
On the Select Properties to Export page, in the Objects pane, expand Variables, expand varFolderName, expand Properties, and then select Value.
On the Select Properties to Export page, click Next.
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 dialog box.
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.
A configuration file typically contains complex information about the package properties, but for this tutorial the only configuration information should be
In Windows Explorer, at the root level of your drive (for example, C:\), create a new folder named New Sample Data.
Locate the sample files on your computer and copy three of the files from the folder.
In the New Sample Data folder, paste the copied files.