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#
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
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.
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.
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.
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.
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).
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.
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.
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.
Hello VIMD, I do not see anything under SSIS menu (package configuration) ?
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.