asked on

Run SQL SISS package sql 2012

Hi,

I am trying to execute an SISS package that I have stored on the server from a stored procedure using the following ;

EXEC msdb.dbo.sp_start_job @job_name = 'AvantixImport'

but I get the message The specified @job_name ('AvantixImport') does not exist.

I can see the job listed under Integration Services / Stored Packages / MSDB but cannot run it. Any ideas please ?

Thanks

x-men

Job is not Package. You need to create a Job that runs the SSIS package

Alpesh Patel

Did you saved the package in Integration catalog or in SQL server?

Integration catalog is new feature of SQL server 2012.

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_10949-Integration-Service-Catalog-in-SSIS-2012.html

RichardSmee

ASKER

Hi sorry not understanding your comments. I have a stored package under /integration services / stored packages / msdb and now (after some potching) I also have it under integration services catalogs / SISSDB, neither of which I seem able to execute from transact SQL.

x-men

can you execute it with DTEXEC ?

RichardSmee

ASKER

Yes but only after ;

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

to allow you to run cmdshell and then

EXEC xp_cmdshell 'C:\"Program Files (x86)\Microsoft SQL Server"\110\DTS\Binn\DTExec /f "C:\revenueimport\import avantix.dtsx"'

to run the package in 32 mode because it's from an excel spreadsheet.

This Soooooooo grim. Is there a way of running a package stored within SQL on the SQL server without resorting to running it on a command shell and lowering the security - without upgrading to SQL 2005 that is (sarcasm).

Thanks for your patience this is just getting stupidly complicated for such a simple thing.

ASKER CERTIFIED SOLUTION

x-men

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RichardSmee

ASKER

Yep that worked. Thanks