run SSIS from SP?

Question

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

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

Justin Lezon SSCommitted Points: 1937 More actions · Answer 1

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

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 2

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

VRT SSCertifiable Points: 5776 More actions · Answer 3

Thanks for your reply.

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

Thank You