• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

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

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
Burzhuin
Asked:
Burzhuin
  • 5
  • 3
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Simone BSenior E-Commerce AnalystCommented:
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
 
BurzhuinAuthor Commented:
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
Industry Leaders: 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!

 
Simone BSenior E-Commerce AnalystCommented:
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
 
BurzhuinAuthor Commented:
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
 
Simone BSenior E-Commerce AnalystCommented:
Can you attach the package?
0
 
BurzhuinAuthor Commented:
I had to change the extension since it does not allow me to attach *.dtsx files.
ImportNetwork-uFinancial.txt
0
 
BurzhuinAuthor Commented:
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
 
BurzhuinAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now