Jeff Shaver brought up a problem he is experienceing in a comment to my last post and instead of posting another comment in that entry, I decided to go ahead and post an article about how you can remotely execute SSIS packages.
Just to recap, we have now automated PowerDesigner so that we can get the create table statements out of it, automated the check in of these new scripts, then run the newest create table scripts against our destination server. At this point we are ready to fill the datamart. The next thing we do is deploy our SSIS package that fills the datamart by using DTUtil. Read more about this here.
The next thing to do is to call the SSIS package that we just deployed.
We have found a few different ways to do this. One way is to create a job on the server through T-SQL and add the package execution as a step in the the job and then turn around and execute the job using sp_start_job. The problem with this method is that sp_start_job doesn’t wait until the job is finished. It only starts the job and reports whether the job began successfully. This is OK, but the problem is that if you have some tasks you need to do after the job is completed, you don’t know when the job is done. We haven’t implemented it yet, but we have discussed having the package perform some sort of a callback – maybe through a database record write or maybe a web service call – something of this nature.
Another way to execute the package would be to use xp_cmdshell to call DTExec on the command line. This has its own set of problems. First of all, there is a security problem that has to be addressed if you decide to turn on xp_cmdshell. Second, if the job hangs, the xp_cmdshell is also hung. I haven’t tried this method out myself to see if the job is executed in-process and blocks the thread until the job is completed or not. I’m sure someone who has tried this can fill us in on that – although I’m pretty sure it will stay in-process.
A third way of doing the job (as Jeff suggested) would be to write a web service that loads up the SSIS package via SSIS objects and runs the package from there. I’m pretty sure this will work but I didn’t work on it long enough to see it all the way through. I also thought about using a CLR stored procedure to host the work – a slam dunk if you could – but it looks like you can’t use the assemblies that we need inside a CLR stored proc. 😦
One other way I want to mention in passing is that you can call DTExec from your build machine and execute a package located on a server. I assume that this runs the package on the build server and not on the server so this could be a performance problem. I did have one problem trying this – I got an error saying that I didn’t have the right version of SSIS to do a Data Conversion Task. I’m assuming that means that when you run packages from the command line on a machine with BIDS, it runs as if you have standard edition instead of enterprise. Not sure, but that is my hunch.
So a compromise is in order – you have to choose your poison.
We have decided, for now, to go with the job creation method. We’ll see how that goes and change it later if we need.
Thanks to Jeff for the inquiry.