Integration Services Learning (6): Package Configuration

The integrartion services package is actually a collection of Object Attributes. All the integration services packages we developed earlier, including the variables and attributes, such as database links and synchronization file directories, we use a constant directly in the package to assign these variables or attributes, which makes publishing and moving this package a lot of trouble, for example, when we develop a package in the development environment, all variables and attributes are set according to the development environment. After the development is complete, we need to deploy the package to the production environment, DB connections and synchronization directories all change. Previous practice: Open the package in the new environment and reset the values of these variables or attributes to run normally. Since integration services 2005, a [Package configuration] feature has been added, which solves this problem. The configuration file can help to seamlessly transfer packages and automate the configuration process, which reduces the trouble of deployment and reduces the risk of errors.
The package configuration of the Integration Services program allows you to write any SSIS attributes such as packages, connections, containers, and variables to an XML file or table, and then read them at runtime. The configuration methods are as follows:
1. xml configuration file.
2. environment variables.
3. Registry.
4. Parent package variables.
5. SQL Server.
Xml configuration files, parent package variables, and SQL Server are common, but the other two types are rarely used. The following describes the common three package configurations in detail.

　　(1) xml configuration file.

Select "SSIS menu"> "package configuration", and open the following window. The package configuration starts from here.

 

Select "enable package configuration" and click "add ",

 

The [configuration type] drop-down list box shows that there are multiple types of package configurations. Here we select "xml configuration file" and enter the configuration file name iislog. dtsconfig.

By default, the configuration file is of the ". dtsconfig" type, and its file format is standard XML file format. Integration Services does not strictly define the extension name of the configuration file. If you modify the extension to .txt or. XML, it is usually changed to a ". xml" file, so that the general XML file editor can be easily edited. (The extension is not modified in the example)

Click [next] to select the attributes to be configured,

 

In the [object] box on the left, if you fold all the Tree nodes, you can see the four types of objects in the package (variables, Connection Manager, properties, executable files) properties can be managed through the configuration file. The [executable file] indicates all components in the control flow. Next we will show all nodes and select the attribute to be configured. When an attribute is selected, the default value of this attribute will be displayed in the [attribute properties] box on the right, this can also be modified.

[Next],

 

Enter the [configuration name] and click Finish. The xml configuration is completed easily.

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

Xml configuration code

<? XML version = "1.0"?>
<Dtsconfiguration>
<Dtsconfigurationheading>
<Dtsconfigurationfileinfo generatedby = "buynow \ goumh" generatedfrompackagename = "importiislog" generatedfrompackageid = "{F874E3F1-9FD5-4B89-8E29-A86A8E6A6783}" generateddate = "2010/1/13 17:36:34"/>
</Dtsconfigurationheading>
<Configuration configuredtype = "property" Path = "\ package. Connections [apiislog]. properties [connectionstring]" valuetype = "string">
<Configuredvalue> Data Source = goumh-hGH; initial catalog = apiislog; provider = sqlncli10.1; Integrated Security = sspi; Auto translate = false; application name = SSIS-importiislog-{28895ffb-4da0-4b30-9e59-649f1d200dfa} RD5-HGH.IisLog; </configuredvalue>
</Configuration>
<Configuration configuredtype = "property" Path = "\ package. Connections [backupfile]. properties [connectionstring]" valuetype = "string">
<Configuredvalue> E: \ Inetpub \ wwwroot \ SQL 2008 study \ SSIS \ iislog \ backupfile </configuredvalue>
</Configuration>
<Configuration configuredtype = "property" Path = "\ package \ foreach loop container. foreachenumerator. properties [Directory]" valuetype = "string">
<Configuredvalue> E: \ Inetpub \ wwwroot \ SQL 2008 study \ SSIS \ iislog \ datafile </configuredvalue>
</Configuration>
<Configuration configuredtype = "property" Path = "\ package \ foreach loop container. foreachenumerator. properties [filespec]" valuetype = "string">
<Configuredvalue> *. Log </configuredvalue>
</Configuration>
</Dtsconfiguration>

This configuration file contains the package ID, attribute path, data type, and attribute value. If we want to modify the configuration of a package, we don't need to open the package any more. We can modify the configuration file directly by modifying the attributes and attributes, it makes package release and deployment quite convenient.

　　(2) SQL Server

This is also a common configuration method. The configuration method is similar to the preceding xml configuration, but the final configuration attribute is stored in the SQL Server 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:

　　(3) parent package Variables

In integration services, there is such a task [executing a package task]. With this component, we can nest one package in another package, which improves the packet granularity, it is very helpful to make the service processing of packages simpler, such as the following modes:

 

 

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.

 

Guidance:

 

SSIS Learning (1) Overview

SSIS Learning (2): Data Flow task (I)

Integration Services Learning (3): Data Flow task (2)

Integration Services Learning (4): variables and expressions

Integration Services Learning (5): Container