Using CLR Stored Procedure to run SSIS packages
-
Has anyone written a CLR Stored Procedure that takes the name of an SSIS package and runs it? I am looking at the possibility of building a Service Broker application to handle the daily importing of data from source systems to an "ODS" database prior to loading a data warehouse. This process will also handle the exporting of data other application systems, most supported by MSP's.
This is only one alternative I am looking at, but would welcome any thoughts, guidance, or gotcha's that I should be considering at this time.
-
Why CLR when you can do the same via t-sql?
Joe
-
How would you run an SSIS package from T-SQL?
-
If you want to do this from inside the database, you would need to allow and use unsafe assemblies in your database. Also, you would probably need to add all the supporting (System.*) CLR assemblies to the database (CLR assemblies and their references stored in the database, so if you need to do this is several databases, you may need to add these to all of them).
You could also create a windows service that is invoked from the database. In this case the stored procedure that sits on the service queue needs to communicate with the windows service, but in this case you may be able to get away with "External Access" CLR assemblies.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Why would I need to allow and use unsafe assemblies in the database? So far in my research I haven't seen that, but of course, I may not have gone as far as I need to yet, so any guidance here would be extremely welcome.
Using a windows service, would I be able to run multiple packages and would the call(s) to the service be syncronous? I would want the Service Broker app to run several packages at the same time.
My other alternative that look viable is to use xp_cmdshell and run the packages from a command line.
A third alternative is to have the Service Broker app simply start a job using sp_start_job, but that adds additional complexity to the starting of the jobs, as I need to limit the number running at any given time. Our current process sometimes has memory issues and we are still trying to tweak that process in our development environment.
-
This is the link in BOL I started my research with:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/2f9fc1a8-a001-4c54-8c64-63b443725422.htm
-
By default CLR stored procedures cannot do whatever they want in the database. E.g. they cannot pop up a dialog window, etc. This is achieved via code access security. If you want to access files or the network, you need to specify that your CLR assembly is allowed to access the file system, communicate with an external service, etc. This is what the unsafe and external access permission sets are for. I think you will need them. More info is on:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/2111cfe0-d5e0-43b1-93c3-e994ac0e9729.htm
If you would like to have concurrency in SQL Server for your packages the Service Broker can handle that without a problem. With the Service Broker you would generally have a request queue with a CLR stored procedure that handles the requests, and you can specify how many queue readers you want to have running in parallel handling the requests.
A good starting point for queues is ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/srvbprg9/html/041db48b-83df-41d3-be9c-b3ca3253e802.htm
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
i'd use xp_cmdshell - keeps things simple!!!
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
-
You have a lot of opinions coming at you, but I will add another one anyway.
I would avoid calling an SSIS package directly through the CLR. It is correct that you would need to do this by allowing unsafe assemblies and that just sounds bad to me. It seems you want to make this event-driven but limited to a particular number of items running at a given time and Service Broker is pretty good at that so I would tend to think you are in the right direction there.
As far as launching the package, I think you have three reasonably safe options. The first is xp_Cmdshell, which would probably work fine and be pretty simple to implement on your queue (probably the best option in my opinion). The second would be to create a single job agent job for each allowed broker processor and have the queue processor call sp_start_job only if the job is not already running (pretty each to check). The third option would be to use a CLR procedure to call a web service and have the web service start the SSIS package. You would have to dive into C# a bit and create your own web service to start an SSIS package, but it is not much code. Doing it this way, you would not have to allow unsafe assemblies because the web service would be out of process.
-
One way to run an SSIS package from T-SQL is to set it up as a job and then start that job in TSQL. Assuming you have a job "Weekly Sales Data Backup", you could start it with the following code:
USE msdb ;
GO
EXEC dbo.sp_start_job N'Weekly Sales Data Backup' ;
GO
-
Fully aware of using SQL to start jobs, or using xp_cmdshell to execute command line programs. Starting a job is asyncronous, and would require additional coding in both the job and the application that starts the processes, as well as creating a job for every SSIS package that needs to be run. Using xp_cmdshell is syncronous, and requires that xp_cmdshell access be enabled (as I am the DBA on the DW systems, I do have control of this, so if I go this way I only have to convince myself), and reducing the additional coding required for an asyncronous solution requires. It does require the writing of a command line for each package, but that can also be stored in a table and pulled when needed.
BOL shows code for running SSIS packages in an application. I am hoping, though not encouraged, that it may be possible to use a CLR stored procedure to run a SSIS package inside the database by simply passing the name of the package to run. We won't need 100's of jobs, or enable xp_cmdshell to accomplish the task. I am, however, pragmatic and will use which ever method turns out to be the best/easiest/quickest/etc way to meet the goals of the system: to reliably import the data from numerous source systems in the most efficient way possible and to recover from hardware/network failures/outages both planned and unplanned.
I was hoping others had gone through a similiar process and could provide me with additional guidance and wisdom so I don't spend too much time going down a possible dead-end path.
-
Hi Lynn,
You can use a regular stored procedure to run SSIS packages with a Job since SQL Server 7.0 Microsoft have changed a few things and have documented somethings. A stored procedure running xp_cmdshell through proxy to run the Job works. The Agent proxy must be Admin and the owner of the package must be Admin.
In Asp.net application you just call the stored procedure as any other stored procedure, I have helped more than fifty users I know it works. I have seen Mcirosoft employees giving instructions for Web service that can be tricky because what you can pass to a web service is limited, you don't have that problem with Asp.net. Hope this helps.
http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp
http://support.microsoft.com/kb/918760/
http://support.microsoft.com/kb/938086/en-US
Kind regards,
Gift Peddie -
Hi ,
I am trying to the exact same thing. I want to create a CLR to run several different packages asynchronously and then once they are done report back. I have found Asynchronous Delegates examples which seemed to do the trick. I started writing code and turns out you can't add
'using Microsoft.SqlServer.Dts.Runtime.Wrapper;'
in VS 2008 or 2005 in the SQL project VS template. Since its not in SQL Server. And the assembly needs to be added to invoke a package.
I think the alternative is to create a C# project and not use the SQL project template, then deploy the CLR with Permission level unsafe so it can access the Dts assembly.
If there is another way can someone help.
Thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply