Using commands to invoke SSIS packages in SQL Server

Source: Internet
Author: User
Tags ssis

You can use the dtexec command in SQL Server to run SSIS packages (more than 2005 versions), and of course you can run SSIS packages through system procedures: xp_cmdshell invoke dtexec.

The specific steps are as follows:

1. First of all, it is of course to design the package in business intelligence and debug it through.

2. Then, there are two ways to run SSIS packages using commands in SQL Server

The first is to execute the SSIS package file directly, commands such as: exec xp_cmdshell ' dtexec/f ' c:test.dtsx '

The second is to publish or import the package file to the Integration Services service and then execute it.

The Publish package generates the publish file in Business Intelligence and publishes it to the Integration Services service. After you generate the publish file, double-click the Publish file, select [Deploy to SQL Server], then enter the server name, username, password, and select the package path as "/" and select [Rely on server storage for encryption], click [Next], and select the location you want to install.

The import package is in Managemenet Studio, select the Integration Services service, select "Stored Packages"-"MSDB", right-click on the package, select the file system, specify the package to import, and the protection level to select [Rely on server storage and roles for access control ].

The role and purpose of the release package and the import package are consistent in order to put the package into the Integration Services service.

After the package has been published or imported, it can be invoked using the following command:

exec xp_cmdshell ' Dtexec/dts ' msdbtest "/server" servername "/maxconcurrent"-1 "/checkpointing off/reporting V"

Note the point:

1. The system procedure used in the above operation xp_cmdshell is disabled by default and must be opened first if you want to use it.

2. When you publish a package or import a package, you must choose the correct level of protection, select [rely on server storage for encryption] When you publish the package, select [Rely on server storage and role for access control] When you import the package, or you will receive an error: [Description: Cannot decrypt protected XML node] Dts:password ", error is 0x8009000b" This item is not suitable for use in the specified state. ". You may not have permission to access this information. This error occurs when there is a cryptographic error. Make sure that you provide the correct key. ]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.