SQLTeam.com | Weblogs | Forums

Text File Fails to Import to Table

Hi Experts,
I have a txt file, trying to import to a SQL table. In the table ALL cols are defined as varchar(max) (ugh)
The column it fails on has data type DT_STR (in the Data Flow task)
Things I tried:

  1. I set the ValidateExternalMetadata property to False - no go
  2. I changed col 63 to DT_TEXT - still failed

: Data conversion failed. The data conversion for column "Column 63" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Any ideas, please? Thanks

1 Like

please post sample txt file and target table schema definition

how many columns in txt file and how many in target table?

1 Like

hi

please see if this link helps you !!

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ebca7af7-b560-4218-81e6-9aab2e7c28d4/data-truncated-in-ssis-package?forum=sqlintegrationservices

1 Like

Thanks, yosiasz.
The first few columns if the text file

... and the Table I'm importing into: has 167 columns - all are defined as varchar(max) nullable

So I've been tweaking the Data Type in the Flat File Connection:
When all columns are set to DT_STR and width 200, the package fails on row 655 column 63
So I changed all columns to DT_TEXT:

The package still failson ro 655 column 63 with "The data conversion for column "Column 63" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page"
Something very strange (and probably easy to fix) is going on.

the issue could be that you are using | as delimiter but there could actually be data in there with | ie Ch|cken

Show us row 655 column 63

Thanks When I open the file in Notepad++ Row 655 column 63 contains this
Hydration for remediation,insulation,exclusion crew

what do you see when you select the following for problematic row

image

1 Like

Also try this for test purposes

BULK INSERT [dbo].[TEST]
FROM 'G:\DATA\TABLE.csv' 
WITH 
(   
    FIRSTROW = 2 
    , FIELDTERMINATOR ='|'
    , ROWTERMINATOR = '0x0a'
);
1 Like

Aha! The Bulk Insert succeeded. (It is a text file btw)
It even inserted the rows that the SSIS package fails on.

But the package is not able to import a few rows. So there is a problem in the package and I'm struggling to find it. I have tried defining all columns as DT_STR and DT_TEXT - the package fails. Any ideas? Thanks very much

not so fast Dj Bates :slight_smile: did the problematic row also get inserted using BULK INSERT ? row 655 column 63. Are you able to see it in there?

@jbates99 how about this trick in notepad for problematic row? ^^

Yes the row was inserted and the data in row 655 looks good. When I view the row in Notepad++ it looks good.

create a new file
add into this file columns headers and rows
654, 655 and 656

Run SSIS against that new file with only these 3 rows

This is why I hate SSIS when dealing with delimited files.

I ran the package with the text file trimmed down to only 3 rows as you suggested. It failed on row 2 as I expected

[Dynamic Flat File Source [2]] Error: Data conversion failed. The data conversion for column "Column 63" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

I was hoping the BULK INSERT could work with the file being a variable so I tried this:
Declare @FileToImport varchar(200);
set @FileToImport = 'G:\ConcurSAE\Meals\extract_attendee_detail_p0010838kah8_20190602171451.txt'

BULK INSERT AuditTest.dbo.[Concur_Attendee_Listing]
FROM @FileToImport
WITH
(
FIRSTROW = 1
, FIELDTERMINATOR ='|'
, ROWTERMINATOR = '0x0a'
);

failed with syntax error. Thanks

Would you like us to guess what the syntax error is? or you could provide it to us :stuck_out_tongue_winking_eye:

declare @dynamicBulkInsert varchar(max)

set @dynamicBulkInsert = 
'BULK INSERT AuditTest.dbo.[Concur_Attendee_Listing]
FROM ''G:\ConcurSAE\Meals\extract_attendee_detail_p0010838kah8_20190602171451.txt''
WITH
(
FIRSTROW = 1
, FIELDTERMINATOR =''|''
, ROWTERMINATOR = ''0x0a''
)';

exec(@dynamicBulkInsert)

Sorry i forgot to include that critical bit of information.

But your code works. However, my package loops thru 50 or so files so I will have to somehow embed the file name into your syntax. Do you already know it's possible to do? Thanks

declare @dynamicBulkInsert varchar(max)
Declare @FileToImport varchar(200);
set @FileToImport = 'G:\ConcurSAE\Meals\extract_attendee_detail_p0010838kah8_20190602171451.txt'

set @dynamicBulkInsert = 
'BULK INSERT AuditTest.dbo.[Concur_Attendee_Listing]
FROM ''' + @FileToImport  + '''
WITH
(
FIRSTROW = 1
, FIELDTERMINATOR =''|''
, ROWTERMINATOR = ''0x0a''
)';

My package variable FileToImport is populated by the ForEach Loop which loops thru several .txt files.

So I need to make the Bulk Insert work for a dynamic, ever-changing file name.
I added this to a SQL task
declare @dynamicBulkInsert varchar(max)

set @dynamicBulkInsert =
'BULK INSERT AuditTest.dbo.[Concur_Attendee_Listing]
FROM ''' + @FileToImport + '''
WITH
(
FIRSTROW = 1
, FIELDTERMINATOR =''|''
, ROWTERMINATOR = ''0x0a''
)';
exec(@dynamicBulkInsert )

It fails:

"Must declare the scalar variable "@FileToImport".

** Is there a way to reference the variable in my Bulk Insert statement? (The variable scope is the Package)**
Thanks