Integration Services Learning (7): 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 package is developed in the development environment, debug
Everything is okay, but it breaks down and cannot run as soon as it reaches the production environment, either there is a problem or there is a problem; or when the user logs on to the server
Dtexecui.exe1_dtexec.exe is used to run OK. However, once deployed to the SQL Server Agent or Windows
In a scheduled task, the task fails to run, and some problems are confusing. These are all integration services.
The deployment process is not well handled. Next I will explain how to deal with these details and what problems 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], Open[Iislog attribute Page], On the left[Configure attributes]Tree, select[Deploy a utility], The right side will display[Deploy a utility]Attributes to be configured,

 

　　Allowconfigurationchanges: Set 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], Return to the main interface.

In[Generate]Click[Generate iislog]The installation and deployment file is generated. 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, we select[Deploy to file system],[Next],

　

Select the configuration file. The following list box displays all properties of the configuration file. If[Iislog attribute Page]Interface, SetAllowconfigurationchanges = trueThese attributes can be modified here. Otherwise, they cannot be modified here. The configuration file is modified only after the installation is complete and then under the installation folder.

[Next]And then click[OK]The deployment is complete.

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

 

By default, click[Execution]You can run it. (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], Click[New](Note that this step is critical, and the difference from other job tasks lies in this step)

 

　　[Step name]: Enter at will.

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

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

In[General]Tab,

　　[Package source]: There are three options: SQL Server, file system, and SSIS package storage area, which have something to do with the previous selection during installation and deployment. We choose[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?

In fact, the reason is very simple, that is, the previous configuration[Running identity]Dtexecui.exe runs as a user logging on to the current server, but runs as an "SQL Server proxy service account" in the SQL Server Agent, 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[Enable package configuration]To 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 isNETWORK SERVICEYou only need to change it to the user logging 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[Security]Under the node, create user creden,

 

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

　　[ID]: The user ID. Select the current user to log on to the machine.

Input[Password]And[Confirm Password], Click[OK]To establish security creden.

Then, add a proxy account.

In SQL Server Management studio, find[SQL Server proxy]-->[Proxy]Node, right-click -->[Create a proxy], Open[Create a 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.

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

Switch[Subject]Page, 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]The SQL Server Agent Proxy account is created, and then we return to the job deployment interface[Running identity]In the drop-down list box, one more item is displayed,

 

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