Deploying SSIS Packages step-by-Step tutorial 1

Http://www.it118.org/specials/c9fba99e-4401-49cf-8256-ac3c1a34c0d9/9f7daa7d-58ce-40a0-8bc2-9960c05f18a5.htm

In this article, a detailed deployment illustrated tutorial on the deployment of an SQL statistical analysis SSIS package, SQL Server Integration Services provides a very simple deployment tool that makes it easy to make package files (*.DTSX), Package profiles (*. Dtsconfig) and other related files of the package, packaged into an installation file to facilitate installation and deployment to other computers.

Here's an example of the IISLog Solution described earlier to detail the complete process of package deployment.

　(-) Build the installation deployment file.

In the SQL Server Business Intelligence Development Studio Explorer, right-click IISLog-->[Properties], open the IISLog property page, and on the left Configuration Properties tree, select Deployment Utility, and the right side shows the properties that the deployment utility requires to be configured, as shown in the figure:

　　allowconfigurationchanges: Sets whether the properties in the package profile allow modification at installation time. true/can be modified; false/cannot be modified. The effect of its installation interface will be seen later.

　　createdeploymentutility: Determines whether to create an installation deployment file.

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

Set the properties as shown, click [OK]and return to the main interface.

On the build menu, click Build IISLog, and as simple as this, the installation deployment file is generated. We go back to the deployment file path set in the previous illustration and we see all the generated deployment files.

Among them, Iislog.ssisdeploymentmanifest, is our installation file.

　　(ii) package deployment.

With all the files under the deployment folder above, copy to the target server, double-click Iislog.ssisdeploymentmanifest to eject the Package Installation Wizard, as shown in the figure:

SSIS packages are typically deployed in two ways: deployed to file systems and deployed to SQL Server. Both of these methods have their own characteristics. Deployment to a file system is simpler, and configuration modification is easier. Deploying to SQL Server is more secure. There is no difference in the execution and scheduling of packages.

In this case, we choose [deploy to File system] ,[next] , as shown in figure:

　

Select a profile, and the following list box displays all the properties of the configuration file. If you set allowconfigurationchanges=true in the [IISLog property Page] interface, these properties can be modified here, otherwise you cannot modify it here, and only after the installation is complete, The configuration file is modified under the installation folder.

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

Go to the installation folder, find the SSIS package file, double-click, and you can see the running interface as shown in the illustration.

By default, click [Execute] to run it. (on the left navigation bar each item configuration, the on-line introduction already a lot of, Google, Baidu, should exhausted have, I here also not wordy.) ）

This approach, which is a utility "DTExecUI.exe" provided by SQL Server integration Services. In addition, the use of another utility "DTExec.exe", the two tools, in the functional aspects of the same, there is no difference, but the former has a UI interface, clearly told you, each step how to operate. The latter does not have a UI interface and all configuration information needs to be passed through the command parameters, which is more appropriate for automating the scenario of SSIS packages through Windows scheduling tasks, Windows Service, SQL Server Agent, and so on.

Here we focus on the detailed process of the SQL Server Agent execution package, which is one of the scheduling methods recommended by Microsoft, although the SQL Server Agent job is already well known, but the configuration of the SSIS package Tasks, for beginners, It may not be that easy.

First, open SQL Server and create a new job, as shown in figure:

Enter a name, and other defaults are OK.

Select [Step], click [New], as shown: (Note that this step is critical and the difference from other job tasks is also in this step)

　　[step name]: Enter randomly.

　　[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 item.

Under the General tab,

　　[package source]: There are three options in it: SQL Server, File system, SSIS package Store, which is related to the selection of the previous installation deployment. We chose [file system].

　　[Package]: Select the SSIS package file.

　　[OK], return to the new job interface.

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

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

When you get to the specified time, go back to the SQL Server Agent and see the job run history, but it's "full red", as shown:

This is why. The DTExecui.exe can run successfully, and the SQL Server Agent cannot run.

In fact, the reason is very simple, is the previously configured [run identity] problem, DTExecui.exe is logged on as the user of the current server to run the identity of the SQL Server Agent, but in the SQL Server Agent service account, the identity of the run, In the SSIS package, the database connection password is based on the current machine's login user, and when a user is changed, the password in the database connection string is not effective. So the error prompts that the Sa connection failed.

In the face of this situation, there are generally three kinds of solutions:

One is [enable package configuration], write the database connection information to the configuration file, regardless of the user run, the package will reload the configuration file, the problem is naturally resolved.

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 the Network Service, and we simply change 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, below we explain in detail, how to establish a proxy account.

First, open SQL Server Management Studio, under the Security node, create a new user credential, as shown in:

　　[credential name]: Admin Acces (random input).

　　[Identity]: That is, the user ID, select the current user to log on to the machine can be.

Enter [password] and [Confirm password], and click OKto complete the establishment of the security credentials.

Then, add the proxy account.

In SQL Server Management Studio, locate the SQL Server Agent-->[proxy ] node, right-click-->[New agent], and open [new proxy account] dialog box, as shown in figure:

On the regular page:

　　[proxy name]: Admin Access Proxy (optionally entered).

　　[credential name]: Admin Access. Is the security credentials that you created earlier.

　　[Valid for the following subsystems]: check Sql Server integration Services, and if the proxy account needs to run other subsystems, tick the corresponding item in the list.

Switch to the main page and add the server role, as shown in the figure:

Click Addto add the user role, and if only the SSIS package is required to run, select only those roles as shown.

Click OKand the SQL Server Agent proxy account is set up, and then we go back to the job deployment interface and in the run as drop-down list box, we have one more item, as shown in the figure:

We select the proxy account we just created [Admin Access Proxy] and click [OK]. Then to see the running history of the package, on the spring, once again green.