January 25, 2008 at 9:27 am
I have built a SSIS package to load the work data into my tables.
The work data will be minipulated by a 2.0 Web Application.
I would like the Web Application Users to be able to run the SSIS package on demand.
Is it possible to exeute an unschechuled Agent Job from a stored procedure?
If so what is the TSQL code?
I've tired using "EXEC msdb.dbo.sp_start_job 'gkLoad';" but though the stored proceedure claims to complete sucessfully the SSIS package never runs.
I'm open to other solutions for this, but it must be something that is "kicked off" by clicking a web link.
MANY, MANY THANKS!
January 25, 2008 at 10:51 am
EXEC msdb..sp_start_job is the correct procedure. If the procedure is reporting success and the job agent history is not showing that the job has run, you have probably mis-spelled the job name or something trivial like that. The job agent service must be running for this to work.
The procedure is asynchronous - it will not wait for the job to run and report the success or failure of the job. It always returns immediately with a notification that it successfully started the job. So, the procedure will not report an error if something goes wrong in your SSIS package.
As an alternative, the SMO objects include methods for starting jobs and viewing their progress.
January 25, 2008 at 4:04 pm
As Michael said, msdb..sp_start_job will start the job asynchronously. Getting your code to wait for the job to finish and check the return/exit status is very non-trivial, and possibly not worth the effort. (I've done it--review the code in sp_start_job, follow it down a few nested procedure calls, hack out some code that references undocumented but decipherable system objects, work in some WAIT commands... can be done, but it's a lot of nuisance value.)
A question: must you launch the SSIS package via a SQL Agent job? Under the hood, all SQL Agent really does is shell out to the command prompt and call DTEXEC with the (many) necessary parameters. However, if you build and xp_cmdshell that command from within a stored procedure, you get a lot of programmatic control over the parameters being passed in, as well as synchronous execution and timely evaluation of return/exit codes. (You may have issues with security--server and database access gets tricky when you start dealing with xp_cmdshell from non-SysAdmin logins.)
I do not recommend creating multiple calls to the same SSIS package--either call it from SQL Agent, or call it from a proc. Since a proc can launch a job, and a job can call a proc, all you (hopefully) have to figure out is which works best for your requirements and environment and standardize accordingly.
Philip
January 28, 2008 at 7:00 am
Asyncrounus running is fine. The SSIS package is built to post process statuses and errors to the database it's running aginst already and those are watched by the website so the user will know when the package is complete.
As for why I'm using a stored Proc:
1) The package must be run on demand by the user and can not be scheduled.
2) I am NOT allowed to put code on the web server that makes RPC calls or put scripting files on the data base server. Network makes the rules and talking to them is like driving into a brick wall.
I've triple checked the name of the package and that is not the problem. I had the DBA give the DBUser account ownership of the job, but that didn't work either. Perhapse there is some other security or permissions I need?
January 28, 2008 at 8:23 am
You do need the appropriate permissions in the MSDB database. There are some roles in the database - I cannot remember which you need to be a member of. Connect to the database via management studio using the login the web page is running from and try running the procedure. It will error if you do not have the appropriate permissions.
If you are using NT authentication based on the user who is accessing the web page, you may need to have your Kerberos server configured correctly to allow the login token to jump far enough to get to the SQL server.
February 12, 2008 at 12:17 pm
I've had the same problem.
Solved using a Job as you need to do.
1.- in the SSIS package do save copy of package as.....
2.- Package Location= SQL Server
3.- SSIS Package Folder in Server
4.- Protection Level =Encrypt sensitive data with user key (connection information is encrypted in de SQL and not in user profiles).-
5.- Generate a Job that calls this dtsx.
6.- from a web page call a SP that call this job.
Its a bit complicated but I tried usingproxyi and all methods that MS says should work and always got error msg.
Hope this works.-
July 18, 2008 at 7:25 am
I am running into the same exact situation.
I have a ASP.NET web app that calls to a SP to execute an Agent Job which involves 3 steps. Each step execute a different SSIS.
How do you obtain the status of the job thru the web app? I would like to have some kind of status output in my web app to notify the user if the job has completed or failed. Thx.
July 18, 2008 at 12:55 pm
nevermind, i got it.
August 19, 2008 at 7:49 am
can anyone advise on how to do this from within VFP9?
September 19, 2008 at 3:33 pm
Hi
Am doing the same kind of code.. I need to run a package on a remote server(which doesnot have SSIS) from my server (which has SSIS). Am using sp_start_job to start a process RUNSSISPACKAGE. Now where do i mention the package name to be handled by this job ?
Can someone send me an example code snippet for this please..
September 22, 2008 at 9:42 am
sri,
Create the job step and select "SQL Server Integration Services Package" in the "Type" dropdown. When you do, you'll see a bunch of tabs where you can enter the package location, package name, config files, etc.
Greg
October 8, 2008 at 2:56 am
Greg Charles (9/22/2008)
sri,Create the job step and select "SQL Server Integration Services Package" in the "Type" dropdown. When you do, you'll see a bunch of tabs where you can enter the package location, package name, config files, etc.
Hey there mate, I have the same issue (though hopefully a little easier to solve). I have a SSIS package which is called from a SQL Agent Job (ad-hoc). The job should then be called from an SP (executed by the app). Things is it wont run the job. Keep getting:
Msg 229, Level 14, State 5, Procedure sp_start_job, Line 1
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.
Even in SMS. Works fine as a SysAdmin but that not really the point. Have tried setting up credentials linked to proxies and all that and no joy (not that there isnt a chance i messed up).
I basically just want the t-script to grant exec to {user} for {job} e.g grant exec to {user} on "sp_start_job N'MyJob'".
Can anyone help?
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 20, 2008 at 6:48 am
SO how is an SSIS job created - I cna find nothing so far in the way of examples
I basicaly want to download a csv file from a webstie and them import it into a sql table without importing to a foxpro table and then sending to the sql table
January 6, 2009 at 8:52 am
I don't know if you got your answer yet, but here is a link to a post that might help clarify things...
http://3msoftwaretechnologists.blogspot.com/2008/12/calling-ssis-package-from-stored.html
February 3, 2009 at 2:54 pm
I know this is a SQL Server Forum, but as an alternative, you can also call off the package from .NET. Our company had a very similar requirement
in that we needed to call off the package when a link was clicked. The benefit of calling this from .NET is that you can process the results from the package call. If any errors occur, you can obtain these errors in the code and act accordingly. In case you were interested in going this route, the following link provides a good tutorial to get started with this:
http://www.codeproject.com/KB/database/CallSSISFromCSharp.aspx
Bob Pinella
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply