Solved

Run SQL SISS package sql 2012

Posted on 2013-05-16
7
400 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
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.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

760 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

19 Experts available now in Live!

Get 1:1 Help Now