Step by step SSIS package deployment graphic tutorial

This article provides a detailed deployment graphic tutorial on the deployment of the SQL statistical analysis SSIS package. SQL server integration services provides a very simple deployment tool, these tools can be used to conveniently put the package file (*. 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.
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.