Data Transformation Services (DTS)
September 24, 2000
The most common method used to execute DTS packages from within T-SQL scripts or stored procedures, is via xp_cmdshell and dtsrun. Whilst this works, and is very simple the implement, it lacks flexibility. The SQL Server 2000 version of dtsrun allows you to pass GlobalVariables on the command line, which improves it's appeal, but the security restrictions on xp_cmdshell can still limit it's appeal. This article demonstrates the OLE stored procedures and some of the benefits of using them.
The OLE stored procedures are a very powerful mechanism to access resources outside of those core to SQL Server, and this article demonstrates their use in relation to DTS. Whilst everyone else know refers to COM, not OLE, the SQL Server Books Online still uses the term OLE Automation, and the stored procedures are prefixed with sp_OA.
All samples use the OLE Automation Return Codes and Error Information stored procedures listed in SQL Server Books Online. The main procedure, sp_displayoaerrorinfo, takes the return code from any of the standard OLE Automation stored procedures, and displays the error Source and Description using sp_OAGetErrorInfo. It also calls sp_hexadecimal to convert the integer error code into a string (char) representation of the true hexadecimal value.
For clarity variable declaration has been removed from the code snippets below
Creating the DTS Package Object:
Loading the Package:
Executing the Package:
You'll notice that my style of calling methods is to feed the whole command,
complete with parameters as a character string. The alternative to this is illustrated below, Loading the Package: