How to dynamically update variable values when you run SSIS packages

Reprint: http://www.cnblogs.com/wghao/archive/2011/04/16/2017679.html

Implementation Mode :

To dynamically update variables, you can create configurations for variables, deploy those configurations to packages, and then update variable values in the configuration file when you deploy the package. This allows the package to use the updated variable value at run time.

The following is a simple example to illustrate how to implement dynamic update variable values.

Example Requirements :

Import an order form from a remote computer instance "Triy-2fe792bb5d/sql2008de" as needed (Order.dbo.Name), importing the data into the native instance "Reri-6ec5991410/sql2008de"

Example Implementation :

Create a simple "data Flow task", the specific steps here to skip, see in the scope "Package", create a user-defined variable "Name". The data type is "String" and the value is "nr.00002"

Referencing variables in OLE DB Source:

When SSIS package mode is OK, we then deploy the variable configuration to the package and find the package configuration in the menu "SSIS":

A package configuration with the configuration type "SQL Server" is set up here.

[dbo]. [SSIS configurations] is the name of the table, created in the database of "order" of the native instance "Reri-6ec5991410/sql2008de".

CREATE TABLE [dbo]. [SSIS configurations]
(
Configurationfilter NVARCHAR (255) Not NULL,
ConfiguredValue NVARCHAR (255) NULL,
PackagePath NVARCHAR (255) Not NULL,
Configuredvaluetype NVARCHAR () not NULL
)

[dbo]. The field configurationfilter in [SSIS configurations] stores the value "Ordername" of the configuration filter

The following is the setting [dbo]. ConfiguredValue the Value property of the SSIS package variable ' Name ' in [SSIS configurations]

When the configuration is complete, we can find the corresponding configuration data in the "order" database of the native instance "Reri-6ec5991410/sql2008de":

After the deployment of the SSIS package completes, modify [dbo] as needed. The configuration value "nr.00002" in [SSIS configurations], the value of the variable "Name" in the package is dynamically updated when the SSIS package is executed, as we now turn "nr.00002" to "nr.00010"

Update dbo. [SSIS configurations]
Set configuredvalue= ' nr.00010 '
Where configurationfilter= ' Ordername '

After executing the package, we can see the package execution progress, the package calls the configuration string "RERI-6EC5991410/SQL2008DE" configured above. Ssis_db.sa "

This enables you to implement the requirements section at the beginning of the article.

Alternatively, the configuration type can be set to a different type at the time of package configuration, often using an XML configuration file with the following style:

<?xml version= "1.0"?>
-<DTSConfiguration>
-<DTSConfigurationHeading>
<dtsconfigurationfileinfo generatedby= "Mono/andy" generatedfrompackagename= "Package" generatedfrompackageid= "{ 36280b53-9cf8-4681-a546-12c05a82957b} "generateddate=" 2011-4-16 1:00:24/>
</DTSConfigurationHeading>
-<configuration configuredtype= "property" Path= "/package.variables[user::name". Properties[value] "valuetype=" String >
<ConfiguredValue>Nr.00002</ConfiguredValue>
</Configuration>
</DTSConfiguration>