August 16, 2004 at 1:57 pm
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
August 17, 2004 at 7:23 am
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.
August 17, 2004 at 9:02 am
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.
August 18, 2004 at 2:43 pm
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