Monday, May 21, 2012

Avoid Null Values From Excel File with SSIS

Working with Excel is bit mess in Integration Services.When we
extracting data from Excel File sometimes struggle with Null values.

Assume that you want to get the data from selected area of an Excel
file excluding Null values. Ex:- I need to get the data only from
A8:C20 range in following excel file. I don’t want to get the first 7
rows of the excel file because it’s just a topic.

This can be done directly through the Excel Source component. There is a property named OpenRowSet in Excel Source component. After configuring the Excel Source component you can only see the sheet name.
I just change the OpenRowset property to Sheet1$A8:C20 . Now it’s only extracting data from selected area.

If you want this value can be given as a variable. It can be done easily by changing the AccessMode property .