February 15, 2011 at 1:28 am
Hello all
I have an SSIS package which retrieves data from the DB through an OLE DB source and writes it to an XML file through an Flat File Destination.
The data comes xml formated from the stored procedure called by OLE DB source.
Everything went perfect on production servers till 2 weeks ago when I started to receive random the following error:
An OLE DB error has occurred. Error code: 0x80040E07.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "Error converting data type nvarchar to numeric.“
component "OLE DB Source" (824) failed the pre-execute phase and returned error code 0xC0202009
If I run manually the stored procedure called by OLE DB srouce, I do not get any error - all the rows are returned correctly. The error is quite strange because no conversion from nvarchar to numeric is done into my SP.
What is even more stranger is: if a make a DB backup from production, take the SSIS package from production and move them to another server - everything is working perfect, no error received WITH THE SAME DATA !
Another strange thing is: if I move the Production Virtual Machine to another server. I start the Job - everything works perfect (same Windows, configurations, data etc.)
Any help on catching this error would be helpful.
Many Thanks !
February 17, 2011 at 8:39 am
You don't have to have a conversion in your proc to get errors like this. It could be a dirty data issue. Or, worse, a mis-mapped column.
This gets really bad when importing Excel. Sometimes, a misplaced value can set the data type of the spreadsheet column to an unexpected type.
EDIT: Another thought is a malformed XML tag.
February 17, 2011 at 8:44 am
Exporting to XML shouldn't be an issue since it doesn't care about data types...unless for some reason you have them specified in the flat file properties. I tend to just force all columns to strings, since everything can always be implicitly converted to a string.
Check your output file properties for a numeric data type and try changing that.
February 21, 2011 at 12:23 am
One reason that comes into my mind is might be because your data (which need to be sent to XML destination) is ill formatted. Just for example if your query returns string value like "Steve O'Brian" or "$2,08,900" then this need to be handled specialy for XML destination as special character like ',/\ are not directly supported in XML.
Thanks
Anjan
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
February 21, 2011 at 4:50 am
Which is exactly what I was thinking, Anjan. Malformed XML has caused more "unsolvable" problems than anything else I've seen.
bogdantoporan, have you run the package in BIDS to get a visual on where failure occurs? I know it says pre-execute, but sometimes things do get highlighted while running.
Also, check the Advanced Edit (if you can) or the metadata on the source and the data flow path to verify that you really aren't transforming nvarchar to numeric.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply