How to dynamically update variable values when running SSIS packages

Implementation Method:

To dynamically update a variable, you can create a configuration for the variable, deploy the configuration to the package, and then update the variable value in the configuration file when deploying the package. In this way, the updated variable value can be used in the package during running.

The following is a simple example to illustrate how to dynamically update the variable value.

Example requirements:

Import data to the local instance "TRIY-2FE792BB5D \ sql2008de" RERI-6EC5991410 \ sql2008de "\ sql2008de"

Example:

Create a simple "Data Flow task". The specific operation steps are skipped here. In the scope of "package", create a user-defined variable "name ". the data type is "string" and the value is "nr.00002"

 

Reference variable in "ole db source:

 

After the SSIS package has been changed, we will deploy the variable configuration to the package. In the menu "SSIS", find "package configuration ":

Here, a configuration package with the configuration type "SQL Server" is set.

 

[DBO]. [SSIS configurations] is the table name, created in the database where order of the local instance "RERI-6EC5991410 \ sql2008de" is located.

Create Table [DBO]. [SSIS configurations]
(
Configurationfilter nvarchar (255) not null,
Configuredvalue nvarchar (255) null,
Packagepath nvarchar (255) not null,
Configuredvaluetype nvarchar (20) not null
)

In [DBO]. [SSIS configurations], the field configurationfilter stores the value of the configuration filter "ordername"

The Value Attribute of configuredvalue corresponding to the SSIS package variable "name" in [DBO]. [SSIS configurations] is set below.

 

After the configuration is complete, we can find the corresponding configuration data in the database where the "order" of "RERI-6EC5991410 \ sql2008de" of the local instance is located:

 

 

After the SSIS package is deployed, modify [DBO] as needed. the Configuration value in [SSIS configurations] is "nr.00002". When the SSIS package is executed, the value of the variable "name" in the package is dynamically updated. For example, we change "nr.00002" to "nr.00010"

Update DBO. [SSIS configurations]
Set configuredvalue = 'nr. 00010'
Where configurationfilter = 'ordername'

 

After the package is executed, we can see the package execution progress, the package calls the configuration string configured above "RERI-6EC5991410 \ sql2008de. ssis_db.sa"

 

In this way, the requirements at the beginning of the article are met.

In addition, the configuration type can be set to another type during package configuration. xml configuration files are often used, and the style is as follows:

<? XML version = "1.0"?>
-<Dtsconfiguration>
-<Dtsconfigurationheading>
<Dtsconfigurationfileinfo generatedby = "mono \ Andy" generatedfrompackagename = "package" generatedfrompackageid = "{region}" generateddate = "1:00:24"/>
</Dtsconfigurationheading>
-<Configuration configuredtype = "property" Path = "\ package. Variables [user: Name]. properties [value]" valuetype = "string">
<Configuredvalue> nr.00002 </configuredvalue>
</Configuration>
</Dtsconfiguration>