Start SSIS package from a SQL Agent job

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Start SSIS package from a SQL Agent job

Start SSIS package from a SQL Agent job

Rate Topic

Display Mode

Topic Options

Author

Message

Warren Peace

Posted Thursday, October 18, 2007 9:35 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 06, 2012 9:48 AM
Points: 207, Visits: 450

I have a SQL job that kicks off to populate some tables with test data. I also have an SSIS package that copies the data to another development SQL server called ScrapAllData_RePopulate.

Within the SQL Agent job I want the final step to kick off my SSIS package. Can someone give an an example of how to do that?

Thanks

Post #412290

Adam Angelini

Posted Thursday, October 18, 2007 9:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:29 PM
Points: 1,084, Visits: 806

add a new job step. In the Type dropdown, select sql server integration services package. then you need to fill in the appropriate tabs. what you will fill in may vary greatly, so I can't really go into what you will need in there, but hopefully this gets you on your way.

Post #412301

mark_copley

Posted Friday, March 16, 2012 6:56 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:37 AM
Points: 232, Visits: 201

You could also call the SSIS package directly too if you wanted

DECLARE @sqlCmd VARCHAR(1250) -- call to ssis package
,@returnCode INT -- returncode from package execution
,@ssisPath VARCHAR(1250) - location of ssis package

SET @ssisPath = '\\server\ssis\mySSISPackage.dtsx'
SELECT @sqlCmd = 'DTEXEC /F "' + @ssisPath + '"'

-- call ssis package and return result
EXEC @returnCode=Master..xp_cmdshell @sqlCmd
SELECT @returnCode AS '@returnCode'

Post #1268195

Divine Flame

Posted Friday, March 16, 2012 7:13 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:00 PM
Points: 1,076, Visits: 1,914

Enabling xp_cmdshell just to execute the SSIS package? You already have the Integration Services Package step type in the agent job configuration. If you decide to call it from CMD anyway then you have "CMDExec" step type availabe in the job configuration.

- Divine Flame

Post #1268213

« Prev Topic | Next Topic »

Permissions