Solved

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

Posted on 2013-02-07
9
444 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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