0

I am trying to import and xlsx file in SQL Server using Wizard but when I try to do I get error in 'Executing'. I have trying everything I could think of but to no avail.

**Error 0xc020901c: Data Flow Task 1: There was an error with output column "DESCRIPTION" (24) on output "Excel Source Output" (9). The column status returned was: "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 "output column "DESCRIPTION" (24)" failed because truncation occurred, and the truncation row disposition on "output column "DESCRIPTION" (24)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Sheet1$" (1) returned error code 0xC020902A. 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)**

When I try changing the data length of one column to nvarchar max (it was nvarchar 255 by default), Executing becomes success But it gets stuck in 'Copying to [dbo].' I am using SQL Server 2008 and on Windows 10.

EDIT: I have isolated the problem. Through hit and trial I have found out that entries in a column similar to the one shown below are the reason it breaks. Can anyone tell me what exactly about the following text causes it to crash? Could it be line break? If so, how do I get around that?

POD:02S222, TL name contact: John Smith 111-111-1111>>Asset tag:SSSS9999
User gets windows credentials pop up while opening IE
error: authorized access

Error Messages:authorized access
Screenshots:attached
Operating Environment: Windows 7


call 2
-user did reboot
-logged on with new password
-then also user getting same pop up
-since password change didn't work
-user has no issues with outlook and lync
-user wanted to check if she has same issue in other asset
-hence logged on to different computer and checked
-able to open IE in that
-hence escalating to DSS

------------------------
call 1
-Asset tag:SRGW943S
-user is ROE but now sitting in the center
-happening since yesterday
-took control
-checked in dra: account fine
-when open IE user getting credential pop up
-hence made user to change password
-SSRPM was not there
-hence used change password option
-user was able to change
-still user was getting the pop up
-hen  asked to reboot and cehck
-shared the ticket no
**Title from related Incident record IM01516567:
DESKTOP / LAPTOP>>failure>>error message
Aurangzeb Rathore
  • 137
  • 1
  • 18
  • try converting your file into lower version (.xls) file first before importing – Ikyong Jan 30 '18 at 08:47
  • 1
    One workaround might be to export your sheet in CSV format. This would also allow you to see your actual data inside the wizard. – Tim Biegeleisen Jan 30 '18 at 08:50
  • I tried both of these methods. CSV brings about way too much data loss. XLS has massive data loss as well and shows the same errors. – Aurangzeb Rathore Jan 30 '18 at 08:53
  • Please check the edit. – Aurangzeb Rathore Jan 30 '18 at 09:31
  • @Leo how would the use of a *10-year old deprecated format* fix a truncation error? There's nothing wrong with `xlsx`. – Panagiotis Kanavos Jan 30 '18 at 09:44
  • @AurangzebRathore what you posted looks like a log file, not an Excel spreadsheet. And changing the type to `nvarchar(max)` is *NOT* a fix - that type is reserved for blobs that can reach 2GB or more. Do you *really* have an XLSX file with such *cells* in a single column? XLSX is a zipped package of XML files, it has no line breaks or column separators. Do you *really* have an XLSX file or a CSV with the wrong extension? – Panagiotis Kanavos Jan 30 '18 at 09:47
  • @PanagiotisKanavos That's what's bugging me. I don't understand how line breaks got in a XLSX file in the first place. And I do not know about it being a CSV before. This is how I received the file. Also, even if I try to import it in a CSV format, the problem persists. – Aurangzeb Rathore Jan 30 '18 at 10:03
  • @AurangzebRathore Excel cells can handle newlines just fine. If you open one with eg 7-zip you'll see that the lines were HTML encoded. Are you getting a single cell instead of multiple rows perhaps? That means that *exporting* the data dumped everything into a single cell. There's something wrong with the export process. You can't fix that with SSIS. That would explain the need for `varchar(max)` by the way - all the data was stored in a single cell – Panagiotis Kanavos Jan 30 '18 at 10:08
  • @PanagiotisKanavos the integrity of data is fine. The problem comes when being imported in SQL. If it isn't the size of the text, or the line breaks, then maybe a character type in the text? But that wouldn't make sense as nvarchar should encompass all the characters in the text. – Aurangzeb Rathore Jan 30 '18 at 10:11
  • I don't like the Wizard at all. Usually I experienced better results with C# Source Components, in which I handled the columns on my own... – Tyron78 Jan 30 '18 at 10:48
  • IMHO, If you can (if you have VStudio), It's better to make a simple SSIS from VStudio than to use SSIS integrated in SQL Management Studio. I have always problems when I try to import data from Excel files with SSMS – Jean Noel Jan 30 '18 at 11:20

1 Answers1

0

Not exactly a solution but the way around I used was import the data to MS Access and then import from there to SQL Server.

I found the solution here. SQL Error while importing Data From Excel

I'll keep the thread open in case someone offer a diagnose of the problem.

Aurangzeb Rathore
  • 137
  • 1
  • 18