How to execute ssis package from stored procedure
how to excute ssis package from stored procedure and get the parameters back from ssis into the stored procedure.
July 23rd, 2005 1:28am
Can I ask why you want to do this?
July 23rd, 2005 9:21am
I have a SSIS package that does data query based on the Fuzzy Logic. Parameters are passed to SSIS and SSIS returns the recordset. Now I have a Java based web application that calls the procedure for many things, one of the things that procedure wants to do is extract records from various application databases based on fuzzy logic.I know SSIS is normally used to do ETL stuff but what if I use its good features to make the usage of SSIS more broadbased.Another thing which comes to my mind is, how about if multiple instances of a same SSIS package are running at the same time. I mean same SSIS package is called by the application again and again; and in a scenario where many calls are made, its quite possible that multiple instances of the same package are running at the same time. How practically feasible is this scenario? And what are the performance issues attached?
July 25th, 2005 7:51pm
Any Idea Experts??
July 28th, 2005 1:35am
You may want to try this procedure to run a vb script from a SQL proc (I think you can modify it to run almost anything), it comes from this article: http://www.sqlservercentral.com/columnists/aloera/sqlserverscriptingandwmi.aspsp_RunVBS Stored Procedure--------------------------CREATE PROCEDURE sp_RunVBS(@cmd VARCHAR(255), @Wait INT = 0) AS--Create WScript.Shell objectDECLARE @result INT, @OLEResult INT, @RunResult INTDECLARE @ShellID INTDeclare @Folder varchar(255)Select @Folder = '"C:\LocalSecureDirectory\'Select @cmd = @Folder + @cmd + '"'--select @cmd --testEXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUTIF @OLEResult <> 0 SELECT @result = @OLEResultIF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult)EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', NULL, @cmd, 0, @WaitIF @OLEResult <> 0 SELECT @result = @OLEResultIF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult)If @OLEResult <> 0 EXEC sp_OAGetErrorInfo @ShellID, @OLEResult Horseshoe
September 6th, 2005 9:37pm
I suggest that using the CLR would be a better option giving you greater control and more feedback. Using sp_OA like is this a security risk really.
September 7th, 2005 10:51am
As long as you limit the directory access, and assign security to this procedure through SQL server like you normally would for a procedure that you dont want users to execute, I don't understand what the issue can be.
September 10th, 2005 6:25pm
Darren, I'd very much like to use CLRfor the reasons you give. Do you have a code example using CLR in a stored procedure to run an SISS package where parameters are passed in and out? I have aDTS package on my SQL 2000 box that I want to move to 2005. The current code uses sp_OACreate 'DTS.Package' but I would prefer to use CLR if it was possible. From my own investigations I haven't found a way to do it. Thanks for any help. Tim
September 28th, 2006 6:54pm
I don't think you can use the CLR inside the SQL engine to execute SSIS. There are some restrictions of using the CLR in this way and SSIS does not meet them I am afraid. I think the best uou coul do would be to try and start a proess, DTEXEC, although I have not tried that either, so may also fall foul of the a limitation.
October 2nd, 2006 8:35am
Why not create a job without a schedule and use sp_start_job to run it?
October 2nd, 2006 1:29pm
Darren, I need to allow multiple users to run an SSIS package that imports a preformatted user specified spreadsheet to holding tables for further validation and return a few stats about the data. The spreadsheet name, server and db name are parameterised. Below are my notes on what Ive found to be the available choices and would be interested in any feedback. xp_cmdshell requires CONTROL SERVER permission & sp_OACreate requires membership of the sysadmin fixed server role. However granting sp_OACreate (+ sp_OADestroy etc) execute permission to users (via a user defined role) would get round this as users would only have additional permission to perform the appropriate tasks. The stored procedure would use supplied parameters to create and execute a dynamic DTEXEC command. This solution would be easy to implement and support but Id imagine is not best practice. If taking this path, are there any ways to limit the potential danger of the security change? I havent been able to find an SSIS equivalent of sp_OACreate 'DTS.Package' as this would be a more useful object to use. e.g. code would look something like this (based on horseshoes above post): DECLARE @cmd VARCHAR(255) DECLARE @Wait INT DECLARE @result INT, @OLEResult INT DECLARE @ShellID INT SET @Wait = 0 SET @cmd = 'DTEXEC /sq "TestSSPSPackageName" /ser <server name> /Set \Package.Variables[User::varTargetName].Properties[Value];"c:\dtsxTest\Test2.txt"' -- create instance of OLE object EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT IF @OLEResult <> 0 SELECT @result = @OLEResult IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult) -- run package EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', NULL, @cmd, 0, @Wait IF @OLEResult <> 0 SELECT @result = @OLEResult IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult) If @OLEResult <> 0 EXEC sp_OAGetErrorInfo @ShellID, @OLEResult -- drop object EXECUTE @OLEResult = sp_OADestroy @ShellID -- I think this will run an an asynchronous process. sp_start_job is recommended on this forum as the method for running packages from a SP. The difficultly here is that I cant find a way to easily supply variables. It looks like Id have to write the parameters to a table and get the package to read them in. I'm not sure how getting return values squares with the job running as an asynchronous task. All in all this seems to be an awkward, difficult to support solution. By the look of it, MS expect packages to be run from client code if security degradation or unwieldy code is to be avoided. However you cannot run packages outside BI Development Studio on a client machine that does not have Integration Services installed, and the terms of your SQL Server 2005 licensing may not let you install Integration Services on additional computers. I understand that to mean Ill have to license and install software on every client machine. MS also suggest running a Web Service or Remote Component in the server. http://msdn2.microsoft.com/en-us/library/ms403355.aspx#service Again this seems like an unwieldy tool chain. Perhaps it would be easier to avoid SSIS altogether, save my spreadsheet as a CSV and run a bcp instead. I'm surprised there isn't a way to easily run SSIS packages from stored procedures as itseems such an obvious requirement. Id be interested to know if Ive overlooked/misunderstood anything. Tim
October 4th, 2006 5:40pm
Hi again, Look at the security from both a folder level, and a proc level to be able to have a user run this...At the folder level you secure the folder so only the 'sql user' can have execute privies, the users should not know the credentials that this 'sql user' has to be able to use or execute the script in the folder.At the folder level you have an execute script vbs, that uses DOS to execute the SSIS trough DTSEXECAt the database level, you create a proc that uses SP_OACREATE to execute the particular vbs scriptAt the database level, you give "execute only" to the 'sql user' running it.At this point, you remain secure. But if you still have worries, then create a job that executes the SSIS package, and have the users execute a proc that runs the SSIS job, which in turn will run the vbs script (or dos command if you dont like VBS) that runs the SSIS package...whew!
October 5th, 2006 7:52pm
Thanks for taking the time to respond. Permission to execute sp_oacreatehas to be explicitly granted to users if they aren't to be given membership of the sysadmins role. As I understand it, if you can execute a sp_oacreate command you can run any command line using wsccript.shell. SQL injection attack springs to mind.... This approach is far from ideal and only seems to be appropriate where the users are known, trusted etc. I remember reading a forum question from a person wanting to run ssis packages from a sp called by a web front end. I'd certainly avoid using the sp_oacreate approach to do this. Ideally I would like to run the SSIS package from a job but cant find a way of reliably & conveniently passing variables to the package. Jobs dont seem to be able to take parameters and pass them onto the ssis package. Using a table to temporarily store the variables is the approach I looked at but would need to deal with multiple import jobs being called at the same time. Ive be very interested to know if this has been done successfully.
October 10th, 2006 6:13pm
Crispin said: Why not create a job without a schedule and use sp_start_job to run it?Hi,I was looking for a simple way to execute a SSIS and this seems work well. Many thanks Vijay
March 12th, 2009 3:04pm
Crispin.Good idea and it does work. However, it seems to run asynchronously. Is there a way to track when the job finishes?ThanksPeej
May 5th, 2009 10:35pm
SELECT @OUTCOME = ISNULL(RUN_STATUS,4) -- IF NULL JOB IS STILL RUNNING -- 4IS SIMILAR TO THE STATUS IF ONE STEP WAS COMPLETE --BUT THE SECOND STEP WAS STILL RUNNING. FROM [Database Name] .MSDB.DBO.SYSJOBS Z INNER JOIN [Database Name]. MSDB.DBO.SYSJOBACTIVITY A ON Z .JOB_ID = A.JOB_ID INNER JOIN ( SELECT MAX(SESSION_ID) AS SESSION_ID FROM [Database Name]. MSDB.DBO.SYSSESSIONS ) AS B ON A .SESSION_ID = B.SESSION_ID LEFT JOIN [Database Name]. MSDB.DBO.SYSJOBHISTORY C ON A .JOB_HISTORY_ID = C.INSTANCE_ID WHERE Z .NAME = 'YOUR JOB NAME HERE'This works for me. Keep calling this until it returns a one or a zero. I think 0 means job ran successfullyand 1 means the job failed. You will get 4 while the job is still running. This is part of a store procedure with @outcome as a output parameter
June 19th, 2009 5:12pm
I'm fairly surprised no one mentioned this, but for what you want to do with allowing a user to specify a spreadsheet and return information to the screen, the SSIS Package would be better called from the application layer than from within SQL Server. It would allow better control and you should be able to hook into the events much easier.
October 15th, 2009 4:30pm
Hi Tim,I store all package in 'File System' format in local network drive, so how can I run it like bellow?I create 1 SSiS package in SQL Server 2008 which load data from excel to SQL Server. I want to prvide file name like global variable, and dynamically load multiple file data 1 by 1. I have file list in SQL Table. So how I can provede file name to Excel source in Data flow task. Please reply me AEAP. if possible send me exaple/SET @cmd = 'DTEXEC /sq "TestSSPSPackageName" /ser <server name> /Set \Package.Variables[User::varTargetName].Properties[Value];"c:\dtsxTest\Test2.txt"'ThanksVasant Jagtapvasant_jagtap1@yahoo.co.in
February 2nd, 2010 10:46am
This seems to work fine for me....CREATE PROCEDURE sp_executeRunSSISPackage @returncode intASDECLARE @params nvarchar(400) SET @params = 'dtexec /file "c:\FILEPATH\SSISPACKAGENAME.dtsx"' EXEC @returncode = xp_cmdshell @params select @returncode
February 11th, 2010 7:05pm
Refering the SSIS execution by using a sql agent job. An SSIS execution can be tracked from a SQL AGENT Job only if the job is still running or not. But, Keep waiting for a job answer may reduce the server performance. Is there a way to make like a timeout for a SQL Server Agent Job? Or can I ask for another job status while waiting for it to end? Your information will be really helpfull. Thanks,
October 25th, 2010 8:58pm
I like to call a stored procedure that invokes a job that has an SSIS package with a step. You can set it up so that a process without rights to the job can execute it (i.e. we have Cold Fusion fire off SSIS packages). The stored proc looks like this CREATE PROCEDURE [dbo].[sp_ssis_RunJob] WITH EXECUTE AS 'mydomain\ServiceAccountID' AS EXEC msdb.dbo.sp_start_job @job_name = 'MY SQL AGENT JOB TO EXECUTE'
October 25th, 2010 10:04pm