How to Correctly Execute SSIS Packages within SQL Server Agent Jobs?

Executing SQL Server Integration Services Packages in SQL Server Agent Jobs

The Problem

Automating SSIS package execution through SQL Server Agent jobs is a common requirement, but syntax errors can возникать when specifying package paths within the job step commands.

The Correct Syntax

To execute an SSIS package in a SQL Server Agent job step, use the following syntax:

/FILE "<package_path>"</package_path>

Troubleshooting Syntax Errors

In the provided script, the syntax error occurs due to the use of '/FILE' without quotation marks. The correct syntax is:

EXEC sp_add_jobstep @job_id = @jobid
                    ,@step_name = N'Upload Data'
                    ,@step_id = 1
                    ,@command=N'/FILE "D:\Installs\Upload.dtsx"'
EXEC sp_add_jobstep @job_id = @jobid
                    ,@step_name = N'Download Data'
                    ,@step_id = 2
                    ,@command=N'/FILE "D:\Installs\Download.dtsx"'

Using the Graphical User Interface

Alternatively, you can use the SQL Server Management Studio graphical user interface to create a job and add SSIS package execution steps:

1. Open SQL Server Management Studio and expand SQL Server Agent.
2. Right-click on Jobs and select New Job....
3. On the New Job dialog, specify the job name, owner, and description.
4. In the Steps section, click New....
5. On the New Job Step dialog, select SQL Server Integration Services Package as the Type.
6. In the Run as field, select an appropriate credential or proxy to execute the SSIS package.
7. Click Browse to select the package path.

Additional Notes

• Ensure that the SQL Server Agent service account has permissions to execute the SSIS package.
• Consider configuring a proxy for SSIS package execution if you require separate permissions.
• The package path should be specified as a fully qualified path to avoid errors.

The above is the detailed content of How to Correctly Execute SSIS Packages within SQL Server Agent Jobs?. For more information, please follow other related articles on the PHP Chinese website!