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".
spiroseAsked:
Who is Participating?
 
Megan BrooksSQL Server ConsultantCommented:
In a derived column. Derived column uses a type cast:
(DT_STR, <length>, <code page>)[ColumnName]
I have had problems trying to do an "in-place" transformation. Instead, I like to give the original column a special name in the data source. For example if the original column is ColumnA I might rename it as ColumnA_NT in the data source, using the Columns pane of the data source component (the original name is typically listed under "External Columns" and you can specify a new name for it in the data flow under "Output Columns").
The derived column expression then becomes something like
(DT_STR, 8000, 1252)[ColumnA_NT]
if you are using code page 1252. You would also select "Add as new column" and you could specify the data desination column name in the Derived Column Name column, or you could use a different suffix such as ColumnA_V. I like to use the destination column name, so that it matches automatically.
0
 
Anthony PerkinsCommented:
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).
0
 
Megan BrooksSQL Server ConsultantCommented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
spiroseAuthor Commented:
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?
0
 
Megan BrooksSQL Server ConsultantCommented:
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.
0
 
spiroseAuthor Commented:
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?
0
 
spiroseAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.