3

We use non unicode on our sql servers. We do a lot of import of excel files using ssis and we spend a lot of time mapping excel unicode to our sql server non unicode columns. Is there some where in ssis I can set a default or some option so that SSIS can default map its source unicode columns to destination non unicode where ever it detects unicode ? This link shows the steps we do right now. It would be nice to have an option in ssis that says 'use this template when importing excel files..' or something like this. I am sure a lot of you import excel files via ssis and I am hoping you will provide some tips in this regard. thank you

Gullu
  • 3,477
  • 7
  • 43
  • 70

3 Answers3

1

There is no mechanism around the way in which the Excel Connection Manager treats text. It is going to treat text in the safest manner possible by assuming it's Unicode. The only option you have with the Connection Manager is exactly what you are doing.

An alternative however, would be to disregard the Excel Connection Manager and use an OLE Connection manager as I describe in this answer. Excel Source as Lookup Transformation Connection

Instead of sending that data to a Cache Connection Manager, just a query to pull data in from your spreadsheet and your conversion issues should be a distant memory.

Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159
0

Have you tried to configure the Flat File Connection Manager and change the type of each column there, it might be an option.

Rednaxel
  • 938
  • 2
  • 16
  • 33
-1

Use .csv files, the flat file connection managers uses DT_STR as default.

Erik Ellinger
  • 1
  • To make this answer more useful to readers of this question, consider adding a description to explain what you're doing and why your doing it. – Chris Albert Apr 16 '20 at 01:47