Text File Fails to Import to Table

jbates99 · July 20, 2020, 8:42pm

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:

I set the ValidateExternalMetadata property to False - no go
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

yosiasz · July 20, 2020, 8:47pm

please post sample txt file and target table schema definition

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

harishgg1 · July 21, 2020, 6:10am

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

jbates99 · July 21, 2020, 5:19pm

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

jbates99 · July 21, 2020, 5:21pm

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

jbates99 · July 21, 2020, 7:29pm

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.

yosiasz · July 21, 2020, 7:52pm

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

jbates99 · July 21, 2020, 9:18pm

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

yosiasz · July 21, 2020, 9:22pm

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

yosiasz · July 21, 2020, 9:27pm

Also try this for test purposes

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

jbates99 · July 21, 2020, 10:44pm

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

yosiasz · July 21, 2020, 10:46pm

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

yosiasz · July 21, 2020, 10:47pm

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

jbates99 · July 21, 2020, 11:05pm

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

yosiasz · July 21, 2020, 11:26pm

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.

jbates99 · July 22, 2020, 3:35pm

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

yosiasz · July 22, 2020, 3:43pm

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

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)

jbates99 · July 22, 2020, 3:59pm

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

yosiasz · July 22, 2020, 4:07pm

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''
)';

jbates99 · July 23, 2020, 4:57pm

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