28

I'm trying to create an automated job for the SQL Server Agent to run. The job is supposed to run my SSIS package.

Here's what I have so far:

EXEC sp_add_job @job_name = 'My Job'
            ,@description = 'My First SSIS Job'
            ,@job_id = @jobid OUTPUT

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"'

Unfortunately when I run this, I get an error saying

Incorrect syntax near '/'

I suspect it's complaining about the /FILE in my command.

I can't find documentation about the appropriate syntax to use within @command anywhere -- I pulled /FILE out of some old code I found somewhere. What is the correct syntax for running an SSIS package in a job?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Slider345
  • 4,558
  • 7
  • 39
  • 47

2 Answers2

79

I ran the SQL script in the question but didn't encounter any errors. So, I am not sure what is causing the error in your case. However, if you have access to SQL Server Agent through SQL Server Management Studio. Here are the steps to create a job using the Graphical User Interface.

  1. Go to SQL Server Management Studio. Expand SQL Server Agent and right-click on Jobs, then select New Job... as shown in screenshot #1.

  2. Provide a name and Owner by default will be the account that creates the job but you can change it according to your requirements. Assign a Category if you would like to and also provide a description. Refer screenshot #2.

  3. On the Steps section, click New... as shown in screenshot #3.

  4. On the New Job Step dialog, provide a Step name. Select SQL Server Inegration Services Package from Type. This step will run under SQL Agent Service Account by default. Select the package source as File system and browse to the package path by clicking on ellipsis. This will populate the Package path. Refer screenshot #4. If you don't want the step to execute under the SQL Agent Service Account, then refer the steps #8 - 9 to know how you can use a different account.

  5. If you have a SSIS configuration file (.dtsConfig) for the package, click on the Configurations tab and add the Configuration file as shown in screenshot #5.

  6. Click OK and there is the package in step 1 as shown in screenshot #6. Similarly, you can create different steps.

  7. Once the job has been created, you can right-click on the job and select Script Job as --> CREATE To --> New Query Editor Window to generate the script as shown in screenshot #7.

  8. To run the SSIS step under different account, on the Management Studio, navigate to Security --> right-click on Cedentials --> select New Credential... as shown in screenshot #8.

  9. On the New Credential dialog, provide a Credential name, Windows account and Password under which you would like to execute SSIS steps in SQL jobs. Refer screenshot #9. Credential will be created as shown in screenshot #10.

  10. Next, we need to create a proxy. On the Management Studio, navigate to SQL Server Agent --> Proxies --> right-click on SSIS Package Execution --> select New Proxy... as shown in screenshot #11.

  11. On the New Proxy Account window, provide a Proxy name, select the newly created Credential, provide a description and select SQL Server Integration Services Package as shown in screenshot #12. Proxy account should be created as shown in screenshot #13.

  12. Now, if you go back to the step in SQL job, you should see the newly created Proxy account in the Run as drop down. Refer screenshot #14.

Screenshot #1:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

Screenshot #6:

6

Screenshot #7:

7

Screenshot #8:

8

Screenshot #9:

9

Screenshot #10:

10

Screenshot #11:

11

Screenshot #12:

12

Screenshot #13:

13

Screenshot #14:

14

starball
  • 20,030
  • 7
  • 43
  • 238
  • 1
    Thanks Siva, this is the most complete answer to my question. – Slider345 Jul 19 '11 at 17:53
  • If you use Configuration table (`dbo.[SSIS Configuration]`) instead of configuration file (`dtsConfig`), be advise to manually edit the `dtsx` file to specify the connection string directly to the table. – itsho Dec 16 '14 at 10:50
  • Can you please provide **Sql Script** for this job creation for SSIS package deployment, to run in client system.. i wnt get access to do manually using wizard – A R S Apr 20 '15 at 14:11
  • This is awesome! Since MS decided to delete the old articles and redirect everything to the new SQL server docs this article finally helped me. – user3772108 Dec 12 '17 at 14:12
9

If you're building the job using the sp_add... procs, I think you need to explicitly set the subsystem to be called, a la 

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

This is equivalent to @Siva's Screenshot #4 'Type'='SQL Server Integration Services Package'.

See this MSDN article for the complete list of subsystems.

EDIT: 'TSQL' is the default, so your current setup is trying to execute '/FILE "D:\Installs\Upload.dtsx"' as a T-SQL command.

DaveE
  • 3,579
  • 28
  • 31