spirose
asked on
SSIS - truncation
I am trying to bring in data from Lotus Notes db into SQL Server db. In SQL Server, There is a field xyz [varchar](8000) NULL. After declaring the DataReader Source as Notes in SSIS, I used Data Transformation to first convert unicode into unicode string; then unicode string to string. But it will not let me allow the length to exceed 4000 in SSIS in Data Conversion Transformation. As a result, I am getting this error:
"Data Conversion failed while converting column "xyz" to column "Copy of xyz". The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target page".
"Data Conversion failed while converting column "xyz" to column "Copy of xyz". The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target page".
Anthony Perkins
The maximum length for nvarchar in SQL Server 2000 is 4000. So if you are using SQL Server 2000 change to ntext, if you are using SQL Server 2005 use nvarchar(MAX).
What is the data type coming from Lotus Notes? Do you need to convert it? SSIS has the data types TEXT and NTEXT for moving text blobs which can move data for the SQL Server 2005 data types VARCHAR(max) and NVARCHAR(max), respectively.
You can look at the SSIS data type by double-clicking the path coming out of the Notes data reader component and selecting the Metadata tab.
I'm going to guess that the data is coming into SSIS as NTEXT. To convert that to 8-bit characters in a type conversion or derived column component, which I gather is what you want to do, cast/convert it to (DT_TEXT, <code page>) where <code page> is the code page is the one you are using. You can then send it to a VARCHAR(max) column in a SQL Sever 2005 table.
You can look at the SSIS data type by double-clicking the path coming out of the Notes data reader component and selecting the Metadata tab.
I'm going to guess that the data is coming into SSIS as NTEXT. To convert that to 8-bit characters in a type conversion or derived column component, which I gather is what you want to do, cast/convert it to (DT_TEXT, <code page>) where <code page> is the code page is the one you are using. You can then send it to a VARCHAR(max) column in a SQL Sever 2005 table.
ASKER
Data coming into SSIS is ntext. I used 2 Data Conversion Transformation. First to change ntext to Unicode text stream [DT_NTEXT]; then second to change DT_NTEXT to text stream DT_TEXT. However, The mapped fields in destination table in SQL serverhave datatype of [varchar] (8000) NULL. When I run the package, I get error code 20276:
Cannot create an OLE DB accessor. Verufy that the column metadata is valid.
I get this error even when I go to the Data Reader Source, Data Conversion Transformation and OLE DB destination and go to properties and set "Validate External Metadata" to False. What should I do?
Cannot create an OLE DB accessor. Verufy that the column metadata is valid.
I get this error even when I go to the Data Reader Source, Data Conversion Transformation and OLE DB destination and go to properties and set "Validate External Metadata" to False. What should I do?
You can explicitly truncate the data to 8000 chars with a cast/conversion to varchar(8000) or you can change the type of the destination column (potentially very problematic). You can't store moe tnan 8000 chars in a varchar.
ASKER
How does the former option work? Where do I explicitly truncate the data to 8000 chars with a cast/conversion to varchar(8000). Is this in Data Conversion transformation? DT_TEXT or DT_STR?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!