Integration Services Learning (4): 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.

Package deployment

After learning from the previous chapters, we have developed more than half of the ETL package, but it is not perfect. Just like a football match, we have worked very hard and passed on very well, however, the technology is too close to the door, and the ball cannot be scored. Everything is useless. Today, we will teach you some tips: integration services package deployment.
Deployment is very important for the SSIS package, and problems often occur during the actual deployment process. For example, if the development of the package is completed in the development environment, debug everything, however, once the environment is generated, the system breaks down and cannot run properly. If there is a problem, the system runs OK. Or, when the user logs on to the server, the system runs OK by logging on to dtexecui.exe1_dtexec.exe, however, once deployed to the SQL Server Agent or Windows scheduled tasks, the task fails to run, and some problems are still confusing. These are not well handled in the integration services deployment process. I will explain in detail how to deal with these details and what issues should be paid attention to during the deployment process.
SQL Server integration services provides very simple deployment tools that can be used to conveniently package files (*. dtsx), package configuration file (*. dtsconfig) and other related files of the package are packaged into an installation file to facilitate installation and deployment on other computers.
By the way, in the solution Resource Manager of the SQL server business intelligence development studio development environment, there is a [Miscellaneous] folder by default. If there are other related files in the package, for example, if you want to package a self-report file or operation instructions together, you only need to add these files to this folder. When you generate a package file, the system automatically saves the files in this folder, sort it into a package folder.

The iislog solution we introduced earlier is used as an example to describe the complete process of package deployment in detail.

(-) Generate the installation and deployment file.

In the resource manager of SQL Server business intelligence development studio, right-click iislog --> [attribute] to open the [iislog attribute page]. In the [Configuration Attribute] tree on the left, select [deploy utility]. The attribute to be configured for [deploy utility] is displayed on the right,

 

Allowconfigurationchanges: sets whether the attributes in the package configuration file can be modified during installation. True/changeable; false/unchangeable. The installation interface will be displayed later.

Createdeploymentutility: determines whether to create an installation and deployment file.

Deploymentoutputpath: Set the location where the installation and deployment files are stored.

Follow the settings and click [OK] to return to the main interface.

On the [generate] menu, click [generate iislog]. This is as simple as generating the installation and deployment file. Go back to the configured deployment file path and you will see all generated deployment files.

 

Here, iislog. ssisdeploymentmanifest is our Installation File.

(2) package deployment.

Copy all files in the deployment folder to the target server and double-click iislog. ssisdeploymentmanifest. The package installation wizard is displayed,

 

The SSIS package can be deployed to a file system or SQL Server. These two methods have their own characteristics. Deploying to a file system is easier and easier to modify configurations. Deploying SQL Server is more secure. There is no difference between package execution and scheduling.

In this example, [deploy to the file system], [next],

　

Select the configuration file. The following list box displays all properties of the configuration file. If allowconfigurationchanges = true is set on the [iislog attribute page] interface, these attributes can be modified here. Otherwise, they cannot be modified here, go to the installation folder and modify the configuration file.

[Next], and then click [OK] to complete the deployment.

Go to the installation folder, find the SSIS package file, and double-click it to display the running interface.

 

By default, click [run] to run the task. (There are already a lot of online information about each configuration item in the left-side navigation bar. For example, Google and Baidu have all the applications available, so I won't bother here .)

In this way, the “dtexecui.exe tool is used by SQL Server integration services ". In addition, another tool named mongodtexec.exe is used. The two tools have the same functions and have no difference. They only have a UI and clearly tell you how to perform each step. The latter does not have a UI. All configuration information must be passed in through command parameters. In this way, it is applicable to scenarios where SSIS packages are automatically executed by means of scheduled tasks on Windows, Windows service, and SQL Server Agent.

Next we will focus on the detailed process of running the SQL Server Agent package, which is also a scheduling method recommended by Microsoft. Although SQL Server Agent job is already very familiar to everyone, however, the SSIS Package task configuration may not be so easy for new users.

First, open SQL Server and create a job,

 

Enter a name. You can use other default names.

Select [STEP] and click [new]. (Note that this step is critical. The difference with other job tasks is also in this step)

 

[Step name]: Enter it at will.

[Type]: Select "SQL server integration services package ".

[Running identity]: by default, this drop-down list contains only one "SQL Server proxy service account". Select this option.

Under the [general] tab,

[Package source]: There are three options: SQL Server, file system, and SSIS package storage area, which are related to the selection during installation and deployment. Select [file system].

[Package]: select the SSIS package file.

[OK]. Return to the new job page.

The configuration plan is very simple, just like other job tasks.

So far, the configuration of an SQL Server Agent job has been completed, and we can finally relax. But I didn't think of it: the darkness before dawn, and the bad news began.

After the specified time is reached, return to the SQL Server Agent to view the job running history, but it is "full of red,

 

Why? Can dtexecui.exe run successfully, but cannot run through SQL Server Agent?

The reason is very simple. dtexecui.exe runs as the user who logs on to the current server, while in SQL Server Agent, it runs as the "SQL Server proxy service account", while in the SSIS package, the database connection password is based on the login user of the current machine. When you change to a user, the password in the database connection string naturally does not take effect. Therefore, the error message is displayed, indicating that the SA connection fails.

There are three solutions to this problem:

One is to [enable the package configuration] and write the database connection information to the configuration file. No matter which user runs the package, the configuration file will be re-loaded, and the problem will naturally be solved.

The second is to reconfigure the Logon account of the SQL Agent service. By default, the Logon account of the SQL Server Agent service is network service, we only need to change it to the user who logs on to the current machine.

The third solution is to add a proxy account. The security creden。 of this proxy account also point to the user logging on to the current machine. There are not many people using this method. The following describes in detail how to create a proxy account.

First, open SQL Server Management studio and create user credenstudio under the [Security] node,

 

[Credential name]: Admin acces (enter at will ).

[ID]: the user ID. You can select the current user to log on to the machine.

Enter the [Password] and [Confirm Password], and click [OK] to complete the establishment of security creden.

Then, add a proxy account.

In SQL Server Management studio, find [SQL Server proxy] --> [proxy] node, right-click --> [Create proxy], and open the [Create Proxy account] dialog box,

 

On the general page:

[Proxy name]: Admin Access proxy (can be entered at will ).

[Credential name]: Admin Access. Is the security creden。 created earlier.

[Effective for the following subsystems]: Check "SQL server integration services". If this proxy account needs to run other subsystems, check the corresponding items in the list.

Switch to the [subject] Page and add a server role,

 

Click [add] to add a user role. If you only need to run the SSIS package, select only two roles.

Click [OK], and the SQL Server Agent Proxy account is created. Then, we return to the job deployment interface. In the [Running identity] drop-down list box, there is one more item,

 

Select the newly created Proxy account [admin access proxy] and click [OK]. Then you can check the running history of the package. It's spring and green again.

Yufeng technology tutorial network http://www.fengfly.com
Http://www.fengfly.com/plus/view-168586-1.html