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
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
Last Comment
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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
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
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.