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?
 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.