Avatar of mcrmg
 asked on

SSIS Excel source

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
Visual Basic.NETMicrosoft SQL Server 2008SSIS

Avatar of undefined
Last Comment

8/22/2022 - Mon
Jim Horn

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.

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
Jim Horn

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck