DialM4Monkey
asked on
Data Type mismatch in Transformation
Hey Folks,
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
SELECT Cycle
FROM CC_EachCycleInfo
WHERE (SAPCustNo = ?)
ORDER BY Cycle DESC
here is vbscript for the transform
Function Main()
DTSDestination("CycleNo") = DTSLookups("CycleNo").Exec ute( DTSSource("SAPCustNo"))
Main = DTSTransformStat_OK
End Function
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").Exec ute(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.
'========================= ========== ========== ========== ========== ===
PART II
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?
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
SELECT Cycle
FROM CC_EachCycleInfo
WHERE (SAPCustNo = ?)
ORDER BY Cycle DESC
here is vbscript for the transform
Function Main()
DTSDestination("CycleNo") = DTSLookups("CycleNo").Exec
Main = DTSTransformStat_OK
End Function
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").Exec
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.
'=========================
PART II
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.