How to handle Excel column that contain null value while loading in SQL server using SSIS ?

Hello ,
It is my first post in this group.
I am facing issue while loading data from excel to sql server using SSIS.
When excel file column contain null value  then it throw error.
and another issue that it  column contain mix data type like string and number it throw error.
so how to resolve that.
I don't have permission to change guessing row. so what is alternate way
meera desaiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>It is my first post in this group.
Welcome to EE.   We hope you like it here.

>When excel file column contain null value  then it throw error.
It would greatly help if you could tell us the error, and not just state 'an error occured'.
Also, you'll want to check how the SSIS Excel Source mapped the Excel doc, as it will often infer the data type (numeric, date, char) from the first value, and if that's a NULL it may have assumed incorrectly and gave it a data type that caused the error when processing subsequent rows.

>it  column contain mix data type like string and number it throw error.
Then you'll have to change the data type to string, which will accept all values.  A column can only have a single data type.

>I don't have permission to change guessing row.
Not sure what you mean by 'guessing row'.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Welcome to Experts Exchange!

Try using a staging table with all the fields defined as varchar or nvarchar of easily-adequate length - import raw data to that and then run a stored procedure to move the cleaned data to the eventual destination table.

Where I work now we get data delimited by pipes (" | ") that is so bad that we import each line into one varchar(max) field and then count the separators! Only if we get the right number of separators (one less than the number of fields) do we even try to parse it into fields and then values!


Vikas GargAssociate Principal EngineerCommented:

About this
"I don't have permission to change guessing row"

In excel you have no option for guessing row like we have in flat file.

But we can left click on excel source and show advance editor
where in the Input/Output properties you can change the data type of any column.
Vitor MontalvãoMSSQL Senior EngineerCommented:
meera desai, I know is your first post here in EE but it would be good if you could give some feedback to the Experts that already spent their time trying to help you.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.