dts config file using SSIS Package
after developing SSIS Package (.dtsx file) if I need to deploy to all environments dynamically, then how can I create  .dts config file and mention properties in
September 3rd, 2015 11:36am

Hi MBH,

You need to either have it at design time or if you use package/project variables set them up programmatically.

Depends on the SSIS version. In SSIS 2012 and onward we have the SSIS Catalog that allows all the variables set programmatically. The deployment can be done via a script.Or in code Programmatically Create and Deploy SSIS 2012 Project into Catalog Using C#


Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 2:02pm

we are using SQL Server 2012 and Business Intelligence 2010 tool.

My client wants the config file similar to below, how to do this ?

<?xml version="1.0"?>

-<DTSConfiguration>


-<DTSConfigurationHeading>

<DTSConfigurationFileInfo GeneratedDate="8/26/2015 11:06:51 AM" GeneratedFromPackageID="{6D416C61-95D1-449F-AD9C-CA0E8C6A2B94}" GeneratedFromPackageName="SamsungInv" GeneratedBy="UserName"/>

</DTSConfigurationHeading>


-<Configuration ValueType="String" Path="\Package.Connections[Armconnection].Properties[InitialCatalog]" ConfiguredType="Property">

<ConfiguredValue>ArmDW_DEV</ConfiguredValue>

</Configuration>


-<Configuration ValueType="String" Path="\Package.Connections[ArmaninoConnection].Properties[ServerName]" ConfiguredType="Property">

<ConfiguredValue>dc-d2-dev-01</ConfiguredValue>

</Configuration>


-<Configuration ValueType="String" Path="\Package.Variables[User::FileName].Properties[Value]" ConfiguredType="Property">

<ConfiguredValue>InventOnHand</ConfiguredValue>

</Configuration>


-<Configuration ValueType="String" Path="\Package.Variables[User::FilePath].Properties[Value]" ConfiguredType="Property">

<ConfiguredValue>\\DC-SI-BIZ-01\Filedrop\AX\Samsung\InvOn\</ConfiguredValue>

</Configuration>

</DTSConfiguration>

ile in xml format

September 3rd, 2015 2:22pm

You would need to enable the XML file config as in https://msdn.microsoft.com/en-us/library/ms166598.aspx?f=255&MSPPError=-2147217396

But it will not comply with the XML provided as SSIS 2012 uses its own XML structure.

If you decide to address the customer needs than you need to roll your own mechanism to sync their file to SSIS' one perhaps by the means of an user interface or some kind.

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 2:44pm

Setting Up Your XML Configuration File

After youve set up your package, the first step in setting up the XML configuration file is to enable package configurations. To do so, click the Package Configurations option on the SSIS menu. This launches the Package Configuration Organizer, shown in Figure 4.

The Package Configuration Organizer in SSIS

Figure 4: The Package Configuration Organizer in SSIS

To enable package configurations on your package, select the Enable package configurations checkbox. You can then add your package configurations to the package. To do so, click Add to launch the Package Configuration wizard. When the wizard appears, click Next to skip the Welcome screen. The Select Configuration Type screen will appear, as shown in Figure 5.

The Select Configuration Type screen in the Package Configuration wizard

Figure 5: The Select Configuration Type screen in the Package Configuration wizard

From the Configuration type drop-down list, select XML configuration file. You can then choose to specify your configuration settings directly or specify a Windows environment variable that stores the path and file names for the configuration file. For this example, I selected the Specify configuration settings directly option and specified the following path and file name: C:\Projects\SsisConfigFiles\LoadPersonData.dtsConfig. The main thing to notice is that the file should use the extension dtsConfig.

NOTE: If you specify an XML file that already exists, youll be prompted whether to use that file or whether to overwrite the files existing settings and use the packages current settings. If you use the files settings, youll skip the next screen, otherwise, the wizard will proceed as if the file had not existed. Also, if you choose to use an environment variable to store the path and file names, the wizard will not create a configuration file and will again skip the next screen. Even if you use an environment variable, you might want to create the file first and then select the environment variable option afterwards.

The next screen in the wizard is Select Properties to Export. As the name implies, this is where you select the properties for which you want package configurations. In this case, I selected the Value property for the ConnectMngr variable and the ServerName property for each of the two connections managers, as shown in Figure 6.

Selecting properties in the Package Configuration wizard

Figure 6: Selecting properties in the Package Configuration wizard

Because I chose three properties, three package configurations will be created in the XML file. You can choose as many properties as you want to add to your file.

On the next screen of the Package Configuration wizard, you provide a name for the configuration and review the settings (shown in Figure 7).

Completing the Wizard screen in the Package Configuration wizard

Figure 7: The Completing the Wizard screen in the Package Configuration wizard

If youre satisfied with the settings, click Finish. The wizard will automatically generate the XML configuration file and add the properties that youve specified. The file will also be listed in the Package Configuration Organizer, as shown in Figure 8.

The XML package configuration as its listed in the Package Configuration Organizer

Figure 8: The XML package configuration as its listed in the Package Configuration Organizer

NOTE: When you add an XML configuration file, no values are displayed in the Target Object and Target Property columns of the Package Configuration Organizer. This is because XML configuration files support multiple package configurations.

You should also verify whether the XML package configuration file has been created in the specified location. For this example, I added the file to the C:\Projects\SsisConfigFiles\ folder. The file is automatically saved with the dtsConfig extension. If you open the file in a text editor or browser, you should see the XML necessary for a configuration file. Figure 9 shows the LoadPersonData.dtsConfig file as it appears in Internet Explorer.

The XML in the LoadPersonData.dtsConfig file

Figure 9: The XML in the LoadPersonData.dtsConfig file

As Figure 9 shows, the XML configuration file includes the <DTSConfigurationHeading> element. The element contains the attributes and their values that define when, who, and how the file was generated. The file also includes one <Configuration> element for each package configuration. Each <Configuration> element includes the attributes and their values necessary to determine which property is being referenced. Within each <Configuration> element is a nested <ConfiguredValue> element, which provides the propertys actual value.

Notice that the property values are the same as that of the package itself. When you first set up an XML configuration file, the current package value is used for each property. You can, of course, change those values, as I demonstrate later in the article.

September 3rd, 2015 9:59pm

Hello VIMD, I do not see anything under SSIS menu (package configuration) ?

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 12:02pm

Hi MBH,

To create Package Configurations in SSIS 2012, we should right-click to an empty space in the Control Flow pane to select Package Configurations or click the next to the Configurations for the Package Properties.

Because Configurations are available for the package deployment model, we cannot access it for project deployment model from the SSIS menu in SQL Server Data Tools. Besides, Parameters are used in place of configurations for the project deployment model.

References:
Create Package Configurations
Integration Services (SSIS) Parameters

Thanks,
Katherine Xiong

If you have any feedback on our support, please click here.

September 8th, 2015 9:56pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics