Link to home
Create AccountLog in
Avatar of mcrmg
mcrmg

asked on

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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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.
Avatar of mcrmg
mcrmg

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mcrmg

ASKER

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