Set SQLStatement Property
-
I have a DTS package (sql server 2000 sp3 on win2k3 server). In this package, I would like to set the SQLStatement property of an ExecuteSQL Task via an ActiveX script. The code I have so far is as follows:
Function Main()
Dim oPackage
Dim oTask
Dim strSql
Dim intCounter
'Get handle to package object:
Set oPackage = DTSGlobalVariables.Parent
For intCounter = 1 To oPackage.Tasks.Count
If oPackage.Tasks(intCounter).Name = "DTSTask_DTSExecuteSQLTask_7" Then
Set oTask = oPackage.Tasks(intCounter)
If DTSGlobalVariables("gv_call_group_source").Value = "1" Then
oTask.SQLStatement = "EXEC usp_update_stage_call_detail_fact_8"
Else
oTask.SQLStatement = "EXEC usp_update_stage_7"
End If
End If
Next
Set oTask = Nothing
Set oPackage = Nothing
Main = DTSTaskExecResult_Success
End Function
On the line:
oTask.SQLStatement = "EXEC usp_update_stage_call_detail_fact_8"
I get an error stating that the object does not support the property or method "oTask.SQLStatement" yet in BOL it states that this property is configurable programatically. Obviously I am not getting a valid reference to the ExecuteSQL task object. Any ideas?
Thank you,
Michael
Michael Weiss -
I've found this same problem before, I think BOL might be out of sync with service pack changes.
There are two other methods you can try to set the SQLStatement,
1. USe the CustomTask property,
Set oTask = oPackage.Tasks("DTSTask_DTSExecuteSQLTask_7").CustomTask
2. Refer to the Properties collection of the task object,
oTask.Properties("SQLStatement").Value = blah...blah...
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Thank you, Phill...you DTS wizard you! Using oTask.Properties("SQLStatement").Value did the trick! Can't thank you enough...
Michael
Michael Weiss
Michael Weiss
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply