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.
BurzhuinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.