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
|