Shulou(Shulou.com)06/01 Report--

In the business intelligence solution, SSIS project has two deployment modes: engineering deployment (project deployment) and package deployment (package deployment). The default is engineering deployment mode. In the management of Package, engineering deployment mode is the current mainstream deployment mode, which is easier to manage than package deployment, and it is also easier to debug and troubleshoot package, while package deployment mode is the traditional deployment mode in package deployment mode. In the package deployment model, the SSIS engine supports the package configuration (Package Configuration) feature, which allows users to dynamically modify property values while Package is running. A package configuration is a collection of configuration items, and each configuration item (configuration item) is a property / value pair (property/value pair). Users add configuration items to the package configuration, and Package acquires attribute values from the package configuration at run time to programmatically control the execution of Package, especially in the scenario of batch management of Package, for example, batch modification of the value of a common variable of Package, switching between Package testing and product environment, etc., package configuration is very useful.

In general, the package configuration information is stored in the SQL Server database, and there are four types of Package objects that users can configure. They are:

Link Manager (connection managers) for Connection Managers:Package

Executable components of Executables:package

Properties of Propertites:package

Variables of Variables:package

The above four Package objects can be parameterized, which is the basis on which SSIS can store its property values in the package configuration and apply the configuration values of the properties at execution time.

When executing Package, SSIS Package first reads the package configuration information, applies the property values in the package configuration to the Package, and then executes the executable component (executables) of the package; modifying the value of the attributes in the package configuration will dynamically change the execution context of the Package, for example, switching between the test environment and the production environment, modifying the value of the connection string of the link manager (Connection Manager), so that the package synchronizes the run results to the test environment After the package runs correctly in the test environment, and then deploy it to the Production environment, if you use the package configuration to manage the link string of the link manager, you only need to modify the value of the connection string of Connection Manager in the package configuration, but do not need to make any changes to Package.

First, switch to package deployment mode

The default deployment mode of SSIS Project is Project Deployment Model. Select the project in the project list of the solution, right-click the pop-up shortcut menu, and click "Convert to Package Deployment Model" to convert the deployment mode of Project to package deployment mode.

After switching to package deployment mode, the Project name is followed by the pattern name: (package deployment model)

Second, design Package

Add a variable (VarCode) and an Execute SQL Task component to the Package, and execute the following TSQL statement script in the Task component, "?" Represents a parameter, maps to a variable (VarCode), and inserts the value of the variable into the specified table

Insert into dbo.dt_testvalues (1)

1. Configure the TSQL statement executed by the Task component

2. Configure parameter mapping for Task components

Third, enable package configuration

1, open the package configuration wizard

Click the SSIS menu, choose "package configurations", or right-click in the space of the "Control Flow" panel of Package, pop up the shortcut menu, and select "package configurations" to open the package configuration wizard.

2, enable package configuration

Check the "Enable package configurations" check box to enable package configuration for the current Package

3, add package configuration

In "Package Configurations Organizer", click the Add button and select SQL Server in the Configuration Type list, which means that Package uses the SQL Server database to store the package configuration information, while Package reads the configuration item information from the SQL Server database.

In order to store package configuration information, you must set up the SQL Server database and configuration table, select "specify configuration settings directly" and specify configuration settings of type SQL Server: link (Connection), configuration table (Configuration table), and configuration filter (Configuration Filter).

4, configuration table (Configuration Table)

When Package executes, if there is no configuration table in the specified SQL Server database, Package automatically creates the configuration table. By default, the script that SSIS uses to create the configuration table is:

CREATE TABLE [dbo]. [SSIS Configurations] (ConfigurationFilter NVARCHAR (255) NOT NULL, ConfiguredValue NVARCHAR (255) NULL, PackagePath NVARCHAR (255) NOT NULL, ConfiguredValueType NVARCHAR (20) NOT NULL)

The meanings of the fields in the configuration table are:

ConfigurationFilter: a filter used to uniquely identify configuration sets. Each configuration set is a collection of attribute / value pairs (property/values pair). Only one ConfigurationFilter can be set per Package, and Packages with the same ConfigurationFilter has the same configuration set and shares the same configuration information.

ConfigurationValue: the value of the configuration property, which is used to update the configuration property value

PackagePath: the path to the configuration property, which contains the configuration property and its path information, for example, "\ package. Variables [user:: VarCode] .Properties [Values]"

ConfiguredValueType: the data type of the configuration property

Set the link to the configuration table and the configuration set filter (Configuration filter):

Connection: used to set the link to access configuration table

Configuration Table: specifies the name of the configuration table (table name)

Configuration filter: when multiple Package share a single Configuration Table, you need to set configuration filter for each Package. If the filter is the same, the package uses the same configuration data. If some Package has special configuration information, you can set a different filter for it.

Fourth, set configuration items (configuration item)

Configure the value of the variable VarCode into the package configuration, which means that the value of the variable VarCode is stored in [dbo]. [SSIS Configurations], and Package reads this value at execution time and uses the read value as the value of the variable VarCode for use by the Task component.

Fifth, view configuration information

When the configuration type is SQL Server, the package configuration information is stored in the SQL Server database, and the default configuration table name is [dbo]. [SSIS Configurations]

Select * from [dbo]. [SSIS Configurations]

6. Execute package and view the results of execution.

1. Execute Package using the default value to view the result of Task component execution

2. Modify the ConfiguredValue in [dbo]. [SSIS Configurations], run package again, and view the results executed by the Task component.

Update [dbo]. [SSIS Configurations] set ConfiguredValue=2where ConfigurationFilter='configuration_parameter_value'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.