Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

SSIS Excel Import conversion error

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
SQLSearcher
Asked:
SQLSearcher
  • 3
  • 3
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the error returned by SSIS?
0
 
SQLSearcherAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
SQLSearcherAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
SQLSearcherAuthor Commented:
Hello
I have deleted the empty rows that did not work.

Regards

SQLSearcher
0
 
Christopher GordonSenior Developer AnalystCommented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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

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.

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