SSIS reading column as NULL when it has data

user_15012638 0
Viewed 224

I am using an EXCEL Source Component where I am reading data from an EXCEL file :

enter image description here

When I check the EXCEL Source Component, I find that it is reading all the values of AssignedTo as NULL :

enter image description here

ssis excel
1 Answers

By default, the Excel driver scans the first few rows to determine the type of the column. And since the first cells are empty, so they are considered as NULL. Excel defaults to only using the first 8 rows of data to do this guessing.

Solution :

Try increasing the number of rows it uses by modifying the TypeGuessRows key in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

OR :

Try to change the type of that column to DTS_Str using DataConversion. Or by modifiying in directly in the EXCEL Source Component like below :

enter image description here

user_95177690
Related