SSIS Excel source

Hi,
This is the first time I use Excel Source in SSIS, I received this error:
An OLE DB record is available.  Source: "Microsoft Access Database Engine"  Hresult: 0x80004005  Description: "The Microsoft Access database engine cannot open or write to the file

Normally, I use flat file, but I need to convert the data type.  I was wondering if using Excel Source would be better and how I can get around with the error.  thanks
mcrmgAsked:
Who is Participating?

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

x
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 Developer, Architect, and AuthorCommented:
Most experienced SSIS developers avoid like the plague importing or exporting to Excel, as the drivers are confusing at best with the whole 32-bit to 64-bit Office conversion but SSIS stayed at 32-bit, plus Excel can easily be edited in such a way that would cause mapping errors when imported into SSIS.

Far better to use flat file / csv.

>but I need to convert the data type.
Explain this, as derived columns can be done regardless of source/destination.
0
mcrmgAuthor Commented:
Thanks for the info.  
Let's say I have a csv file, all the data in csv is treated as string.  What would be the best way to do this? As tables in sql have variety of data types.  

I know my way is not the best way, I setup staging tables in sql with all varchar type, after loading the data to sql, I then convert them into desired data type.  Is there a better way?  thank you
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Let's say I have a csv file, all the data in csv is treated as string.  What would be the best way to do this?
>I then convert them into desired data type.

(1, preferred)  Create an all-varchar 'staging' table that accepts the .csv values.  Then you can execute T-SQL to validate that dates are dates, numbers are numbers, anything with an acceptable range (i.e. not born yesterday) is validated.  Then either in another SSIS data pump, or better yet just a SP call, insert that data from the 'staging' table to your final production table, with all the CAST()ing you need to insert dates as dates, numbers as numbers, etc.

Another benefit of this is you can 'mark' anything that fails validation in a separate column, say validation_errors = 0 for a good row and +1 for anything bad.  Then the 'good' insert has a WHERE validation_errors = 0, and you can gracefully handle the 'bad' rows WHERE validation_errors > 0.

(2, faster and only advisable if the data source is rock solid) One SSIS data pump, used derived column / data conversion task to transform varchar's into whatever date/number is needed, then pump into final destination.
0

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
mcrmgAuthor Commented:
Thanks for the quick reply, I got them except for

Another benefit of this is you can 'mark' anything that fails validation in a separate column, say validation_errors = 0 for a good row and +1 for anything bad.  Then the 'good' insert has a WHERE validation_errors = 0, and you can gracefully handle the 'bad' rows WHERE validation_errors > 0.

I have never use that in SP, is it possible you can show me an example ot point me to some directions?  thanks
0
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
Visual Basic.NET

From novice to tech pro — start learning today.