Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Lightbulb Unanswered: Using the SourceSQLStatement Property

    OKie,.. you I'm trying to set the SourceSQLStatement of a DTS task using an active script.... my code looks something like...

    Sub RunTask(myPackage, myStep, mySQL)
    Set oTasks = myPackage.Tasks
    for each oTask in oTasks
    If oTask.Name = myStep Then
    oTask.SourceSQLStatement
    oTask.Execute myPackage, nothing , Nothing, Clng(0)
    end if
    Next
    Set oTasks = nothing
    End Sub

    when I try and run it I get "Object does not support this property or method: oTask.SourceSQLStatement"

    I have looked at examples and the books online and everything suggests that this should work. If I loop through the properties of "oTask" it shows SourceSQLStatement as being one of them and I can even display what it is but when I try and change it I get the error.

    Anyone tried to do this before and succeeded?

    Cheers,
    Roko.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    it appears that this works....

    <pre>
    Sub RunTask(myPackage, myStep, mySQL)
    Set oTasks = myPackage.Tasks
    for each oTask in oTasks
    If oTask.Name = myStep Then
    oTask.Properties("SourceSQLStatement").value = mySQL'
    oTask.Execute myPackage, nothing , Nothing, Clng(0)
    end if
    Next
    Set oTasks = nothing
    End Sub
    </pre>

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    but <pre> tags to layout the code do not....

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Not every task supports this method - you need to be specific in your for..each loop which task you want to modify.

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    that's why we pass "mytask" and check that mytask and the task we have reaching in our loop are the same.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    So what tasks are you allowing to pass in as mystep ?

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    DataPumpTask2
    DataDrivenQueryTask2
    ParallelDataPumpTask
    DataPumpTask
    DataDrivenQueryTask

    I might use the same idea with some ExecuteSQL Tasks as well but in that can I'll change the code to just use the SQLStatement Property.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •