Lookup with Oracle Failures
Hello all. My data source is an Excel file. In my Data Flow Task, I am using a value from the Excel data to perform a look-up using a query to an Oracle database. Currently, about half the look-up's fail. When I redirect the No Match Output to a flat file and query the Oracle table(s) using keys from this file directly (using TOAD), the data is there. The datatype of the key in the Oracle table(s) is VARCHAR2(20), the input data from the Excel file is converted to (DT_WSTR,20). This is 32-bit SSIS 2012 on Windows 7 and an Oracle 8i database. I cannot figure out why some look-up's fail while others do not when the key is there in both cases. Any ideas?
October 20th, 2012 8:46pm
this looks like data issue.... you must trim your data for lookup (use trim in both sources) ... you must check for the UPPER case also. let us know your observation.. -- Let us TRY this | My Blog :: http://quest4gen.blogspot.com/
October 20th, 2012 11:46pm
this looks like data issue.... you must trim your data for lookup (use trim in both sources) ... you must check for the UPPER case also. let us know your observation.. -- Let us TRY this | My Blog :: http://quest4gen.blogspot.com/ Yes, we're certain it's a data issue. I always normalize data before using it in a lookup (with TRIM and UPPER, when appropriate). Regenerating the source data as a CSV file solved the problem, so something was happening in Excel and only to certain values. The data appeared normal when examined in a data viewer and when redirected from the No Match to various destinations. I need more time to look into this. For now, using a CSV file is acceptable.
October 24th, 2012 6:27pm