Excel to SQL 2008 using SSIS. From Unicode text stream to text stream

Hello Experts;

First of all, I have attached the source file (Excel file), the destination file (CREATE TABLE) and other information.

Trouble converting a column in Excel that is Unicode Text Stream to SQL Text Stream, sounds simple.

In the Data Flow of SSIS I have the Source (Excel Connection Manager), the Data Conversion and Destination (OLEDB).  In Data Conversion the input column is "Comment" the Output Alias is "Copy of Comment" and Data Type is "text stream [DT-TEXT]".

When I execute the Data Flow I receive the attached errors, truncation.  If I Configure Error Output to "ignore"  mostly empty rows are inserted into the table.

How do I correct this?

Please let me know if you need anything else.
Data-Flow.jpg
Comment.xlsx
CREATE-TABLE-Comment.sql
Error-Message
wdbatesAsked:
Who is Participating?

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

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

lcohanDatabase AnalystCommented:
Excel to SQL 2008 using SSIS.  From Unicode text stream to text stream


Your create table should have the Nvarchar(MAX) in this case like:

USE ???
GO

IF EXISTS ( SELECT *
              FROM dbo.sysobjects
             WHERE id = object_id(N'dbo.Comment')
               AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )

   DROP TABLE dbo.Comment

GO

CREATE TABLE dbo.Comment ( Comment Nvarchar(MAX) NULL )
GO

And check here https://msdn.microsoft.com/en-us/library/ms141036.aspx for SSIS datatypes including conversions to make sure you chose the right one in your Data conversion task - DT_NTEXT

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
wdbatesAuthor Commented:
Thank you very much lcohan not only for the solution, but the rapid reply to my question!  
Your solution was 100% correct.
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 2008

From novice to tech pro — start learning today.