Solved

SSIS Excel Import conversion error

Posted on 2014-10-08
8
427 Views
Last Modified: 2016-02-11
Hello Experts Exchange
I have a Excel spreadsheet I'm trying to import in to SQL Server using SSIS wizard.

I have several columns all are fine apart from one, where SSIS see the field as a Double field, its not its a text field.

I opened the Excel file selected the column and used Text to columns to set the field to text, this normally works for me but at the moment it does not solve the problem.

If I was to save the package and open it up in Microsoft Visual Studio, is there a way I can get this column in, in its text format?

Regards

SQLSearcher
0
Comment
Question by:SQLSearcher
  • 3
  • 3
  • 2
8 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
What's the error returned by SSIS?
0
 

Author Comment

by:SQLSearcher
Comment Utility
Hello
There is no error message, either it imports and where I have data its sets it to null, or I set the datatype of the database table and then the SSIS wizard will only let me save the package.

Regards

SQLSearcher
0
 
LVL 14

Expert Comment

by:Christopher Gordon
Comment Utility
Is it possible to save the Excel file as a tab delimited or csv file?  SSIS and Excel make "guesses" on the data type of your columns that are often incorrect.
0
 

Author Comment

by:SQLSearcher
Comment Utility
Hello
I have several Excel sheets I need to import so I would like to be able to import from the Excel spreadsheets.

I have found a web page that gives me a solution to my problem, but now I have a new error.

Web site;
http://blog.concentra.co.uk/2013/05/15/why-ssis-always-gets-excel-data-types-wrong-and-how-to-fix-it/

New Error;
[Source - H4$ [49]] Warning: The external columns for Source - H4$ are out of synchronization with the data source columns. The external column "Part Number" needs to be updated.
The external column "Level 1" needs to be updated.
The external column "Level 2" needs to be updated.
The external column "Level 3" needs to be updated.
The external column "Problem" needs to be updated.



I have check the source data types for the problem fields and it is Unicode string [DT_WSTR], but the data will not import.

Can anyone help me please?

Regards

SQLSearcher
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Usually the last rows and columns are empty. If is the case, try to delete the empty columns and rows and run the import wizard again.
0
 

Author Comment

by:SQLSearcher
Comment Utility
Hello
I have deleted the empty rows that did not work.

Regards

SQLSearcher
0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 500 total points
Comment Utility
The issue is with the SSIS metadata.

Delete your destination task.

Right Click on your Source Task and click Edit.

Click on the "Columns" option in right hand tree view.  Make no changes and hit "OK".  You may get a message, hit "OK".

Put your Destination Task back on the canvas, reconfigure, and wire it back up to your source task.

This should refresh the metadata.
0
 
LVL 14

Expert Comment

by:Christopher Gordon
Comment Utility
Ah, my previous comment assumes you're doing this in visual studio.  You can ignore if your doing this via wizard.  Sorry for the confusion.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now