Receive "Acquiring a connection requires a valid Task name" when execute a DTS

26 Nov 2007 3:44 AM

Dobby Cai

Hi All,
I'm creating "DTSExecuteSQLTask" Task in DDQ ActiveX script code. Then
I'm trying to execute it from within this code with command:
oCustomerTask.execute oPkg, Nothing, Nothing,
DTSTaskExecResult_Success

but I receive error message:
"Acquiring a connection requires a valid Task name".

What's the matter?

Following is my activx script code
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
    Dim oPkg, startDate, oTask, oConnection, oCustomerTask
    Dim oResult
    set oPkg = DTSGlobalVariables.parent
    startDate = DTSGlobalVariables("startDate")
       Set oConnection = oPkg.Connections("db_conn2")
    Set oTask = oPkg.tasks.new("DTSExecuteSQLTask")
    oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
    Set oCustomerTask = oTask.CustomTask
    oCustomerTask.Name = "DTSTask_DTSExecuteSQLTask_1"
    oCustomerTask.description = "test task"
    oCustomerTask.ConnectionID=oConnection.id
    oCustomerTask.CommandTimeout=0
    oCustomerTask.OutputAsRecordset = False
    oCustomerTask.SQLStatement = "delete from tmp_table"
    oCustomerTask.execute oPkg, Nothing, Nothing,
DTSTaskExecResult_Success

    Set oCustomerTask = Nothing
    set oTask=Nothing
    Set oConnection=Nothing
    set oPkg=nothing
    Main = DTSTaskExecResult_Success
End Function

What's wrong?
Thanks in advance.

26 Nov 2007 4:14 AM

Dobby Cai

I have found the resolution, after you create the task, you need
create a step object whcih will refers this new created task, you also
need add the task to current package. And then execute the step, you
can get the result. After the step complets, you need to remove the
task from package.

This solution works for me, but I do not know this is the best
solution or not. If anybody has more better solution, please post it.

Thanks a lot.

Following is the code which can run on my SQL Server 2000 Enterprise
Edition
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
    Dim oPkg, startDate, oTask, oConnection, oCustomerTask
    Dim oResult
    set oPkg = DTSGlobalVariables.parent
    startDate = DTSGlobalVariables("startDate")

Set oConnection = oPkg.Connections("db_conn2")

'create task itself
    Set oTask = oPkg.tasks.new("DTSExecuteSQLTask")
    oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
    Set oCustomerTask = oTask.CustomTask
    oCustomerTask.Name = "DTSTask_DTSExecuteSQLTask_1"
    oCustomerTask.description = "test task"
    oCustomerTask.ConnectionID=oConnection.id
    oCustomerTask.CommandTimeout=0
    oCustomerTask.OutputAsRecordset = False
    oCustomerTask.SQLStatement = "select * from test_table"
    oPkg.tasks.add oTask

    'associate step with a task
    Dim oStep
Set oStep = oPkg.Steps.New
    oStep.Name = "DTSStep_DTSExecuteSQLTask_1"
    oStep.Description = "Execute SQL Task: undefined"
    oStep.ExecutionStatus = 1
    oStep.TaskName = "DTSTask_DTSExecuteSQLTask_1"
    oStep.CommitSuccess = False
    oStep.RollbackFailure = False
    oStep.ScriptLanguage = "VBScript"
    oStep.AddGlobalVariables = True
    oStep.RelativePriority = 3
    oStep.CloseConnection = False
    oStep.ExecuteInMainThread = False
    oStep.IsPackageDSORowset = False
    oStep.JoinTransactionIfPresent = False
    oStep.DisableStep = False
    oStep.FailPackageOnError = False

    'run step
    oStep.execute

    'remove the added customer task
    Dim index
    For index=1 To oPkg.tasks.count
       If (oPkg.tasks.item(index).name = "DTSTask_DTSExecuteSQLTask_1")
Then
           oPkg.tasks.remove index
           Exit For
       End If
Next

    Set oStep = Nothing
    Set oCustomerTask = Nothing
    set oTask=Nothing
    Set oConnection=Nothing
    set oPkg=nothing
    Main = DTSTaskExecResult_Success
End Function