DTS ActiveX Script called from SQL Stored Procedure

I have a DTS Package that I call from a Stored Proecure. The stored proecdure passes a department code parameter (3 character text string) to the DTS Package. The DTS Package doesn't seem to see or properly recognize my parameter.

------------------------------------------------------------------------------------------------------------
-- Here is my Stored Procedure that passes the one variable
------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[DTS_DeptTestPackage]
      @DeptName VARCHAR(8)
AS
exec master..xp_cmdshell 'DTSRun /S "(local)" /N "DeptTestPackage" /G "{0D58F060-56E3-4DE0-8D8A-C51F60EC5EA8}" /A "strDeptName":"8"=@DeptName /W "0" /E '
------------------------------------------------------------------------------------------------------------
When I execute this stored procedure I am prompted for the parameter, which I provide (its RMA). The query result returns following...
DTSRun:  Loading...
DTSRun:  Executing...
DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart:  DTSStep_DTSActiveScriptTask_1
DTSRun OnError:  DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B)
   Error string:  The task reported failure on execution.
   Error source:  Microsoft Data Transformation Services (DTS) Package
   Help file:  sqldts80.hlp
   Help context:  1100

------------------------------------------------------------------------------------------------------------
-- Here is what I've got going on in my DTS Package...
------------------------------------------------------------------------------------------------------------
I've got a Global Variable defined on the Global Variables tab..
Name=strDeptName  Type=String  Value=<not displayable> (default value.. nothing... I haven't set it to anything)

-- Step 1
-- Execute SQL Task -- 1 imput paramter defined as strDeptName from the Global Variables list
DELETE
FROM Users
WHERE DeptName = ?

-- Step 2
-- ActiveX Script Task
Function Main()
     If DTSGlobalVariables("strDeptName") ="RMA" Then
          Main = DTSTaskExecResult_Success
     Else
          Main = DTSTaskExecResult_Failure
     End If      
End Function

-- Step 3
-- This is a Transform Data Task, but it doesn't seem that execution gets this far, so I'll refrain from posting its clutter. It has no parameters. If I 'Execute Step' from the server's Enterprise Manager, this runs fine. Trying to 'Execute Step' for Steps 1 and 2 fail because it doesn't know the parameter at that time and doesn't prompt me for one.


The issue as best as I can tell is that it doesn't seem to recognize the parameter that my Stored Procedure is attempting to pass. Please help.Start Free Trial