Use commands in SQL Server to call SSIS packages

The procedure is as follows:

1. First, of course, we need to design the package in Business Intelligence and debug it.

2. There are two ways to run the SSIS package using commands in SQL Server.

The first is to directly execute the ssis package file. The command is exec xp_mongoshell 'dtexec/f "c: \ test. dtsx "'

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

A release package is used to generate a release file in Business Intelligence and publish it to the Integration Services Service. After generating the release file, double-click the release file, select [deploy to SQL Server], enter the Server name, user name, and password, and select the package path as "/". select [encrypt by using server storage], click [next], and select the location to install.

The import package is in Managemenet Studio. Select Integration Services, select "stored package"-"MSDB", right-click the import package, select the file system, and specify the package to be imported, for protection level, You must select [access control by server storage and role].

Both publish and import packages serve the same purpose and purpose, so as to put the packages into the Integration Services service.

After the package is published or imported, you can use the following command to call it:

Exec xp_cmdshell 'dtexec/DTS "\ MSDB \ test"/SERVER "servername"/MAXCONCURRENT "-1"/checkpointing off/REPORTING V'

Note:

1. The system process xp_cmdshell used in the above operation is disabled by default, to use, must be enabled first, open the method see: http://www.jb51.net/article/41667.htm

2. when releasing or importing a package, you must select the correct protection level. When releasing a package, select [encrypt by using server storage]. when importing a package, select [access control by server storage and role]. Otherwise, an error occurs: [Note: the protected XML node "DTS: Password" cannot be decrypted ", error: 0x8009000B "This item is not suitable for use in the specified state. ". You may not be authorized to access this information. This error occurs when an encryption error occurs. Ensure that the correct key is provided.