September 10, 2014 at 8:58 am
I am using SSIS 2008. The package retrieve data from one source DB table and insert to other DB table.
The source table have one column 'comments' and sometime it returning more than 8000 char.
To dealt with this I made some changes to my stored proc which actually pulls the data from source.
I am using Varchar(Max) now instead of Varchar(8000) for comments field and also made changes to target table EMPDetails' Comments field.
Target table EmpDetails Comments field has also Varchar (Max) datatype now instead of Varchar(8000).
I opened my SSIS package and I noticed some warning message on DataFlow task and once i clicked on the task
It prompt me to automatically fixed the errors and i clicked for Yes.
I believe this alert was because I changes the source and destination table Column DataType.
After this all the package looks fine to me and I saved the package.
Now when I am running my SSIS package it throws below exception.
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode conversion failed".
The "input "OLE DB Destination Input" (53)" failed because error code 0xC020907B occurred.
The source data will contain large string value below format
AA1;20110809;AA55;1009.0000;C15;20110809;AA4;1010.0000;
Please let me know the resolution for this issue.
September 11, 2014 at 12:43 am
What are the datatypes of the source and target columns (in the physical tables)?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 11, 2014 at 2:01 am
What data type did you pick in the source component in the data flow?
DT_TEXT?
If the source is unicode, it should be DT_NTEXT.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 11, 2014 at 9:03 am
Koen Verbeeck (9/11/2014)
What data type did you pick in the source component in the data flow?DT_TEXT?
If the source is unicode, it should be DT_NTEXT.
More to the point, if the data you are copying is unicode data copying it into a varchar(max) column won't work unless the unicode data can be expressed in the default codepage associated with the collation for that column, which I find is usually not the case. so perhaps you should try NVARCHAR(MAX) instead of VARCHAR(MAX).
Tom
September 26, 2014 at 12:07 am
All.
Apologies for delay response.
Into SSIS the data type for these column is
text stream [DT_TEXT]
The below string I am retrieving sand inserting to database table.
AA1;20110809;AA55;1009.0000;C15;20110809;AA4;1010.0000;
Is this Unicode string? Please advise what changes I should apply to resolve the issue?
The database stored procedure and table has VarChar(Max) data type to process the above string.
September 26, 2014 at 12:28 am
Karan_W (9/26/2014)
All.Apologies for delay response.
Into SSIS the data type for these column is
text stream [DT_TEXT]
The below string I am retrieving sand inserting to database table.
AA1;20110809;AA55;1009.0000;C15;20110809;AA4;1010.0000;
Is this Unicode string? Please advise what changes I should apply to resolve the issue?
The database stored procedure and table has VarChar(Max) data type to process the above string.
Although it does not seem to contain any characters which cannot be represented as VARCHAR(), we cannot tell you whether the string is Unicode or not - that depends on what is storing the string.
As both source and target columns are VARCHAR() and SSIS is using DT_TEXT, you should not get any Unicode warnings for this data.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 26, 2014 at 3:25 am
Thanks Phill.
Any suggestion what I should try now to resole this issue?
September 26, 2014 at 3:31 am
Karan_W (9/26/2014)
Thanks Phill.Any suggestion what I should try now to resole this issue?
Source is not Unicode, target is not Unicode.
So something in the package must be trying to use Unicode.
Follow the data path through the package using advanced editor etc to find out where this is happening.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply