How to load more than 8000 charatcters data in sql server through SSIS.

Question

_{Thursday, March 1, 2018 4:17 AM}

My source is text file in which one column has more than 8000 characters data. Data type for the same column in destination sql server is varchar(max). In my Flat file connection manager i am using 65001 code page to adopt the other column data data so that i can't change this into 1252 code page. Could you please tell me that how to store more than 8000 characters data from text file to varchar(max) field in sql server through SSIS? 

All replies (6)

_{Thursday, March 1, 2018 5:01 AM}

DT_NTEXT is the datatype available in SSIS for handling nvarchar(max)

see

/en-us/sql/integration-services/data-flow/integration-services-data-types

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

---

_{Thursday, March 1, 2018 5:17 AM}

Thanks for reply. But my destination database is varchar(max) not nvarchar(max)

---

_{Thursday, March 1, 2018 6:47 AM}

Then use DT_TEXT.

Olaf Helper

[ Blog] [ Xing] [ MVP]

---

_{Thursday, March 1, 2018 7:08 AM}

Thanks for reply. But my destination database is varchar(max) not nvarchar(max)

If your code page is 65001  how can it be varchar(max).?

65001  is unicode UTF-8 based so destination  should be nvarchar

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

---

_{Thursday, March 1, 2018 7:12 AM}

Hi Visakh46,

That is my question?? This is existing design. One column is in varchar and remaining all columns are nvarchar. so i can't change the data type to nvarchar in SQL. So my question is that how to load text file (more than 8000) data into sql server varchar(max) field?

---

_{Friday, March 2, 2018 8:56 AM}

Hi SQL MSBI,

You need to use the SSIS datatype text stream [DT_TEXT] to fetch data from SQL Server table columns of data type varchar(MAX)

See:

How to transfer data using SSIS when database columns are defined as VARCHAR(MAX)?

Import and Export VARCHAR(MAX) data with SQL Server Integration Services (SSIS)

Regards,

Pirlo Zhang

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.