August 4, 2009 at 1:39 pm
Hi
I am relatively new to SSIS but I have created a number of packages to load/import tables from Informix to SQL 2005 before...
But I am really lost in this specific package... One of the coulmns in informix table is 'text' data type, which I have to copy over to SQL 2005 (data type in destination table does not matter).... I have tried a lot of stuff and a lot of data types to sucessfully finish and run this package but I get the same errors (see below) every time... Need help ASAP... Would really appreciate it..
P.S. I do not have the write access to informix, so whatever we need to do, we have to do it on the SSIS/SQL 2005 side...
Errors:
Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
Error: Failed to retrieve long data for column "[column name]".
Error: There was an error with output column "[column name]" (6455) on output "OLE DB Source Output" (3752). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "[column name]" (6455)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "[column name]" (6455)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "source table name]" (3742) returned error code 0xC0209029. 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. There may be error messages posted before this with more information about the failure.
And a lot of other errors like these... Let me know if you need all of them
August 4, 2009 at 2:57 pm
Forgot to mention...
The source table has about a million records and the field with 'text' data type has paragraphs of unknown length and all kinds of special characters...
Also... this package works sucessfully as SQL 2000 DTS
PLEASE HELP!!!
August 5, 2009 at 3:27 am
If you check the meta data for the OLEDB source in your SSIS package, for both External data and Output data, what is the datatype of the column which is failing?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 5, 2009 at 11:27 am
External Data is DT_TEXT
Output Data was DT_IMAGE... I even tried changing this to DT_TEXT.... still failed
August 5, 2009 at 12:01 pm
The reference to 'long' data made me wonder whether it is, for some reason, expecting a long integer rather than a string. But based on what you have written, this should not be happening, so I am not sure.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 5, 2009 at 1:37 pm
yeah I dont think thats it...
After a lot of investigation and debugging, I've found that its failing at one specific row and only thing that I can see different about that row is that it contains the trademark (TM) sign in the text field... Could that be it? If yes, Is there a solution?
I've tried casting, replacing etc. but nothing seems to work....
Any and all responses will be appreciated...
August 5, 2009 at 2:19 pm
So is this happening in a data flow? Are you using an OLE DB Source? I had a similar problem pulling data from Teradata into SQL Server. I ended up using a CAST in my SELECT statement in the OLE DB Source. Explicitly casting the column to a data type that is compatible with the package and the SQL Server destination table may help here.
August 5, 2009 at 2:31 pm
Yes it is in a dataflow... When I try to cast it in the select statement in source, i get a DTS_E_OLEDBERROR --> "An OLE DB record is available. Source:"(null)" Hresult:0x90040E14 Description: "(null)"."
August 5, 2009 at 2:55 pm
August 5, 2009 at 3:51 pm
Yes... Right where the select statement is...
August 5, 2009 at 4:18 pm
August 6, 2009 at 8:53 am
Nevermind guys... I think I figured out a workaround... I change the conenction to ODBC instead of OLE DB and looks like it is working now.... Thanks for all your help though.. Really appreciate it..
August 6, 2009 at 9:14 am
September 28, 2018 at 1:42 pm
I know this was posted 9 year ago but I had same thing happen to me with BLOB fields from DB2 to SQL Server 2016 and this article came up. What I found is that because the server I was working on is part of a clustered, when the SQL Servers moved from node to node, the drive mappings changed. So I had set my BLOBTempStoragePath to the E: drive which was no longer on that server. This was due to me working with Visual Studio and the package running locally while I was debugging it. Once it is in SQL Server the drives will stay constant. Hope this helps the next person to search for this error.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply