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
  • 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.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 32
hyperlink data type in SQL 3 28
Need some help to cast ntext to nvarchar SQL 2000 7 34
SQL - Copy data from one database to another 6 20
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

821 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