Importing Text file madness

Question

Importing Text file madness

RedBirdOBX

SSCommitted

Points: 1836
More actions
July 10, 2013 at 1:09 pm

#277255

OK. This one has driven me mad for the past couple of hours. I am working on a new & improved web site for this State Agency and converting some old Informix text-file based databases to SQL Sever and .NET.
The task is simple. Import a text file into a SQL Server 2008 Table.
I am trying to import this file into a SQL Server 2008 Table:
http://www.mvdb.virginia.gov/Downloads.txt
And I am getting these errors:
* Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Description" 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 1: The "Source - Downloads_txt.Outputs[Flat File Source Output].Columns[Description]" failed because truncation occurred, and the truncation row disposition on "Source - Downloads_txt.Outputs[Flat File Source Output].Columns[Description]" 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 1: An error occurred while processing file "C:\.....\Downloads.txt" on data row 2. (SQL Server Import and Export Wizard)
* Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - Downloads_txt 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. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
From what I can gather, my "Desciption" field is being truncated. Here's the weird part. The txt file comes another SQL Server (test machine) 2008 table. Description is defined as being varchar(200). So it the table/field I'm trying to import INTO. In fact, I doubled it to 400 just to see what it would happen. No luck. Same errors.
Any ideas? Going nuts here.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 1

I feel that I should also mention that the Import "Preview" seems fine. It shows me the first 100 records in a preview window and all seems fine. I also just bumped the receiving table's Description field to varchar(2000). No luck.

keebler96 Old Hand Points: 300 More actions · Answer 2

The problem is likely when you are using the import wizard for sql server... It defaults the column output width to 50 characters... You have to click on the Advanced section to define the data types and lengths of the columns coming from your text file.

Try increasing that size and see if it helps.

Chrissy321 SSCoach Points: 15763 More actions · Answer 3

If you are using the Import and Export Wizard and it is failing I would select the option to save the package and then open the package in BIDS. Run the package and post the error messages from BIDS. I recall the error messages being more explicit in BIDS. Troubleshooting in BIDS will be easier.

You also can simplify your file down to one line to see if its a file structure issue or a data issue.

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 4

keebler96 (7/10/2013)
The problem is likely when you are using the import wizard for sql server... It defaults the column output width to 50 characters... You have to click on the Advanced section to define the data types and lengths of the columns coming from your text file.
Try increasing that size and see if it helps.

Thanks! I'll take a look. Up until about 30 minutes ago, I was having to do this run around Export / Import via text files. Due to a Firewall blocking issue, I was having to export on one machine one on side of the firewall and import it via text file on the other side (laptop using hotspot on phone) even though both databases were SQL Server 2008.

Now that the firewall issue was fixed, I can import and export and stay natively to SQL08. Works fine. But I'll check that setting again and test it just to see if I missed it.

Thanks!