July 7, 2009 at 9:29 am
We have a SSIS package that is failing on the Task Flow. The source fails but destination turns green and populates with data. The error message (below) appears on the Source OLE DB task and does not provide any details as to the column or source of the error except for a conversion issue. There are approximately 50 columns that are extracted.
I've tried going column by column but can not find any issues. This package has been in production for about 2 years and this is the first time we are getting this error.
Error Message Returned:
[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conversion failed when converting the nvarchar value '1.5' to data type int.".
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.
July 7, 2009 at 9:57 am
It is pretty clear:
[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conversion failed when converting the nvarchar value '1.5' to data type int.".
Change your select query on the data source to explicitly do the conversion.. Like adding:
replacementcolumnname = CONVERT( int, columnname )
to the end of your query and then using the new column and not using the old column at all..
The reason one successd and the olther fails is tied to the batching parameters you've set (or by default), the records that have been passed to the destination have been written so that part IS successful, but the upper portion of the dataflow dies and does not pass anymore records down, so it fails..
CEWII
July 7, 2009 at 11:02 am
Thanks Elliot. It is pretty clear to the issue but it doesn't provide which column failed. There are about 50 columns in the extraction and all the data coming from our source table doesn't allow for fractions (data type in Oracle is numeric(4,0) or varchar). Therefore, my best guess is that it is coming from a varchar field in Oracle that is being incorrectly mapped to int in our extraction.
Is there anyway to get the column that is failing or more detailed explanation?
July 7, 2009 at 11:05 am
Also, I get the exact same error when I run the openquery in a query in Management Studio but again no row failure or column that it failed on. There are about 400,000 records and 50 columns in our extraction.
Sorry to edit again, but I believe this is significant because when just viewing the data in Management Studio I get the same warning but all records are returned from the query.
July 7, 2009 at 11:26 am
That all sounds kind of interesting.. If the error was limited to SSIS I ight have bought mismapped, but you get it at the end of every OLEDB destination..
I would pare the query is SSMS down to 5 groups of 10 columns and see which groups return the error, on the groups that return the error, take is down to 2 groups of 5 and then singles till you can isolate the culprit.
My thought is that there is some kind of problem in the OLEDB provider where it is not mapping correctly in some cases, if the field is defined as a number (numeric, int, etc.) it shouldn't be doing conversions from varchar to int..
CEWII
July 7, 2009 at 11:29 am
Thanks Elliot. I'm going to break it down to 10 column increments like you suggested and see where it is failing.
July 7, 2009 at 11:40 am
I'd had a similar problem in the past (couldn't figure out what column was the culprit) and since the messages wouldn't tell me this is what I had to do.
I also had a second thought, but I don't know if you will get the row or not. Add a counter to the dataflow, choose any variable you want to hold the count, it isn't important for what I'm suggesting.. Then setup the error output from the source and feed it to the counter, add a dataviewer to the dataflow between the source and the counter and see if the row comes through..
CEWII
July 7, 2009 at 1:03 pm
The problem was the pass-through (openquery) query was pulling in a numeric(18,3) field and casting it as a nvarchar. I had a line of code in my select statement in T-SQL to do some indicators:
CASE WHEN OrderQty > 0 then 1 else 0 end OpenIndicator
This was failing because it was trying to convert the nvarchar field to an int to do the comparison. It still failed even when I changed it to:
CASE WHEN OrderQty > 0.0 then 1 else 0 end OpenIndicator
To get it working properly, I had to explicitly cast it as numeric for it to work again.
CASE WHEN cast(OrderQty as numeric(18,3) > 0.0 then 1 else 0 end OpenIndicator
Thanks for the help!
July 7, 2009 at 1:22 pm
You bet, some of the errors out of SSIS are so opaque it is really hard to see what the problem is. I'm glad you figured it out..
CEWII
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply