Import problem with varchar(max) field

I'm trying to import some Assessor data from a text file into a table and the field for Legal Description (column 2 in the source text file) in the table is of data type varchar(max) because some of the data goes over the 8K size. I get an error on the first row of importing that refers to column 2 (see 'Initial errors' below). I read the related post and changed the size of input column 2 to 8000 and got this error. Finally I set the size of the of input column 2 to 4000 and it ran. So I'm thinking there is a limit on the size of varchar data that can be imported. Just want to clarify what that limit is and how I might go about importing this data.

Thanks, John

Error with input column 2 set to size of 8000:

Setting Destination Connection (Error)

Messages

Error 0xc0204016: DTS.Pipeline: The "output column "Column 2" (388)" has a length that is not valid. The length must be between 0 and 4000.
(SQL Server Import and Export Wizard)

Exception from HRESULT: 0xC0204016 (Microsoft.SqlServer.DTSPipelineWrap)

Initial errors:

Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Column 2" (18)" failed because truncation occurred, and the truncation row disposition on "output column "Column 2" (18)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "\\Scux00\assrdumps\SQLServerDB\exportsql.txt" on data row 1.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - exportsql_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

There is a limit on the size of unicode character strings at 4000. Non-unicode character strings have a limit of 8000. Ensure that you don't have type conversions between varchar and nvarchar.|||

I'm trying the SQL Server Import and Export Wizard. I've set the DataType to most of the fields as 'string[DT_STR]' and the Unicode box is unchecked, but when I look at column mappings after clicking 'Edit Mappings', all the columns say they're type nvarchar on the screen and it doesn't appear to be something I can change. If I highlight a column on that screen it shows the setting that I set in the Advanced screen for the text file, ie .. Column 2 string[DT_STR](4000). Since I'm getting the limit of 4k it must be because of this. I'm not sure how to modify it. I'll save the package as a file and open it up in BID Studio ... john

Import fails on varchar field

Hello,
I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
file into SQL Server 2005. This file is a commercial marketing
information database that contains about 76,000 rows. I converted the
xls files provided into text files before the import.
However, I keep getting this error when trying to import:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
conversion for column "CBSAName" returned status value 4 and status
text "Text was truncated or one or more characters had no match in the
target code page.".
(SQL Server Import and Export Wizard)
I looked at the text file and noticed that strings are delmited by
double-quotes, so I changed all the double-quotes to single quotes.
However the error persists.
I'm not sure why it's complaining about the field, as it is just a
simple text field delimited by single quotes.
Any ideas? I seem to have lots of problems with import in 2005.
Thanks!
Hi
It looks like you text has some data that can not be imported within the
text field. You may want to look how far the import got and then look at the
data on the row that failed.
You can import directly from the excel file without converting the data.
John
"tootsuite@.gmail.com" wrote:

> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" returned status value 4 and status
> text "Text was truncated or one or more characters had no match in the
> target code page.".
> (SQL Server Import and Export Wizard)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>
|||Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
between the two. If it is a VARCHAR column, what is its specified size? Is
it possible that there is data in the tab-delimited file that exceeds the
defined size of the column in the table? If it is tab-delimited, why are
the values enclosed in quotes (single or double)?
<tootsuite@.gmail.com> wrote in message
news:1166647034.331993.313170@.n67g2000cwd.googlegr oups.com...
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" returned status value 4 and status
> text "Text was truncated or one or more characters had no match in the
> target code page.".
> (SQL Server Import and Export Wizard)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>
|||Yeah, it was the single quotes - thanks
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
> between the two. If it is a VARCHAR column, what is its specified size? Is
> it possible that there is data in the tab-delimited file that exceeds the
> defined size of the column in the table? If it is tab-delimited, why are
> the values enclosed in quotes (single or double)?
>
>
> <tootsuite@.gmail.com> wrote in message
> news:1166647034.331993.313170@.n67g2000cwd.googlegr oups.com...
|||I know you can import xls directly however if you've ever had your
"text" converted to float in the process sometimes it's just easier to
go with the txt format so you don't have to map fields
John Bell wrote:[vbcol=seagreen]
> Hi
> It looks like you text has some data that can not be imported within the
> text field. You may want to look how far the import got and then look at the
> data on the row that failed.
> You can import directly from the excel file without converting the data.
> John
>
> "tootsuite@.gmail.com" wrote:
|||Hi
I assume you are doing this manually and not wanting to automate it?
John
"tootsuite@.gmail.com" wrote:

> I know you can import xls directly however if you've ever had your
> "text" converted to float in the process sometimes it's just easier to
> go with the txt format so you don't have to map fields
> John Bell wrote:
>
|||This is a one time thing, but yes I'd like to know how to automate an
import!
John Bell wrote:[vbcol=seagreen]
> Hi
> I assume you are doing this manually and not wanting to automate it?
> John
> "tootsuite@.gmail.com" wrote:
|||Hi
I assume you are using the Import/Export Wizard, in which case on the last
step you can save the task as a SSIS package which can then be scheduled or
editted in BI Studio. Unless you import directly from the excel spreadsheet
you are not going to fully automate this process, you have the ability to
specify exactly what you need regarding column settings on the advanced
screen of the wizard.
John
"tootsuite@.gmail.com" wrote:

> This is a one time thing, but yes I'd like to know how to automate an
> import!
> John Bell wrote:
>

Import fails on varchar field

Hello,
I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
file into SQL Server 2005. This file is a commercial marketing
information database that contains about 76,000 rows. I converted the
xls files provided into text files before the import.
However, I keep getting this error when trying to import:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
conversion for column "CBSAName" returned status value 4 and status
text "Text was truncated or one or more characters had no match in the
target code page.".
(SQL Server Import and Export Wizard)
I looked at the text file and noticed that strings are delmited by
double-quotes, so I changed all the double-quotes to single quotes.
However the error persists.
I'm not sure why it's complaining about the field, as it is just a
simple text field delimited by single quotes.
Any ideas? I seem to have lots of problems with import in 2005.
Thanks!Hi
It looks like you text has some data that can not be imported within the
text field. You may want to look how far the import got and then look at the
data on the row that failed.
You can import directly from the excel file without converting the data.
John
"tootsuite@.gmail.com" wrote:

> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" returned status value 4 and status
> text "Text was truncated or one or more characters had no match in the
> target code page.".
> (SQL Server Import and Export Wizard)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>|||Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
between the two. If it is a VARCHAR column, what is its specified size? Is
it possible that there is data in the tab-delimited file that exceeds the
defined size of the column in the table? If it is tab-delimited, why are
the values enclosed in quotes (single or double)?
<tootsuite@.gmail.com> wrote in message
news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" returned status value 4 and status
> text "Text was truncated or one or more characters had no match in the
> target code page.".
> (SQL Server Import and Export Wizard)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>|||Yeah, it was the single quotes - thanks
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> Is it a TEXT column or a VARCHAR column? Your narrative seems to alternat
e
> between the two. If it is a VARCHAR column, what is its specified size?
Is
> it possible that there is data in the tab-delimited file that exceeds the
> defined size of the column in the table? If it is tab-delimited, why are
> the values enclosed in quotes (single or double)?
>
>
> <tootsuite@.gmail.com> wrote in message
> news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...|||I know you can import xls directly however if you've ever had your
"text" converted to float in the process sometimes it's just easier to
go with the txt format so you don't have to map fields
John Bell wrote:[vbcol=seagreen]
> Hi
> It looks like you text has some data that can not be imported within the
> text field. You may want to look how far the import got and then look at t
he
> data on the row that failed.
> You can import directly from the excel file without converting the data.
> John
>
> "tootsuite@.gmail.com" wrote:
>|||Hi
I assume you are doing this manually and not wanting to automate it?
John
"tootsuite@.gmail.com" wrote:

> I know you can import xls directly however if you've ever had your
> "text" converted to float in the process sometimes it's just easier to
> go with the txt format so you don't have to map fields
> John Bell wrote:
>|||This is a one time thing, but yes I'd like to know how to automate an
import!
John Bell wrote:[vbcol=seagreen]
> Hi
> I assume you are doing this manually and not wanting to automate it?
> John
> "tootsuite@.gmail.com" wrote:
>|||Hi
I assume you are using the Import/Export Wizard, in which case on the last
step you can save the task as a SSIS package which can then be scheduled or
editted in BI Studio. Unless you import directly from the excel spreadsheet
you are not going to fully automate this process, you have the ability to
specify exactly what you need regarding column settings on the advanced
screen of the wizard.
John
"tootsuite@.gmail.com" wrote:

> This is a one time thing, but yes I'd like to know how to automate an
> import!
> John Bell wrote:
>

Import fails on varchar field

Hello,
I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
file into SQL Server 2005. This file is a commercial marketing
information database that contains about 76,000 rows. I converted the
xls files provided into text files before the import.
However, I keep getting this error when trying to import:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
conversion for column "CBSAName" returned status value 4 and status
text "Text was truncated or one or more characters had no match in the
target code page.".
(SQL Server Import and Export Wizard)
I looked at the text file and noticed that strings are delmited by
double-quotes, so I changed all the double-quotes to single quotes.
However the error persists.
I'm not sure why it's complaining about the field, as it is just a
simple text field delimited by single quotes.
Any ideas? I seem to have lots of problems with import in 2005.
Thanks!Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
between the two. If it is a VARCHAR column, what is its specified size? Is
it possible that there is data in the tab-delimited file that exceeds the
defined size of the column in the table? If it is tab-delimited, why are
the values enclosed in quotes (single or double)?
<tootsuite@.gmail.com> wrote in message
news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" returned status value 4 and status
> text "Text was truncated or one or more characters had no match in the
> target code page.".
> (SQL Server Import and Export Wizard)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>|||Yeah, it was the single quotes - thanks
Aaron Bertrand [SQL Server MVP] wrote:
> Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
> between the two. If it is a VARCHAR column, what is its specified size? Is
> it possible that there is data in the tab-delimited file that exceeds the
> defined size of the column in the table? If it is tab-delimited, why are
> the values enclosed in quotes (single or double)?
>
>
> <tootsuite@.gmail.com> wrote in message
> news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...
> > Hello,
> >
> > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > file into SQL Server 2005. This file is a commercial marketing
> > information database that contains about 76,000 rows. I converted the
> > xls files provided into text files before the import.
> >
> > However, I keep getting this error when trying to import:
> >
> > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > conversion for column "CBSAName" returned status value 4 and status
> > text "Text was truncated or one or more characters had no match in the
> > target code page.".
> > (SQL Server Import and Export Wizard)
> >
> > I looked at the text file and noticed that strings are delmited by
> > double-quotes, so I changed all the double-quotes to single quotes.
> > However the error persists.
> >
> > I'm not sure why it's complaining about the field, as it is just a
> > simple text field delimited by single quotes.
> >
> > Any ideas? I seem to have lots of problems with import in 2005.
> >
> > Thanks!
> >|||I know you can import xls directly however if you've ever had your
"text" converted to float in the process sometimes it's just easier to
go with the txt format so you don't have to map fields
John Bell wrote:
> Hi
> It looks like you text has some data that can not be imported within the
> text field. You may want to look how far the import got and then look at the
> data on the row that failed.
> You can import directly from the excel file without converting the data.
> John
>
> "tootsuite@.gmail.com" wrote:
> > Hello,
> >
> > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > file into SQL Server 2005. This file is a commercial marketing
> > information database that contains about 76,000 rows. I converted the
> > xls files provided into text files before the import.
> >
> > However, I keep getting this error when trying to import:
> >
> > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > conversion for column "CBSAName" returned status value 4 and status
> > text "Text was truncated or one or more characters had no match in the
> > target code page.".
> > (SQL Server Import and Export Wizard)
> >
> > I looked at the text file and noticed that strings are delmited by
> > double-quotes, so I changed all the double-quotes to single quotes.
> > However the error persists.
> >
> > I'm not sure why it's complaining about the field, as it is just a
> > simple text field delimited by single quotes.
> >
> > Any ideas? I seem to have lots of problems with import in 2005.
> >
> > Thanks!
> >
> >|||This is a one time thing, but yes I'd like to know how to automate an
import!
John Bell wrote:
> Hi
> I assume you are doing this manually and not wanting to automate it?
> John
> "tootsuite@.gmail.com" wrote:
> > I know you can import xls directly however if you've ever had your
> > "text" converted to float in the process sometimes it's just easier to
> > go with the txt format so you don't have to map fields
> >
> > John Bell wrote:
> > > Hi
> > >
> > > It looks like you text has some data that can not be imported within the
> > > text field. You may want to look how far the import got and then look at the
> > > data on the row that failed.
> > >
> > > You can import directly from the excel file without converting the data.
> > >
> > > John
> > >
> > >
> > >
> > > "tootsuite@.gmail.com" wrote:
> > >
> > > > Hello,
> > > >
> > > > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > > > file into SQL Server 2005. This file is a commercial marketing
> > > > information database that contains about 76,000 rows. I converted the
> > > > xls files provided into text files before the import.
> > > >
> > > > However, I keep getting this error when trying to import:
> > > >
> > > > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > > > conversion for column "CBSAName" returned status value 4 and status
> > > > text "Text was truncated or one or more characters had no match in the
> > > > target code page.".
> > > > (SQL Server Import and Export Wizard)
> > > >
> > > > I looked at the text file and noticed that strings are delmited by
> > > > double-quotes, so I changed all the double-quotes to single quotes.
> > > > However the error persists.
> > > >
> > > > I'm not sure why it's complaining about the field, as it is just a
> > > > simple text field delimited by single quotes.
> > > >
> > > > Any ideas? I seem to have lots of problems with import in 2005.
> > > >
> > > > Thanks!
> > > >
> > > >
> >
> >|||Hi
I assume you are using the Import/Export Wizard, in which case on the last
step you can save the task as a SSIS package which can then be scheduled or
editted in BI Studio. Unless you import directly from the excel spreadsheet
you are not going to fully automate this process, you have the ability to
specify exactly what you need regarding column settings on the advanced
screen of the wizard.
John
"tootsuite@.gmail.com" wrote:
> This is a one time thing, but yes I'd like to know how to automate an
> import!
> John Bell wrote:
> > Hi
> >
> > I assume you are doing this manually and not wanting to automate it?
> >
> > John
> >
> > "tootsuite@.gmail.com" wrote:
> >
> > > I know you can import xls directly however if you've ever had your
> > > "text" converted to float in the process sometimes it's just easier to
> > > go with the txt format so you don't have to map fields
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > It looks like you text has some data that can not be imported within the
> > > > text field. You may want to look how far the import got and then look at the
> > > > data on the row that failed.
> > > >
> > > > You can import directly from the excel file without converting the data.
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > > "tootsuite@.gmail.com" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > > > > file into SQL Server 2005. This file is a commercial marketing
> > > > > information database that contains about 76,000 rows. I converted the
> > > > > xls files provided into text files before the import.
> > > > >
> > > > > However, I keep getting this error when trying to import:
> > > > >
> > > > > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > > > > conversion for column "CBSAName" returned status value 4 and status
> > > > > text "Text was truncated or one or more characters had no match in the
> > > > > target code page.".
> > > > > (SQL Server Import and Export Wizard)
> > > > >
> > > > > I looked at the text file and noticed that strings are delmited by
> > > > > double-quotes, so I changed all the double-quotes to single quotes.
> > > > > However the error persists.
> > > > >
> > > > > I'm not sure why it's complaining about the field, as it is just a
> > > > > simple text field delimited by single quotes.
> > > > >
> > > > > Any ideas? I seem to have lots of problems with import in 2005.
> > > > >
> > > > > Thanks!
> > > > >
> > > > >
> > >
> > >
>