CODEWITHSUNDEEP
sql-serveroracle-databasessis
Amanda
Amanda

Reputation: 31

SSIS lookup with numeric value not working

I am new to using ssis and am on my third package. We are taking data from Oracle into Sql Server. On my oracle table, the unique key is called recnum and is numeric(12,0). In this particular package, I am trying to take the record from oracle, lookup in a sql server table to see if that unique key is found, and if not add the record to the sql server table. My issue is it wouldn't find a match. After much testing, I came up with the following method that works. But I don't understand why I had to do this.

How I currently have it working: I get the data from oracle. In my next step, I added a derived column that uses the oracle column. (The expression is just that field, no other formatting.) Then in the lookup I use the derived column instead of the column from Oracle.

We had already done this on another table where the unique key was numeric(8,0) and it worked ok without needing a derived column.

Upvotes: 1

Views: 1352

Answers (1)

SinisterPenguin
SinisterPenguin

Reputation: 1618

SSIS is very fussy about data types, lookups only work nicely if data types match.

Double click on the Data Path lines between Data Flow objects to check data types. I use Data Conversion tasks or CAST statements to force matching data types when I use lookups.

Hope this helps.

Upvotes: 1

Related Questions