?
Solved

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

Posted on 2013-02-07
9
Medium Priority
?
452 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 2000 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

771 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