SSIS package Configuration

Document directory

• Xml configuration file
• Ii. SQL service configuration

SQL serverintegration Services provides the package configuration that can be used to update attribute values at runtime. Configuration is the property/value pair added to the completed package. Generally, during package development, you create a package setting attribute on the package object and add the configuration to the package. When the package runs, it obtains the new property value from the configuration. For example, by using the configuration, you can change the connection string of the Connection Manager or update the value of the variable.

Package configuration has the following advantages:

• With configuration, you can easily transfer packages from the development environment to the production environment. For example, the configuration can update the path of the source file or change the name of the database or server.
• The configuration is useful when you deploy a package to multiple servers. For example, variables used in the configuration of each deployed package can contain different disk space. If the available disk space does not meet this value, the package will not run.
• Configuration can make the package more flexible. For example, the configuration can update the value of the variable used in the attribute expression.

Integration Services supports several different storage package configurations (such as XML files, tables in the SQL Server database, and environment variables and package variables.

Each configuration is an attribute/value pair. The xml configuration file and the SQL server configuration type can contain multiple configurations.

Xml configuration file

Select SSIS menu-> package configuration. In the following window, configure the package configuration from here.

 

Select enable package configuration check, and click Add ,:

1. The [configuration type] drop-down list box shows that there are multiple types of package configurations. Here we select "xml configuration file"

2. Select to directly specify configuration settings

3. Click Browse to save the configuration file to the specified path.

The default configuration file type is ". dtsconfig". The file format is standard XML. Integration Services does not strictly define the extension of the configuration file. It does not matter if you change the extension name to .txt or. xml. Click the [next] button and select the desired properties. :

 

In the dialog box on the left, you can select the attribute to be configured as needed. When an attribute is selected, the default value of this attribute is displayed in the attribute box on the right, this value can be changed. After modifying the attributes, click the next button ,:

 

Enter the [configuration name] and click Finish. The xml configuration is completed easily. The package configuration organizer dialog box appears, as shown in. Click the close button to complete the configuration of the entire package.

Go to the directory where the configuration file is stored and find the configuration file "sqlconnectionstring. dtsconfig" to see what it looks like:

<? XML version = "1.0"?>

<Dtsconfiguration>

<Dtsconfigurationheading>

<Dtsconfigurationfileinfo generatedby = "******" generatedfrompackagename = "readrecommendation" generatedfrompackageid = "{region}" generateddate = "2011/10/28 10:00:21"/>

</Dtsconfigurationheading>

<Configuration configuredtype = "property" Path = "\ package. Connections [recommendationdb]. properties [connectionstring]" valuetype = "string">

<Configuredvalue> Data Source = ******; user id = *****; initial catalog = recommendationdb; provider = sqlncli10.1; Auto translate = false; application name = SSIS-readrecommendation; </configuredvalue>

</Configuration>

<Configuration configuredtype = "property" Path = "\ package. Connections [recommendationdb]. properties [Password]" valuetype = "string">

<Configuredvalue> ****** </configuredvalue>

</Configuration>

</Dtsconfiguration>

This configuration file contains the connection method of the database. If you need to modify the database connection, you only need to change the corresponding attributes.

Ii. SQL service configuration

This is also one of our common configuration methods. The configuration method is similar to the xml configuration, but the final configuration attribute is stored in the SQL Service database.

 

In the [configuration type] drop-down list box, select [SQL Server]. The following configuration page also changes.

[Connection], that is, SQL Server, that is, database connection. If the corresponding connection cannot be found from the drop-down list box, click [add] to create a new connection, this is the same as creating an ole db connection in the Connection Manager.

[Configuration table (a)], that is, the table where the Configuration Attribute is stored. If the table is not created in advance, click [new]. The system displays the following SQL statement by default, click [OK] to create the table.

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

[Configure filter (f)]: This is critical. It is used to differentiate the key value of the Configuration Attribute of each package. That is to say, if a table, when storing the configuration attributes of multiple packages, each package is filtered based on this key value. Therefore, different packages should have different key values. [Next], select the attribute to be configured, which is consistent with the processing method of the [xml configuration file] described above. Next, let's go back to SQL Server and find the record we just created to see what it looks like:

III, Parent package Variables

The definition of parent package variables is also very simple. Open the package Configuration Wizard, such:

 

[Configuration type]: Select "parent package variable ".

[Parent variable]: enter the name of the variable in the parent package.

[Next], select the property name of the parent package variable ing, and click Finish to obtain the effect shown in.

This is our common package configuration. environment variables and registries are rarely used. We will not talk about them here.

Package configuration makes package deployment very convenient. The best way is to use a configuration file for each server. However, you can see from the package configuration interface that a package can apply multiple configuration files, each package configuration is applied in the order shown in [Package configuration Organization Program] (except for the parent package variable). If one of the configurations fails, only a warning is sent, it will not cause package errors. On the contrary, a configuration file can also be applied to multiple packages. If we use it flexibly, the seamless migration of packages will no longer be a myth.