Help using Activx Task to modify Lookup Query

  • I am trying to use an activex task to update the sql statement in the query of a lookup in a transformation.  I believe that my problem is with my access to the object model.  I am using the code below. 

    I get an error when I try to access the .Lookups collection.

    Any help will be greatly appreciated...


     Dim oPkg, oDataPump, sSQLStatement, sNewSQLStatement

     Dim oLookup

        Dim sCurrentLib

     Dim sHRLIB

     Dim iFromLoc

     Dim iDotLoc

     ' Build new SQL Statement


     sHRLIB = DTSGlobalVariables("INFINIUM_HR_LIBRARY").Value

     ' Get reference to the DataPump Task

     Set oPkg = DTSGlobalVariables.Parent

     Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

     Set oLookup = oDataPump.Lookups("INF JOD DESC")

     ' Assign SQL Statement to Lookup Query

     sSQLStatement  = oLookup.Query

     '*  Isolate the first library name

     iFromLoc = InStr( 1 , sSQLStatement , "FROM " , 1 )

     IF iFromLoc > 0 Then      'Found FROM string

      iFromLoc = iFromLoc + 5

      iDocLoc = InStr( iFromLoc, sSQLStatement, ".", 0) 'Find dot

      sCurrentLib = Trim( Mid( sSQLStatement, iFromLoc , iDocLoc - iFromLoc ) )

      '  Replace the hardcoded library with the global variable

      sNewSQLStatement = Replace( sSQLStatement , sCurrentLib , sHRLIB, 1, -1, 1 )

      oLookup.Query = sNewSQLStatement

     End If

     ' Clean Up

     Set oLookip = Nothing

     Set oDataPump = Nothing

     Set oPkg = Nothing

     Main = DTSTaskExecResult_Success 

  • You can simplify this by using the ActiveX task to merely set the value of another global variable to the desired query, followed by a Dynamic Properties task to set the datapump task query property using the global variable.

    Then you don't have to worry about whether you are using the object model correctly.

  • I tried the same thing myself. I ran into no issues. The only difference was I did not build the SQL string I created a static string.

    I suggest adding a MsgBox statement for debugging to determine the point the package is dying at exactly.

    Also add "Option Explicit" at the begining of the ActiveX script to make sure there are no typos in your variablesas the root cause. Looked like cleanup had a typo but that would not affect you.

    If you get past the point of building the string then MsgBox you SQL String and make sure there are no errors.

    Comment out the section around Lookups only and slowly add those pieces to be sure it is the Lookup collection itself.

  • Thanks to all for the responses.  It turns out that I had a dumb error, I had a typo in the lookup name.  The code as posted works fine.


Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply