How to execute ssis package from stored procedure

How to execute ssis package from stored procedure

• dom_tiger_99

  Darren,

  I'd very much like to use CLR for 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 a DTS 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

  
  

• qwerty51015

  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.
  
  

• Tycotrix

  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.
  

• Arthur Knight

  Thanks for taking the time to respond.

  Permission to execute sp_oacreate has 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 can’t find a way of reliably & conveniently passing variables to the package. Jobs don’t 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. I’ve be very interested to know if this has been done successfully.

  
  

• loosie

  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 I’ve 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 I’d imagine is not best practice. If taking this path, are there any ways to limit the potential danger of the security change I haven’t 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 horseshoe’s 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 can’t find a way to easily supply variables. It looks like I’d 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 I’ll 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 it seems such an obvious requirement.

  I’d be interested to know if I’ve overlooked/misunderstood anything.

  Tim

  
  

• Carolyn Chau

  Any Idea Experts
  

• ndomain

  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
  

• mokeefe

  Why not create a job without a schedule and use sp_start_job to run it
  
  

• SalamELIAS

  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.asp
  
  
  sp_RunVBS Stored Procedure
  --------------------------
  CREATE PROCEDURE sp_RunVBS(@cmd VARCHAR(255), @Wait INT = 0) AS
  --Create WScript.Shell object
  DECLARE @result INT, @OLEResult INT, @RunResult INT
  DECLARE @ShellID INT
  Declare @Folder varchar(255)
  
  Select @Folder = '"C:\LocalSecureDirectory\'
  Select @cmd = @Folder + @cmd + '"'
  --select @cmd --test
  EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT
  IF @OLEResult <> 0 SELECT @result = @OLEResult
  IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult)
  
  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
  
  
  
  
  Horseshoe
  

• c.v.

  Can I ask why you want to do this

  
  
  

• kmax

  Hi again,
  
  Look at the security from both a folder level, and a proc level to be able to have a user run this...
  
  

  1. 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.
  2. At the folder level you have an execute script vbs, that uses DOS to execute the SSIS trough DTSEXEC
     
  3. At the database level, you create a proc that uses SP_OACREATE to execute the particular vbs script
  4. At 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!
  
  
  

• Rob Cannon

  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.
  
  
• How to execute ssis package from stored procedure