Solved

How to turn off the conversion between Excel and SQL Server Table

Posted on 2013-02-07
9
435 Views
Last Modified: 2016-02-10
I am building SSIS package where as initial step I am importing data from Excel (97-2003) Spreadsheet to SQL Server 2008 table. I got a Package Validation Error:

"Error at Data Flow Task [SQL Server Destination[14]]: Column "VendorName" cannot convert between unicode and non-unicode string data types."

I made sure that data format in the spreadsheet columns match table columns but it did not resolve the problem. Any suggestions? Thank you.
0
Comment
Question by:Burzhuin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 38864018
You have to open the advanced editor, go to the input nad output properties of the spreadsheet, and change the output columns type to match that of sql.
0
 
LVL 11

Accepted Solution

by:
Simone B earned 500 total points
ID: 38864033
In your data flow, you will need a Data Conversion task after your Source, and before your Destination.

Data Conversion
Within the Data Conversion task, convert the string columns. This makes a copy of each column in the correct string format.

Convert Data
Finally, in your Destination, map the copies of the columns to your SQL columns.

Mapping
0
 

Author Comment

by:Burzhuin
ID: 38864264
Thank you very much it helped to get rid of valied columns errors but I still have one last error.

I noticed that there is F10 column shown in Advanced Editor. But there is no such column in Excel Spreadsheet and in SQL Server Table. When I remove the column from External and Output Columns I still get the Error:

"Error at Data Flow Task [Excel Source [1]]: The output colum "F10" (682) on the error output has no corresponding output column on non-error output'"

How can I get tid of the column I cannot find?
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 11

Expert Comment

by:Simone B
ID: 38864316
It's possible that you have a value somewhere in that column in the spreadsheet, even just a space in one of the cells. You could try opening the spreadsheet and deleting the blank columns immediately to the right of your data.

You could also go into the Excel Source Editor, and ensure that F10 is not selected in the Columns tab. However if the column doesn't show in the Advanced Editor, I'm not certain it would show here.
0
 

Author Comment

by:Burzhuin
ID: 38865830
I inserted a conversion and adjusted the columns but when I execute the task the last component turn red. So the import has failed. And I cannot find any error why it happenned and what can be a cause of the import failure. Any suggestions?
0
 
LVL 11

Expert Comment

by:Simone B
ID: 38865845
Can you attach the package?
0
 

Author Comment

by:Burzhuin
ID: 38865881
I had to change the extension since it does not allow me to attach *.dtsx files.
ImportNetwork-uFinancial.txt
0
 

Author Comment

by:Burzhuin
ID: 38867807
I build the new Package from scratch and got the following errors in importing data to SQL Server Table:

[OLE DB Header Destination [1112]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Unspecified error".

[OLE DB Header Destination [1112]] Error: There was an error with input column "Copy of InvoiceNumber" (1167) on input "OLE DB Destination Input" (1125). The column status returned was: "The value violated the integrity constraints for the column.".

[OLE DB Header Destination [1112]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (1125)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (1125)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Header Destination" (1112) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

Error: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC0209029.  There may be error messages posted before this with more information on why the thread has exited.

Can somebody tell me what these errors mean? Thank you.
0
 

Author Comment

by:Burzhuin
ID: 38867864
I changed from OLE DB Destination to SQL Server Destination and from five rows of my test data three where imported into the Table and I got four, instead of five, errors:

[Import Network Header Destination [1209]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "The bulk load failed. Unexpected NULL value in data file row 2, column 5. The destination column (OriginalInvoiceNumber) is defined as NOT NULL.". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "The bulk load failed. Unexpected NULL value in data file row 1, column 3. The destination column (InvoiceNumber) is defined as NOT NULL.".

Unless I am blind could not find any NULL in the data in the File.
Header.xls
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question