Null values when extracting data from an excel file

Hello,

I'm trying to import some data from a spreadsheet to a database table from a package in integration services. The problem is that I see the data when I open the excel file but when I try to run my package , it doesn't insert any rows in the table and it finishes with a success status.

My excel file has some formulas to get the data from other worksheets. I added a Data Viewer and all I see is null values in every cell.

I need help...does anyone know what's wrong?

This may seem to be obviuos; but meake sure you are pointing to the right sheet. What happens when you click the preview button in the Excel source?

Rafael Salas

|||It's the same thing... null values :(|||As soon as I close the Excel workbook containing links, I start getting NULLS through the dataflow.

When the workbook is open, the linked data works fine.|||

In my case, it happens in both situations, closed or opened. But it happens when I modify something in the sheet. I inserted some rows in blank and that's when I cannot read the data in SSIS. I still have the original file and I was able to import that specific sheet (before the change) to the database.

I did the same thing in another file(I′m looping through files) and it did OK, so I don't think the problem is the blank rows...

NULL Entry in Lookup

Hi,

In my Integration Scenario I have a source field 'CompanyTypeCode' which is nullable in 'tblCompany' and There is a specific lookup Table 'tblCompanyType' to map the codes with descriptions for this field. When I want to transform this table, The NULL Entries fail in Lookup and I ran into problem. Please suggest a clean solution for bypassing the lookup when the entry in source is NULL. (If I bypass the lookup, the flow changes since there is not an extra column from the lookup component any more, therefore I must have another OLEDB Destination if the code is NULL).

Regards,
Sassan

Sassan,

You can still have the extra column if the lookup fails. What you do is in your error path from the lookup add a derived column for the description and put an "Unknown type" string in there. That way both the pipelines will have the same number of data columns.
You can then 'union all' the clean and error outputs from the lookup to a single destination.

I don't think you should bypass the lookup, but if you want to do it, how about using a conditional split before the lookup to re-route the NULL rows?

Hope that helps.