Data Transformation Services (DTS) - Page 2

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.