Today I’ve got a question from a student (I was teaching a DTS/AS course) that asked me if the use of sp_OAxxx stored procedures to launch a DTS package execution is a good thing (of course, in a production environment). My answer is very simple: never ever ever ever!
Now, I then looked for some articles on the web talking about this and… there are too many articles that suggest to use sp_OAxxx stored procedures without warning about security issues.
So, for the next hundreds of web search on google about this topic, let me say this very clear.
Don’t use sp_OACreate, sp_OADestroy, sp_OAGetErrorInfo, sp_OAGetProperty, sp_OAMethod, sp_OASetProperty and sp_OAStop without taking care of security issues related to these stored procedures. If you want to execute a DTS Package, these stored procedures load the package in-process with SQL Server and a serious crash of DTS could be fatal to the running SQL Server instance. If you are lucky and don’t get any bug, you are still using a precious resource for a SQL Server service: virtual memory, and you need a lot of virtual memory for SQL Server when you are loading data in data mart tables.
An execute process would be a better choice. Use a separate process other than SQL Server service to execute a package.