opc.three (10/8/2012)

---

arussell_10 (10/5/2012)

---

All,

The development group at my new company is using a WCF service running on the SQL Server to call a stored procedure that executes xp_cmdshell to call an SSIS package. They are doing this in order to handle control flow to know when they can execute their next package. I am not sure why they didn't just create a master package to control the flow of all the packages, but they didn't. Now I'm looking for any issues that might arise for the SQL Server as a result of running the WCF service on the SQL Server and calling xp_cmdshell numerous times to execute loads of millions of records at a time. For example, could this cause SQL Server to crash, cause performance issues, etc..? (I'm aware of the security concerns with xp_cmdshell and I typically turn it off.) However, I'm not sure I have a choice in this deployment so I'm trying to do my best to be prepared for potential issues. I have been researching it myself but am not finding anything. Thanks in advance for your help.

Thanks,

arussell_10

Be careful using xp_cmdshell in this way. You are right to have concerns about security, but aside from that, one thing you give up is the ability to cancel an executing SSIS Package via the connection to SQL Server. With Packages processing large swaths of data that could be a major concern.

The scenario goes like this:

1. Execute stored procedure.

2. Stored procedure executes xp_cmdshell.

3. xp_cmdshell calls DTExec.exe.

4. OS process is spawned.

5. Caller decides processing it taking too long and cancels request. *

6. Database sessions ends and stored procedure exits/returns control to caller.

7. DTExec.exe process spawned by xp_cmdshell is still running on the OS.

* Or the process times out. Watch out for this from .NET. The default ADO.NET CommandTimeout is 30 seconds.

To recover from this scenario you would need to log into the OS and kill the OS process running DTExec.exe. If you have multiple clients spawning DTExec.exe via xp_cmdshell you'll have a fun time trying to decide which one to kill. Have you ever seen the end of The Abyss?

I would strongly urge you to recommend to your app design team that they go a different route.

Personally, I would not involve the database in the execution of an SSIS Package via xp_cmdshell. Is upgrading an option? In SQL 2012 you can execute SSIS natively within T-SQL.

Here is how you could safely implement remote execution of SSIS with SQL 2008:

Loading and Running a Remote Package Programmatically