Solved

Run SQL SISS package sql 2012

Posted on 2013-05-16
7
405 Views
Last Modified: 2016-02-11
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
0
Comment
Question by:RichardSmee
  • 3
  • 3
7 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 39171181
Job is not Package. You need to create a Job that runs the SSIS package
0
 
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.


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

Author Comment

by:RichardSmee
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.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 18

Expert Comment

by:x-men
ID: 39180570
can you execute it with DTEXEC ?
0
 

Author Comment

by:RichardSmee
ID: 39180658
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.
0
 
LVL 18

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:RichardSmee
ID: 39180706
Yep that worked. Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

939 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now