I rather new at DTS packages, so here goes...
I have an XL spreadsheet that I am trying to load into MSSQL2000.
most of the transformations are straight column copy.
I have one transformation that does a lookup, named 'CycleNo'. here is my SQL for the lookup
WHERE (SAPCustNo = ?)
ORDER BY Cycle DESC
here is vbscript for the transform
DTSDestination("CycleNo") = DTSLookups("CycleNo").Execute( DTSSource("SAPCustNo"))
Main = DTSTransformStat_OK
when I test the transformation I get a 'data type mismatch in criteria expression' error
data type for DTSSource("SAPCustNo") is Long (this is from the tooltip you get when hovering over the fieldname of the transformations tab in the transform data task properties window.)
data type for DTSDestination("CycleNo") is BIGINT
if I hardcode DTSSource("SAPCustNo") like ...
DTSDestination("CycleNo") = DTSLookups("CycleNo").Execute(10266)
then it works
I have another transformation that is displaying the same symptoms, but If I can get this working then I can probably get the other one working.
on another note. I am also using a few global variables, but when I specify the data types in the 'global variables' tab of the Edit DTS Package dialog my changes dont seem to get saved. i.e. I have a GlobVar named SAPCustNo that is set to 'STRING' when I change it to 'INTEGER', Save/Exit and Edit the package again, the data types for all my GlobVars are set to string again. ARGHH!!
does anyone have any ideas on this?