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".
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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).
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.
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?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
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?
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spiroseAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.