Data Transformation Services (DTS)

Sunday Sep 24th 2000 by Darren Green
Share:

Use the T-SQL OLE (COM) stored procedures to execute and manipulate DTS Packages.

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

Advanced

Here we cover some of the more advanced uses you can make of the OLE Automation stored procedures and DTS.

Package Properties and GlobalVariables

The most obvious advantage of using the OLE Automation stored procedures is the ability to dynamically alter the package prior to execution. For example you can alter a connection's DataSource property, which is the filename property for a Text file Connection. I prefer a variation on this theme whereby you set global variables not the actual property, then use an ActiveX Script Task or a Dynamic Properties Task (SQL 2000 Only) within the package to amend the appropriate property. This way if you change your package, you don't have to change your SQL code. In addition it makes it easer to change the execution method, for example you might like to take advantage of the new functionality in the SQL 2000 dtsrun and pass in global variables, or you might decide to utilise the package in a program. In each case you can write the package once, and with minimal effort use different code to set your global variables and execute the package.

For clarity variable declaration has been removed from the code snippets below

Set a Global Variable:

EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("MyGVName").Value', 'MyGVValue'
IF @hr <> 0
BEGIN
    PRINT '***  GlobalVariable Assignment Failed'
	EXEC sp_displayoaerrorinfo @oPKG, @hr
	RETURN
END

 

As well as setting global variables, you can read them after execution, which is one method of returning data from a DTS package.

Return a single Global Variable:

EXEC @hr = sp_OAGetProperty @oPKG, 'GlobalVariables("MyGVName").Value', @GVOutput OUT
IF @hr <> 0
BEGIN
    PRINT '***  GlobalVariable Read Failed'
	EXEC sp_displayoaerrorinfo @oPKG, @hr
	RETURN
END
PRINT 'My Global Variable is - ' + @GVOutput

Enumerate all global variables:

-- Get Global Variables Count
EXEC @hr = sp_OAGetProperty @oPKG, 'GlobalVariables.Count', @Count OUT
IF @hr <> 0
BEGIN
    PRINT '***  Get GlobalVariable Count Failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END
PRINT 'GlobalVariables.Count: ' + CAST(@Count as varchar(4))

-- List Global Variable Names & Values
WHILE @i <= @Count
BEGIN
    PRINT 'Global Variable Index: ' + CAST(@i as varchar(4))
    -- Get Global Variable Name
    SET @cmd = 'GlobalVariables.Item(' + CAST(@i as varchar(4)) + ').Name'
    EXEC @hr = sp_OAGetProperty @oPKG, @cmd, @property OUT
    IF @hr <> 0
    BEGIN
        PRINT '*** Get Global Variable Name Failed'
        EXEC sp_displayoaerrorinfo @oPKG, @hr
        RETURN
    END
    PRINT '  GlobalVariable Name: ' + @property
    -- Get Global Variable Value
    SET @cmd = 'GlobalVariables.Item(' + CAST(@i as varchar(4)) + ').Value'
    EXEC @hr = sp_OAGetProperty @oPKG, @cmd, @property OUT
    IF @hr <> 0
    BEGIN
        PRINT '*** Get Global Variable Value Failed'
        EXEC sp_displayoaerrorinfo @oPKG, @hr
        RETURN
    END
    PRINT '  GlobalVariable Value: ' + @property
    SET @i = @i + 1
END

 

Package Execution Errors

The other big issue with executing packages is to ensure they succeeded, and when they fail, obtaining as much information as possible as to why. An easy way to see where a package failed is to check the step's ExecutionResult property, which is the method used in my spDisplayPKGErrors stored procedure. As to why, this a little more difficult. Perhaps the best method is to use the step's GetExecutionErrorInfo method, but this will fail with a Type mismatch error. This is because by reference parameters must be exactly the same type as they are declared in the method. Unfortunately the OLE stored procedures do not support strong typing. On workaround is to create a simple COM object into which you pass a reference to the step, this can then call the GetExecutionErrorInfo correctly and return the error information for you. As this is yet another object to manage in your code, I generally avoid this when using the OLE stored procedures, and rely on the package error log to supply any further information. If this is insufficient, then you can always execute the package interactively via Enterprise Manager to get the step specific errors.

Sample Scripts


All samples use the OLE Automation Return Codes and Error Information stored procedures listed in SQL Server Books Online. SQL  msoleerror.sql
Simple stored procedure to execute a package. SQL  spExecutePKG.sql
Stored procedure to execute a package, with additional parameters to set up to two global variables prior to execution. SQL  spExecutePKGGlobalVariables.sql
Supporting stored procedure used to check step ExecutionResult property. (Required by spExecutePKG and spExecutePKGGlobalVariables). SQL  spDisplayPKGErrors.sql

Script Usage

I generally create the OLE Automation Return Codes and Error Information stored procedures in master, and keep the others in my utility type database in an effort to keep the master database as clean as possible. The calling parameters are the same for spExecutePKG and spExecutePKGGlobalVariables are the same, apart from the additional global variables.

Simple package execution, using integrated security:

EXEC spExecutePKGGlobalVariables @Server='MyServer', 
  @PkgName='MyPackage', @IntSecurity=1

Package execution with a package password and SQL Server security:
N.B. The SQL login comes from the current connection via SUSER_NAME()

EXEC spExecutePKGGlobalVariables @Server='MyServer', 
  @PkgName='MyPackage', @ServerPWD='xxxx', @PkgPWD='xxxx'

Set one global variable, and execute package. Results are returned in a temporary table for further analysis:

IF OBJECT_ID('tempdb..#PkgResults') IS NOT NULL
    DROP TABLE #PkgResults

CREATE TABLE #PkgResults
(
  StepName varchar(255) NOT NULL,
  StepDescription varchar(255) NOT NULL,
  Result bit NOT NULL
)

INSERT #PkgResults 
  EXEC spExecutePKGGlobalVariables @Server='MyServer', 
    @PkgName='MyPackage', @IntSecurity=1, 
    @GlobalV_1='VariableName=VariableValue'
    
SELECT StepName, StepDescription, Result FROM #PkgResults    
StepName                       StepDescription          Result 
------------------------------ ------------------------ ------
DTSStep_DTSActiveScriptTask_1  ActX: Set Filename       0
DTSStep_DTSExecuteSQLTask_1    ExecSQL: Create Table    0
DTSStep_DTSDataPumpTask_1      Data Pump Task: Import   0

Credits

These stored procedures are variations of some written by Bill Hoghead, and distributed via Neil Pike's SQL Server FAQ.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved