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 ' \msdb\test "/server" servername "/maxconcurrent"-1 "/checkpointing off/reporting V"
Note the point:
1. The system procedure used in the above operation xp_cmdshell by default is disabled, want to use, must first open, open method see: http://www.jb51.net/article/41667.htm
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 a 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.