How to Schedule and Run an SSIS Package using SQL Server Agent?

How to Run an SSIS Package in a SQL Server Agent Job

Creating a Job Step to Execute an SSIS Package

You can create a job step using T-SQL commands, but the GUI method is more user-friendly. To create a job step to execute an SSIS package:

1. In SQL Server Management Studio, navigate to SQL Server Agent > Jobs > New Job....
2. Provide a Job name, Owner, and optional Description.
3. In the Steps section, click New....

4. In the New Job Step dialog:

   • Assign a Step name.
   • Select SQL Server Integration Services Package as the Type.
   • Choose File system as the Package source.
   • Browse to the package path using the ellipsis.
5. If you have a configuration file (.dtsConfig), click Configurations and add the file.
6. Click OK.

Troubleshooting the Error: Incorrect Syntax near '/'

The error you encountered with the /FILE command is likely due to using a deprecated syntax. To execute an SSIS package correctly, use the following syntax:

EXEC sp_add_jobstep @job_id = @jobid
                    ,@step_name = N'Upload Data'
                    ,@step_id = 1
                    ,@command=N'/TargetServer "\SSISDB\Upload.dtsx"'

Note that the package path should now be enclosed in double quotes and preceded by /TargetServer.

Running the SSIS Step Under a Different Account

If you need to execute the SSIS step under a different account than the SQL Agent Service Account:

1. Create a Credential with the necessary Windows account and password.
2. Create a Proxy that uses the Credential and selects SQL Server Integration Services Package as the type.
3. In the job step's Run as drop-down, select the newly created Proxy account.

By following these steps, you can create a job step to run your SSIS package and resolve the incorrect syntax error.

The above is the detailed content of How to Schedule and Run an SSIS Package using SQL Server Agent?. For more information, please follow other related articles on the PHP Chinese website!