Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bogdantop
Starting Member
3 Posts |
 Posted - 2011-02-15 : 03:52:05
|
| Hello allI 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:[color=#FF0000]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[/color]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 ! |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-02-15 : 05:51:54
|
| You may be looking at a collation issue or a LOCAL settings issues.Copy/Amend the package to have minimal columns and re-run and repeat by adding one column until you identify which column is failing. then inspect the column to see if you have unusual data in it. focus on formatting values that may be unexpected (ie east-european format numbers going into a US format field - sometimes they switch the "," and decimal point) |
 |
|
|
bogdantop
Starting Member
3 Posts |
Posted - 2011-02-15 : 06:04:54
|
| Hi AndrewThe OLE DB soruce / Stored Procedure is returning one single column - which is a text one and no conversions are made afterwards. The column is written directly into an xml file.Example of output column: "<Row>XXXYYY</Row>" |
|
|
|
ZZartin
Starting Member
30 Posts |
Posted - 2011-02-15 : 12:27:36
|
| How are is the job being run when it's being tested in your test environment vs when it's run on the server? If you have the job saved on the server in production and it's being run through the SQL server agent maybe the meta data on the saved job got corrupted and it's trying to export that column as in number instead of as text(which would throw the error you're seeing). |
|
|
|
bogdantop
Starting Member
3 Posts |
Posted - 2011-02-16 : 02:03:59
|
| Both on test and production environment the job is run through SQL Server agent. |
|
|
|
|
|
|
failed the pre-execute phase