Solved

Data Type mismatch in Transformation

Posted on 2003-12-09
3
631 Views
Last Modified: 2007-12-19
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").Execute( 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").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.

'====================================================================
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?
0
Comment
Question by:DialM4Monkey
3 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 300 total points
ID: 9908430
long and bigint are different datatypes.  wrap your DTSDestination("CycleNo") with a clng function:


Function Main()
  DTSDestination("CycleNo") =     DTSLookups("CycleNo").Execute(clng( DTSSource("SAPCustNo")))
  Main = DTSTransformStat_OK
End Function


On your Global variable problem, right click anywhere on "white space" inside your package and choose "disconnected edit".  Search the dts properties for your global data types and change them there--you might have more luck.
Brett
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now