Step-by-step deployment of SSIS packages illustrated tutorials

This article provides a detailed deployment diagram tutorial for SQL statistical Analysis of the SSIS package deployment, and SQL Server Integration Services offers a very simple deployment tool that makes it easy to package files (*.DTSX), Package profiles (*. Dtsconfig) and other related files of the package, packaged as an installation file to facilitate installation and deployment to other computers.

The following is an example of the DEPJ1200 described earlier in this article detailing the complete process of package deployment.

(-) To build the installation deployment file.

In the SQL Server business Intelligence Development Studio Explorer, right-click DEPJ1200-->[Properties], open [property pages], on the left [ Configuration Properties tree, select Deployment Utility, and on the right you will see the properties that the deployment utility needs to configure.

AllowConfigurationChanges: Sets whether the properties in the package configuration file are allowed to be modified at the time of installation. true/can be modified; false/cannot be modified. The effect of its installation interface will be seen later.

Createdeploymentutility: Decide whether to create the installation deployment file.

DeploymentOutputPath: Sets the location where the installation deployment files are stored.

Follow the settings and click [OK] to return to the main interface. On the Build menu, click Build, and as simple as this, the installation deployment file is generated.

We go back to the set deployment file path and we see all the generated deployment files.

Among them, DEPJ1200. Ssisdeploymentmanifest, this is our installation file.

(ii) Package deployment.

Copy all the files under the deployment folder to the target server, double-click Depj1200.ssisdeploymentmanifest, and the Package Installation Wizard will pop up.

SSIS packages are typically deployed in two ways: deploy to file systems and deploy to SQL Server. Each of these two ways has its own characteristics. Deployment to the file system is simpler and easier to configure. and deploy to

SQL Server, the security will be higher. There is no difference in the execution and scheduling of packages. In this example, we select [Deploy to File System],[next],

Select the configuration file, and the following list box will display all the properties of the profile. If you set allowconfigurationchanges=true in the [Property pages] Interface, these properties can be modified here, otherwise you cannot modify this, but only after the installation is complete, and then to the installation folder, the configuration file is modified.

[Next], and then click [OK], the deployment is complete. Go to the installation folder, locate the SSIS package file, double-click on the run interface that can appear.

By default, click [Execute] to run. (The left navigation bar each item's configuration, the online introduction already many, Google, Baidu, should exhausted has, I here also will not wordy. ）

This approach, which is a utility "DTExecUI.exe" provided by SQL Server integration Services. In addition, there is another utility "DTExec.exe", the two tools, functionally identical, no difference, but the former has a UI interface, specifically told you, how to operate each step. The latter does not have a UI interface, and all configuration information needs to be passed in by command parameters, which is a better scenario for automating the SSIS package execution through Windows Scheduled Tasks, Windows Service, SQL Server Agent, and so on.

Here we highlight the detailed process of SQL Server Agent execution package, which is also recommended by Microsoft, although the SQL Server Agent job is very familiar, but the configuration of the SSIS Package task, for the novice, Maybe it's not that easy.

First, open SQL Server, create a new job,

Enter a name, other default. Select "Step", click "New",:(note that this step is critical, and the differences with other job tasks are also in this step)

[Step name]: random input.

[Type]: select SQL Server Integration Services package.

[Run as]: By default, there is only one SQL Server Agent service account in the drop-down list box, select this.

Under the General tab,

[Package source]: There are three options: SQL Server, File system, SSIS package Store, which is related to the choice of the previous installation deployment. We select [File system].

[Package]: Select the SSIS package file.

[OK] to return to the new job interface.

Configuration plan, this is simple, as with other job tasks, no longer be described.

So far, a SQL Server Agent Job has been configured, and we can finally get a sigh of relief. But did not think: Before the dawn is the darkest, the sad news also began.

After reaching the specified time, go back to the SQL Server Agent to view the job run history, but it is "full of red" ah,

What is this for? Can run successfully through DTExecui.exe, and cannot be run by SQL Server Agent?
In fact, the reason is very simple, is the previous configuration of the [run identity] problem, DTExecui.exe is logged on to the current server's identity to run, and in the SQL Server Agent, but in the SQL Server Agent service account, but in the SSIS package, The password of the database connection is based on the login user of the current machine, and when a user is changed, the password in the database connection string will not take effect naturally. As a result of the error, the Sa connection failed.
In the face of this situation, there are generally three solutions:
One is [enable package configuration], the database connection information to the configuration file, the package regardless of what user run, will reload the configuration file, the problem naturally solved.
The second is to reconfigure the login account for the SQL Agent service, by default, the login account for the SQL Server Agent service is Network Service, and we only need to modify it to the user who is logged on to the current machine.
The third solution is to add the proxy account, which also points to the user who is logged on to the current machine. This method does not use a lot of people, the following we describe in detail, how to set up a proxy account.
First, open SQL Server Management Studio, under the Security node, create a new user credential,

[Credential name]:admin Acces (discretionary input).
[Identity]: That is, the user ID, choose to log on to the machine's current user is OK.
Enter [password] and [Confirm password] and click [OK] to complete the establishment of the security credentials.
Then, add the proxy account.
In SQL Server Management Studio, locate the SQL Server Agent]-->[Agent node, right-click-->[New Agent], and open the New Proxy Account dialog box.

On the General page:
[Proxy name]:admin Access Proxy (you can enter it freely).
[Credential name]:admin Access. Is the security credentials created earlier.
[Valid for the following subsystems]: tick "SQL Server integration Services", if this proxy account also needs to run other subsystems, tick the corresponding item in the list.
Switch to the main page, add server roles,

Click [Add] to add a user role, if you only need to run the SSIS package, just select both roles.
Click [OK] to establish the],sql Server Agent Agent account is complete, then we go back to the job deployment interface, in the [Run as] drop-down list box, there is an extra item,

We select the proxy account you just created [Admin Access proxy] and click [OK]. Then go to see the package's running history, the spring, green again.

Step-by-step deployment of SSIS packages illustrated tutorials