RichardSmee
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
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
Job is not Package. You need to create a Job that runs the SSIS package
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
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
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.
can you execute it with DTEXEC ?
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\DTExe c /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.
-- 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\DTExe
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep that worked. Thanks