2

I have set up a data flow in SSIS 2005 which checks values from an excel source against a SQL Server query using a lookup and outputs the results to a flat file.

This part works fine. However I also want to capture the values from the lookup that did not match in a separate flat file. I have tried multiple things and I still cant capture the non-matching values from lookup (the flat file is always empty).

What am I doing wrong?

OPs image

Ardalan Shahgholi
  • 11,967
  • 21
  • 108
  • 144
btecndcomp
  • 21
  • 2
  • Have you set the error text file to extend rather than overwrite? If this code is within a loop then the error file may be overwritten by later loops – MiguelH Jan 29 '16 at 12:56
  • Hi mate, I have tried ticking and unticking the overwrite file box in the flat file destination. I still see no content in the text file that is produced. – btecndcomp Jan 29 '16 at 13:01
  • Presume you've put a data viewer on your error output and seen what data is being siphoned off? Also how have you set up the output text file? Do error columns link up to columns in your output text file? If so are the error text file columns defined correctly? – MiguelH Jan 29 '16 at 14:04
  • Based on your image and description, I can only conclude that the data IS being written to the flat file destination, and that that destination is not where you think it is. – Tab Alleman Jan 29 '16 at 14:39
  • The data viewer shows me three columns that are being passed to the flat file destination Priority - (this is the name of the column i want to pas through but it just contains null values), ErrorCode and ErrorColumn. The lookup itself takes in the priority from an excel column and replaces it with the associated lookup value from the lookup sql server query – btecndcomp Jan 29 '16 at 14:45
  • In that case I think the fault may lay in SSis getting data out of Excel. If Priority is numeric but the first few rows of data are blank then SSis may be treating this as alpha. Any subsequent numerics will be treated as null. Thesis is also true if the first few values of Priority are alpha and "numerics" occur later in the dataset. Search for IMEX.http://blog.concentra.co.uk/2013/05/15/why-ssis-always-gets-excel-data-types-wrong-and-how-to-fix-it/ – MiguelH Feb 02 '16 at 18:22

0 Answers0