Run SQL SISS package sql 2012

Posted on 2013-05-16
Last Modified: 2016-02-11

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 ?

Question by:RichardSmee
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 18

Expert Comment

ID: 39171181
Job is not Package. You need to create a Job that runs the SSIS package
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39171300
Did you saved the package in Integration catalog or in SQL server?

Integration catalog is new feature of SQL server 2012.

Author Comment

ID: 39180495
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.
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

LVL 18

Expert Comment

ID: 39180570
can you execute it with DTEXEC ?

Author Comment

ID: 39180658
Yes but only after ;

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

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.
LVL 18

Accepted Solution

x-men earned 500 total points
ID: 39180663
create a SQL Agent job, that runs the SSIS package in 32bit mode.

execute the SQL Agent Job.

Author Closing Comment

ID: 39180706
Yep that worked. Thanks

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question