Post reply

run SSIS from SP?

  • March 5, 2009 at 7:08 am

    #201983

    Hi,

    I just got a different doubt.

    Is it possible to run the SSIS pacakge from a Stored Procedure?

    Thanks in advance,

    Venki.

    Thank You

  • March 5, 2009 at 8:15 am

    #954073

    Create a SQL Job to run the SSIS package and then execute the job from the sp.

  • March 5, 2009 at 9:52 am

    #954155

    Take a look in Books Online at the system procedures sp_add_job, sp_add_jobstep, sp_add_jobserver, sp_start_job and (probably) sp_delete_job.

    Something like...

    use msdb

    exec sp_add_job

    @job_name = 'Test 1'

    declare @cmd varchar(100)

    --

    -- Command value was simply copied from an existing job

    --

    set @cmd = '/FILE "E:\Data\ssis\Email outstanding user requests.dtsx" ' +

    '/CONNECTION "email.txt";"e:\Data\ssis\email.txt" ' +

    '/CONNECTION "email.xsl";"e:\Data\ssis\email.xsl" ' +

    '/CONNECTION "GBMASQLDB.Perf";"Data Source=GBMASQLDB;Initial Catalog=Perf;Integrated Security=True;" ' +

    '/CONNECTION "ureqs.htm";"e:\Data\ssis\ureqs.htm" ' +

    '/CONNECTION "ureqs.sql";"e:\Data\ssis\ureqs.sql" ' +

    '/CONNECTION "ureqs.xsl";"e:\Data\ssis\ureqs.xsl" ' +

    '/MAXCONCURRENT " -1 " ' +

    '/CHECKPOINTING OFF'

    exec sp_add_jobstep

    @job_name = 'Test 1',

    @step_name = 'SSIS',

    @subsystem = 'dts',

    @command = @cmd

    exec sp_add_jobserver

    @job_name = 'Test 1',

    @server_name = 'GBMASQLDB'

    exec sp_start_job

    @job_name = 'Test 1'

    --exec sp_delete_job

    -- @job_name = 'Test 1'

    Result:

    Job 'Test 1' started successfully.

    Derek

  • March 6, 2009 at 5:33 am

    #954777

    Thanks for your reply.

    It seems I am getting info after lookng into Books online.

    Thank You

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply