DTSLookups Array is null ?
-
I'm creating a transformation script the uses a lookup table. I want to skip the record insert if the look returns no value. Is the following code correct ... I run against millions of records so don't want to get it wronge ... Thanks!
FoundKey = DTSLookups("ResetKeys").Execute(DTSSource("KeyField
if FoundKey(0) is null then
Main = DTSTransformStat_SkipRow
else
DTSDestination("A") = DTSSource("A")
Main = DTSTransformStat_OK
end if
-
In general, lookups are performance killers in DTS packages.
If I were using DTS against millions of records, I'd not use a lookup. Could you add the foundkey in your source SQL?
-
Yep. I can add a field
-
I hope you didn't take my answer as though I was insulting you. I truly hope you CAN add a field. Otherwise, you'd be like one of the "users" people like us have to support.
Anywho, what I was suggesting, in a terse, non-clear way, was to add the foundkey as a criteria for your source. I know sometimes, you don't have the ability to use criteria (text files, etc.), so what I do then is to jam all of my fields into an ActiveX script and check the source then. If the source exists in the table, I use it (or skip it, whichever you want to do)
An easy example using a hard-coded value (I hate using hard coded values, but alas, sometimes they are the "only" way.
In the transformation script (ActiveX scripting), you choose all of the source and destination fields (last two tabs) and then click the properties button to create the script, you set your limiting condition and if it's met, you pass back the skip row value (Main = DTSTransformStat_SkipRow) and DTS doesn't try to insert this row, otherwise, you set the field values and pass back the OK(Main =DTSTransformStat_OK) and DTS writes the record. The SkipRow will skip the record and record no error. There's also a SkipRowError status that will record an error if you want to see which records failed to meet your criteria.
if DTSSource("Col005") = 503 Then
Main = DTSTransformStat_SkipRow
else
DTSDestination("TipAmt") = DTSSource("Col008")
DTSDestination("PmtAmt") = DTSSource("Col007")
DTSDestination("PmtName") = DTSSource("Col006")
DTSDestination("PmtType") = DTSSource("Col005")
DTSDestination("TrxDate") = DTSSource("Col004")
DTSDestination("CheckSeq") = DTSSource("Col003")
DTSDestination("DocNum") = DTSSource("Col001")
DTSDestination("PaymentID") = DTSSource("Col002")
Main = DTSTransformStat_OK
end if
If you need to "Lookup" a value, you can go directly to the database by using the following code (modify at your leisure).
Dim oConn
Dim oRecordset
Dim strSQL
strSQL = "select testdata from tablename where criteria = " & DTSSource("Put your Sourcefieldname here")
' This is a cool little feature in that all of your connection objects are
' available to use. SWEET.
SET oConn = DTSGlobalVariables.parent.connections("Put your connection name here")
SET oRecordset = CreateObject("ADODB.Recordset")
oRecordset.open strSQL, oConn
if oRecordset.Eof then
Criteria not met, skip row
Main = DTSTransformStat_SkipRow
else
Set your field values here
DTSDestination("Your field name") = DTSSource("Your Field Name")
blah, blah, blah
Main = DTSTransformStat_OK
end if
-
Thanks. I'm too new to this to be insulted. Thanks for the reply. I think I'll try embedding this in my solution (In a fashion).
Dim oConn
Dim oRecordset
Dim strSQL
strSQL = "select testdata from tablename where criteria = " & DTSSource("Put your Sourcefieldname here")
' This is a cool little feature in that all of your connection objects are
' available to use. SWEET.
SET oConn = DTSGlobalVariables.parent.connections("Put your connection name here")
SET oRecordset = CreateObject("ADODB.Recordset")
oRecordset.open strSQL, oConn
if oRecordset.Eof then
Criteria not met, skip row
Main = DTSTransformStat_SkipRow
else
Set your field values here
DTSDestination("Your field name") = DTSSource("Your Field Name")
blah, blah, blah
Main = DTSTransformStat_OK
end if
-
So the following is executed for every record processed...does it kill my performance with the connection being made for every record. Or do I pull the connection info out of the transformation into anothe robject before the SQL Select?
' Lookup SSN
Dim oConnection
Dim oRecordset
Dim strSQL
strSQL = "select SSN from REPRICE_SSNs where SSN = " & DTSSource("SSNIn")
SET oConnection = DTSGlobalVariables.parent.connections("Connection2")
SET oRecordset = CreateObject("ADODB.Recordset")
oRecordset.open strSQL, oConnection
if oRecordset.Eof then
Main = DTSTransformStat_SkipRow
else
....
blah, blah, blah
Main = DTSTransformStat_OK
end if
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply