DTS Audit Task Failure: Automation Type Not Supported in VBScript

[btturner]

Receiving error message during the exection of my DTS Audit Task:

"Variable uses an automation type not supported in VBScript"

This Audit Task worked fine at my last client site (sourced directly from the SQL Server 2000 DTS Book from WROX)

Here is the script - failing on line 55 (Note: I've carefully examined and tweaked the connection Global Variable gvsCertDMAudit to no avail!)

sSQL = sSQL & DTSGlobalVariables("gviCertDMAuditID").Value & ", "

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Dim sStepName
Dim sTaskName
Dim nLastRow
Dim oPackage
Dim oStep
Dim oTask
Dim oProperty
Dim oConn
Dim sSQL
Dim nCntr
Dim vRecordsProcessed

'!!!!!!!!!!!!!!!!!!!!!! Set Step Name !!!!!!!!!!!!!!!!!!!!
sStepName = "DTSStep_DTSExecuteSQLTask_1"

'Get Handle to Current DTS Package
Set oPackage = DTSGlobalVariables.Parent

'Find Step
For nCntr = 1 to oPackage.Steps.Count
If oPackage.Steps(nCntr).Name = sStepName Then
Exit For
End If
Next
Set oStep = oPackage.Steps(nCntr)

'Set Step
sStepName = oStep.Description & " (" & oStep.Name & ")"

'Get Handle to Task
For nCntr = 1 to oPackage.Tasks.Count
If oPackage.Tasks(nCntr).Name = oStep.TaskName Then
Exit For
End If
Next
Set oTask = oPackage.Tasks(nCntr)

'If the previous task processed records, we can access the
'property, otherwise set to NULL
vRecordsProcessed = "NULL"
For Each oProperty In oTask.Properties
If oProperty.Name = "RowsComplete" Then
vRecordsProcessed = oProperty.Value
End If
Next

'Build SQL Statement
sSQL = "INSERT INTO AuditTask (AuditID, PackageName, "
sSQL = sSQL & "TaskName, TaskStatus, TaskStartDate, "
sSQL = sSQL & "TaskEndDate, RecordsProcessed) VALUES ("
sSQL = sSQL & DTSGlobalVariables("gviCertDMAuditID").Value & ", "
sSQL = sSQL & "'" & oPackage.Name & "', "
sSQL = sSQL & "'" & sStepName & "', 'Successful', "
sSQL = sSQL & "'" & oStep.StartTime & "', '" & oStep.FinishTime
sSQL = sSQL & "'," & vRecordsProcessed & ")"

'Insert Row
Set oCONN = CreateObject("ADODB.Connection")
oConn.Open "File Name=" & _
DTSGlobalVariables("gvsCertDMAudit").Value
oConn.Execute sSQL

'Clean up
oConn.Close
Set oConn = Nothing

Main = DTSTaskExecResult_Success
End Function

 

[yksvaan]

I think because of quotes

try to set variable value into a string variable and concatenate that
sVar = DTSGlobalVariables("gviCertDMAuditID")
....
....
....
sSQL = sSQL & sVar & ", "
....

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[btturner]

problem resided in that I was attempting an INSERT into a row into a table w/ an identity column. My global variable was set to 1 - causing a failure. I reseeded the identity column table def to zero and the INSERT worked.