Using commands to invoke SSIS packages in SQL Server

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. ]