Data Transformation Services (DTS)

Overview

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:

EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
    PRINT '***  Create Package object failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

Loading the Package:

-- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256 
EXEC @hr = sp_OAMethod @oPKG, 
  'LoadFromSQLServer("MyServer", "", "", 256, , , , "MyPackage")', 
  NULL
IF @hr <> 0
BEGIN
    PRINT '***  Load Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

Executing the Package:

EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
    PRINT '***  Execute failed'
    EXEC sp_displayoaerrorinfo @oPKG , @hr
    RETURN
END

Cleaning up:

EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
    PRINT '***  Destroy Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

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:

EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSqlServer', NULL, 
  @ServerName='MyServer', @PackageName='MyPackage', @Flags=256
IF @hr <> 0
BEGIN
    PRINT '***  Load Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END