Import Excel unicode data with SQL Server Integration Services

By:   |   Comments (74)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Integration Services Excel


Problem

One task that most people are faced with at some point in time is the need to import data into SQL Server from an Excel spreadsheet.  We have talked about different approaches to doing this in previous tips using OPENROWSET, OPENQUERY, Link Servers, etc... These options are great, but they may not necessarily give you as much control as you may need during the import process.

Another approach to doing this is using SQL Server Integration Services (SSIS).  With SSIS you can import different types of data as well as apply other logic during the importing process.  One problem though that I have faced with importing data from Excel into a SQL Server table is the issue of having to convert data types from Unicode to non-Unicode.  SSIS treats data in an Excel file as Unicode, but my database tables are defined as non-Unicode, because I don't have the need to store other code sets and therefore I don't want to waste additional storage space.  Is there any simple way to do this in SSIS?

Solution
If you have used SSIS to import Excel data into SQL Server you may have run into the issue of having to convert data from Unicode to non-Unicode.  By default Excel data is treated as Unicode and also by default when you create new tables SQL Server will make your character type columns Unicode as well (nchar, nvarchar,etc...)  If you don't have the need to store Unicode data, you probably always use non-Unicode datatypes such as char and varchar when creating your tables, so what is the easiest way to import my Excel data into non-Unicode columns?

The following shows two different examples of importing data from Excel into SQL Server.  The first example uses Unicode datatypes and the second does not.

Here is what the data in Excel looks like.

data

Example 1 - Unicode data types in SQL Server

Our table 'unicode" is defined as follows:

CREATE TABLE [dbo].[unicode](
[firstName] [nvarchar](50) NULL,
[lastName] [nvarchar](50) NULL
) ON [PRIMARY]

If we create a simple Data Flow Task and an Excel Source and an OLE DB Destination mapping firstname to firstname and lastname to lastname the import works great as shown below.

excel source

Example 2- non-Unicode data types in SQL Server

Our table 'non_unicode" is defined as follows:

CREATE TABLE [dbo].[non_unicode](
[firstName] [varchar](50) NULL,
[lastName] [varchar](50) NULL
) ON [PRIMARY]

If we map the columns firstname to firstname and lastname to lastname we automatically get the following error in the OLE DB Destination.

Columns "firstname" and "firstname" cannot convert between unicode and non-unicode data types...

excel source

If we execute the task we get the following error dialog box which gives us additional information.

package validation error

Solving the Problem

So based on the error we need to convert the data types so they are the same types.

If you right click on the OLE Destination and select "Show Advanced Editor" you have the option of changing the DataType from string [DT_STR] to Unicode string [DT_WSTR].  But once you click on OK it looks like the changed was saved, but if you open the editor again the change is gone and back to the original value.  This makes sense since you can not change the data type in the actual table.

common properties

If you right click on the Excel Source and select "Show Advanced Editor" you have the option of changing the DataType from Unicode string [DT_WSTR] to string [DT_STR] and the change is saved. 

input and output

If you click OK the change is saved, but now you get the error in the Excel Source that you can not convert between unicode and non-unicode as shown below.  So this did not solve the problem either.

excel source

Using the Data Conversion Task

So to get around this problem we have to also use a Data Conversion task.  This will allow us to convert data types so we can get the import completed.  The following picture shows the "Data Conversion" task in between the Excel Source and the OLE DB Destination.

data conversion

If you right click on "Data Conversion" and select properties you will get a dialog box such as the following.  In here we created an Output Alias for each column.

Our firstname column becomes firstname_nu (this could be any name you want) and we are making the output be a non-unicode string.  In addition we do the same thing for the lastname column.

transformation editor

If we save this and change the mapping as shown to use our new output columns and then execute the task we can see that the import was successful.

available input

excel source

As you can see this is pretty simple to do once you know that you need to use the Data Conversion task to convert the data types.

Next Steps
  • Next time you are importing data into SQL Server, don't forget about using the Data Conversion task if you are importing unicode data types into non-unicode columns
  • If you encounter this error Columns "xx" and "xx" cannot convert between unicode and non-unicode data types...remember this tip
  • Take a look at these other SSIS tips


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, September 3, 2019 - 5:47:27 AM - Srivatsan Back To Top (82219)

Hi, This method works good. And this solved my issue. Thanks for the help.  


Thursday, October 18, 2018 - 7:01:38 AM - Taona Madzudzo Back To Top (77976)

Thanks for the  great article. It helped me a lot


Friday, April 6, 2018 - 9:19:44 AM - Greg Robidoux Back To Top (75625)

Hi Pandurang,

In SSIS when you do the column mapping, check the datatype for the SSN column for both the input and output to make sure they are the same datatypes.

 


Friday, April 6, 2018 - 7:01:41 AM - Pandurang Back To Top (75622)

 Hi , I am importing the  columns , the column contains SSN data with "-" hypen symble in the text . while imort through excel transformation . null value is importing , SSN Number is not importing  for the "-" hyper symbole case .

could you please advise me how to import  as it is text in the  ssis package .

Thanks in advance

 

 


Thursday, October 5, 2017 - 4:34:24 PM - Cordell P Back To Top (66941)

 Hi there, this post finally solved my problem after a whole wasted day.

Thank you.

 

Cordell P.

 


Tuesday, August 29, 2017 - 1:11:14 PM - ANDRES JARRIN Back To Top (65580)

buen articulo amigo. Gracias


Sunday, November 29, 2015 - 5:13:52 PM - Fred Back To Top (40155)

Greg,

This was very helpful on a school project I was working on at Marist College in my Database class.  I was stuck on importing data from a spreadsheet until I found this, thank you, thank you, thank you!  I hope I cited this page correctly :).

 

Robidoux, G. (2012, 10 22). Import Excel unicode data with SQL Server Integration Services. Retrieved 11 01, 2015, from mssqltips: https://www.mssqltips.com/sqlservertip/1393/import-excel-unicode-data-with-sql-server-integration-services/


Wednesday, November 4, 2015 - 10:43:14 AM - Greg Robidoux Back To Top (39019)

Hi Micheal,

you could try to add a Data Viewer to the SSIS package to see the data from step to step.

Take a look at this https://msdn.microsoft.com/en-us/library/ms140318.aspx

-Greg


Tuesday, November 3, 2015 - 6:43:46 PM - MICHEAL Back To Top (39014)

I did this and I am only passing 50% of my data from the source csv file to the SQL Server table.

I get green boxes. but when i check the SQL Server table 'exactly' 50% of the data was moved to the destination.

Any ideas? or a link to the answer would be great.

Thanks!

 


Tuesday, August 4, 2015 - 2:43:04 AM - Nikash Back To Top (38358)

This tip solve my problem...........great job !


Tuesday, July 14, 2015 - 8:15:30 AM - Ram Back To Top (38202)

 

Nice and Simple Explanation. Thanks for sharing... 


Tuesday, January 27, 2015 - 5:39:35 PM - Erick Back To Top (36077)

Very good !!!, thanks for your contribution.


Friday, December 5, 2014 - 5:13:50 AM - MANOJ Back To Top (35521)

Really good explanation


Friday, November 14, 2014 - 6:00:18 AM - Vivek Singh Back To Top (35290)

Thanks a lot for this detailed explaination 


Monday, October 27, 2014 - 9:44:22 PM - Hussaini Back To Top (35084)

Hi Greg,

Thanks for detail solution, it is very well explained step by step. I searched many blogs online but didn't realy work. This post is very helpfull.

And once again Thankyou.

Thanks,

Hussaini. 


Wednesday, October 22, 2014 - 8:16:16 AM - Alex Back To Top (35035)

It may be not exactly what you are looking for, Camilla, but i personally use an Excel plugin savetodb to load and save big tables to SQL Server, hopefully it will work for you.


Wednesday, August 6, 2014 - 8:45:48 PM - Camila Back To Top (34035)

Hi Greg!

 

Just wanted to thank you! I worked fine for me, I did a simulation using a much smaller environment than the real one I am going to use...

 

I have a "big" database where I will have to aply your tips, and I was just wondering if there would be a faster way to apply these to a bunch a columns from a bunch of tables, ahaha

 

If anyone know/can help, please get in touch! 


Wednesday, July 16, 2014 - 1:35:23 PM - mike Back To Top (32739)

Yes, there is a DEBUG menu item.  The non-grey options are  "Windows -> Breakpoints",  "Attach to process", and "Toggle breakpoint"


Wednesday, July 16, 2014 - 10:23:38 AM - Greg Robidoux Back To Top (32734)

Hi Mike, do you see a DEBUG menu item on the top toolbar?  You could use the Start Debugging option in the DEBUG menu.


Tuesday, July 15, 2014 - 5:31:18 PM - mike Back To Top (32722)

Thanks, but I don't have a green "play" button in my BIDS session. I see a greyed out arrow. Do I need to be in debug mode or something ?


Tuesday, July 15, 2014 - 3:33:02 PM - Greg Robidoux Back To Top (32719)

Hi Mike,

From with BIDS or SSDT you can click on the green play button to execute the package directly from within the development tools and it will show you each step as it is processing.

Greg


Tuesday, July 15, 2014 - 1:17:45 PM - mike Back To Top (32716)

How do you run the SSIS package from the editor, showing the steps turn green ?

 

I have been saving the package, importing into SQL, then running the package, which is more cumbersome.


Tuesday, May 27, 2014 - 1:06:31 PM - Greg Robidoux Back To Top (31958)

Hi Thomas, I am glad this tip was helpful.


Tuesday, May 27, 2014 - 10:31:59 AM - Thomas Aimiuwu Back To Top (31956)

Hello Greg

Just want to commend for your immerse work and most importantly your kindness to humanity. This post was particularly helpful to

Thanks again.

Thomas(Bracknell, UK)

 


Monday, February 17, 2014 - 12:51:32 PM - patrick Back To Top (29475)

This is great , I had been struggling with this conversion non unicode to unicode. The way you explained it was the best.

Thanks a lot for the tip.

 

patrick


Wednesday, February 5, 2014 - 9:36:16 AM - Shovan Mukherjee Back To Top (29340)

The way you stress on Concepts while exlaning the Excersize really great !!! Very Nice way of Explanation Greg Robidoux. Thank You.


Friday, December 6, 2013 - 12:36:45 PM - Sid Back To Top (27719)

Follow up Solution:

Hello guys, here's what I did to make my package work. 

Background: Source columns are unicode strings (DT_STR) and destination columns are varchar,date and Int.

1. Set the text qualifier to "

2. Explicitely typecasted the columns using derived column

3.

For Integer columns, I used LTRIM and RTIM to remove any blank spaces:

LEN(TRIM([IntColumn])) ==0|| TRIM([IntColumn]) == ""?  NULL(DT_I4):DT_I4([IntColumn])

 

For date datatype columns, I checked for the conditions Null and empty spaces:

ISNULL([DateColumn]) || TRIM([DateColumn])=="" ? Null(DT_DBDATE):(DT_DBDATE)(DateColumn)

4. Also as a final step, I checked the source file for columns which are longer than what SSIS tell us they are. And I was so mad when I found that couple of columns were beyong size 150 while SSIS picked it up as 50. I used LTRIM,RTIM and Substring to fit the data into the destination tables.

Hope this solution helps you guys out as well.

-Sid.

 

 


Friday, December 6, 2013 - 10:32:16 AM - Alexander Back To Top (27717)

Hello Greg, i have a llitle question maybe you could help me.

I have a similar error (about error to convert some columns tha are not unicode) when i m trying to run a package that executes a store procedure and exporte the result in an excel:

Package Validation error:

Error at "REPORTE" [Excel Destination[16]]: Column "XXXXX"cannot convert between unicode and non-unicode string data types.

(and this message repeats for many columns, about 17 of 37 columns tha have the select statement)

I tried to use the DATA CONVERSION tool but i dont know hoy to configure it, ionly tried to select data type string[DT_STR] in the columns that hace the error but the error persists.

 


Monday, November 25, 2013 - 8:21:36 PM - Sid Back To Top (27603)

Followup: Even for testing puporses, the environment is set up in such a way that I don't have any create permissions to test it out with dummy tables.


Monday, November 25, 2013 - 5:44:46 PM - Sid Back To Top (27602)

 

Hi Greg,

 

I cannot alter the destination to Unicode as per the client's requirements.

New Attempt: I used derived column to explicitely convert the datatypes to match the column datatypes in the destination. The good news is the string datatypes have been taken care of but the unicode version of date,int(which are also the destination data types) are having issues.

 

Monday, November 25, 2013 - 3:48:56 PM - Greg Robidoux Back To Top (27600)

Hi Sid,

can you try a test and make your destination table Unicode to see if you can get the data to load?


Monday, November 25, 2013 - 3:38:14 PM - Sid Back To Top (27598)
 
Hey did anyone had encountered similar issue with a csv file source. I have been fighting with it by
 
  1. following the above steps
  2. adding extra data conversion tool
...but I still have the unicode-nonunicode conversion error throwing up.
 
background:
Source fiels are unicode
Destination table is non-unicode

Wednesday, August 21, 2013 - 4:44:40 PM - Anita Back To Top (26416)

Hi,

   I have 2 columns from Access Data base which has data types as memo . For one of the columns i converted from DT_NTEXT  to UniCode String[DT_WSTR] it works fine when i tried to convert the other  column its throwing me an error . 

[Data Conversion [1193]] Error: Data conversion failed while converting column "SERVICES" (1234) to column "Copy of SERVICES" (1267).  The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

     Anything missing during conversion ?


Tuesday, July 16, 2013 - 10:20:30 PM - Frank Djabanor Back To Top (25867)

Excellent tip!! Will definitly keep this in the back of my mind when moving data from Excel spreadsheets to SQL Server. I am relatively new to  SQL, and I am amazed at the wealth of information, and the willingness to share good info. I will pay it forward!


Tuesday, July 2, 2013 - 2:29:53 PM - Asghar Shah Back To Top (25681)

Thanks fo rthe tip. I was encountring similar error in SSIS 05 transferring data from Excel to SQL. I have used Derived Column for the conversion. Now I am gettting two columns in the output one unicode and one none unicode but it worked like a charm. Thank you.


Monday, June 24, 2013 - 10:44:48 AM - gopi Back To Top (25539)

Hi,

 

i have a scenario where i need to populate the data from Excel file with grouing, can some one help me out please.


Wednesday, June 12, 2013 - 2:09:06 PM - Prema Mallikarjunan Back To Top (25413)

Please help me.   I have situation where I use Oracle source to SQL server destination in SSIS  Everything works fine and I get data from oracle to sql destination without any problem.  I have "Invoice date" column where I use to_char(invoice_date, DDMMYYYY') in the oracle command and it stores as british format in sql table.  But I wanted the format to be in MMDDYYYY to store in SQL table.  I have tried all the data conversion tasks to convert it but nothing works for me. Do I need to use derived columns in SSIS?  If so can you please let me know with examples.

Thanks


Monday, May 27, 2013 - 10:45:27 AM - Armen Back To Top (25152)

Good solution!

But I have some issue with uploading Unicode data from Oracle table to SQL Server 2005 table via Import Wizard.

Data in Armenian are shown in tables as "?"

Even when I assign data type for appropriate column from varchar to nvarchar and repeat Import again it does not help.

Result is the same.

Could you help me with this issue?


Monday, May 13, 2013 - 4:43:47 PM - Steve Back To Top (23927)

Is there an advantage to data conversion vs. derived column?


Thursday, April 11, 2013 - 1:56:29 AM - Ujjwal Back To Top (23296)

Hi Data conversion works get, but it slows the process rather letting the SSIS create the table (just to get the datatype accurate) would be great and you can finally push the data into the final table in from that staging table in the format you like, i have noticed this issue while bring database from non-sql server environment like Oracle or DB2. Hope this helps.

Ujjwal


Tuesday, March 12, 2013 - 2:51:24 AM - vijay Back To Top (22726)

SSIS Issue:

 

Hi I am not able to laod the data more than 4000 charecters to the SQL database using SSIS pacakage . I am getting the following issue. Could somebody help me here.

 

Error Message:[XML Source [3245]] Error: The "component "XML Source" (3245)" failed because truncation occurred, and the truncation row disposition on "output column "Actions" (6814)"  at "output "StepDetails" (3254)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

 
I have defined the Actions column as Varchar(6000) on database and I have chnaged the External columns length on XML advanced editor to Unicode string (WT_STR) 4000. Please correct me for any changes.
 
Thanks,
Vijay.B
 
 

Monday, February 18, 2013 - 6:52:23 AM - Scott Back To Top (22226)

So, I'm converted a table from varchar to nvarchar etc..  So, I modifed the SSIS package which is  trying to export from SQL 2008 to a non-unicode file and I'm getting the dreaded cannot convert between unicode and non-unicode string data types.  I deleted the original green lines and removed the mappings.  I added a data conversion object and remapped the fields.  There's no errors on any of the objects.  But when it runs I get the error.


Tuesday, January 1, 2013 - 9:58:01 PM - Budisanto Back To Top (21220)

Mr. Robidoux, you're a great man!! thank you for your detailed yet simple tips!!


Wednesday, December 12, 2012 - 9:50:46 AM - Jeremy Kadlec Back To Top (20912)

Sobz,

Happy we can help.

Thank you,
Jeremy Kadlec
Co-Community Leader


Wednesday, December 12, 2012 - 8:34:07 AM - Sobz Back To Top (20911)

Language : (Xhosa - South Africa)

Message : Enkosi Kakhulu !!

 


Thursday, November 22, 2012 - 2:22:14 PM - Toufiq Back To Top (20477)

 

Hello there

 

Am facing  problem.  Am trying to export data from excel file to my data base table.  Am want to transfer data  to database table not as excel file format. Users are posting data in excel . Here example for my excel file format .

 

Excel  

 

Id    date        customer  product1 product 2 product3 product4

 

1   01-01-12  cus-01        10                12                0                       50

 

1   01-01-12  cus-02          0              15                20                      0

 

1   01-01-12  cus-03          0              15                0                         30

 

 

 

table

 

id            date       customer             product                                qty

 

 

 

please tell me how can I transfer data from excel to database table.

Is there any way?


Monday, October 29, 2012 - 7:57:52 AM - Abdul Rahman Back To Top (20122)

Works fine , Thanks a bunch sir. :)


Thursday, October 18, 2012 - 6:28:48 AM - Josuva Back To Top (19970)

 

Perfect Example.Your explanation awesome . I am become fan of you


Monday, July 30, 2012 - 8:27:57 AM - Rachael Back To Top (18846)

Perfect! It worked like a charm for me, Thanks.


Thursday, July 26, 2012 - 10:04:47 AM - Michael Back To Top (18813)

@Greg,  I just got into work this morning and have been informed that my admin rights are not full so IT in the company I work for is working on making sure that I have full admin rights.  I’ve already tried using the import wizard but was stymied there too.  It must come down to admin rights because this should be a simple one time data dump as you said.  Thanks for your help and patience with this matter, I really appreciate it!


Wednesday, July 25, 2012 - 5:57:34 PM - Greg Robidoux Back To Top (18803)

@Michael - not sure what the issue is you are having.  Is this just a one time data import?  Did you try to just use the Import Export Wizard?

If you go to Start > SQL Server > then there should be an Import and Export Data option.  This might be a simpler approach.

Sorry I don't have a specific answer for you related to your other issues.


Wednesday, July 25, 2012 - 5:26:07 PM - Michael Back To Top (18802)

@Greg – All I am trying to do at this time is take a csv file and populate a table in a database.  I used Excel to fill in the fields and then I saved as csv format.  I’m doing this so that I don’t have to manually populate this one particular table in the database.  There will be 2 more tables that are populated this way before I can start testing full database loads. 

As for what tasks for this, I am using a Data Flow task.  In the data flow, I have a Flat File Source, the Data Conversion as per discussed on this webpage, and the OLE DB Destination which is the database.


Wednesday, July 25, 2012 - 3:32:22 PM - Greg Robidoux Back To Top (18801)

@Michael - can you let me know what you are trying to do in SSIS and what tasks you are using.


Tuesday, July 24, 2012 - 12:21:01 PM - Michael Back To Top (18776)

Thank you very much for your quick response Greg, it's good to have people like you on the interwebs helping novices like me.  Your suggestions cleared up the build errors I was receiving but now run-time has gone all hoowee.  Instead of showing the complete output, I'll show one warning and 3 errors:

Warning: 0x80049304 at Data Flow Task, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.

 

Error: 0xC0202009 at Data Flow Task, OLE DB Destination [9]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.

 

Error: 0xC0202025 at Data Flow Task, OLE DB Destination [9]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

 

Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Destination" (9) failed the pre-execute phase and returned error code 0xC0202025.

 

I’m not experienced enough to know how to deal with these and am lost.  Thanks for your time.

 


Tuesday, July 24, 2012 - 11:58:34 AM - Greg Robidoux Back To Top (18775)

@Michael - you should use these formats in SSIS to load data from a CSV to a SQL table.

INT in SQL use a four-byte signed integer [DT_I4]

DATE in SQL use [DT_DATE]

NVARCHAR(MAX) use Unicode text stream [DT_NTEXT]

Hope this helps.


Tuesday, July 24, 2012 - 11:22:17 AM - Michael Back To Top (18774)

I followed this data conversion step by step but am still receiving the same errors.  The table I am currently trying to load with a csv file contains an int, 2 nvarchar(max), and a date.  I don't see nvarchar(max) as a type of option in the data conversion editor so I was hoping that using string[DT_STR] would suffice but it does not.  Also, to match my int in the table, I am using single-byte signed integer [DT_I1], is that right?  Finally, does date [DT_DATE] match the date properties of the database which just says date?  I noticed there are other date types to choose from in the editor but I am unsure as to which is proper.  


Saturday, July 21, 2012 - 10:22:59 AM - Bhanu Prakash Back To Top (18724)

NO it did not work out for me


Friday, July 6, 2012 - 8:03:49 AM - Greg Robidoux Back To Top (18371)

Dinesh - there are other data types you can use in SSIS.  The 255 was just used for this example. 


Friday, July 6, 2012 - 5:37:24 AM - Dinesh Back To Top (18367)

Where are we handling chars more than 255 in the above case?


Thursday, July 5, 2012 - 6:32:56 AM - Ram Back To Top (18353)

Thanks .. It resolved my issue


Thursday, May 17, 2012 - 2:00:24 PM - mike Back To Top (17509)

Thanks....good info that worked for me.


Friday, May 11, 2012 - 2:25:56 PM - Dave Back To Top (17423)

You saved my a$$ !

THANK YOU for this article!


Monday, April 30, 2012 - 6:05:16 AM - Taruna Back To Top (17196)

HI

Ur Tips were really useful for conversion between UNICODE and non UNICODE data.

 

Thanks

Taruna


Wednesday, April 11, 2012 - 10:48:23 AM - Mike Gibson Back To Top (16853)

As a side note, I have had the pleasure of playing with some of the new SQL Server 2012 features and they have made some good advances with Excel / SQL integrations.  If I find some time, I will put up a tip on it.  Thanks Greg!   Best, Mike


Thursday, March 8, 2012 - 9:36:11 PM - ssang Back To Top (16309)

"Friday, September 11, 2009 - 5:45:20 AM - joeording
Note:  On a 64-Bit Windows server the registry keys will be found here:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel"

Thanks to joeording for making this note.  I spent all day trying to fix this, even going so far as to import the Jet 4.0 registry and afterwards editing the TypeGuessRows key.  Turns out I needed to do this in the 64-bit reg key and now no NULLS on mixed data.


 


Tuesday, February 21, 2012 - 10:30:29 PM - Jet Back To Top (16122)

Thank you very much for your

 


Monday, February 28, 2011 - 11:36:07 PM - Subhash Makkena Back To Top (13071)

This tip was helpful for me


Saturday, September 19, 2009 - 12:01:57 PM - njserves Back To Top (4061)

Awesome work...very  explicitly mentioned...appreciate the help !!! 


Friday, September 11, 2009 - 5:45:20 AM - joeording Back To Top (4030)

Your issue is probably due to Mixed data types in a column.  Where you have numbers in some rows and text in other rows of a single column.  Here is a useful post I came across to fix this.

http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

 In the SSIS Excel Source Connection properties you need to modify the connection string like this, adding the IMEX=1 to enfoce the mixed types conversion.  Then change the registry keys to tell it to look through all the records to see if there are mixed data types.  The default is to look at the first 8 rows.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TEMP\XLTest.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
"ImportMixedTypes"="Text"
"TypeGuessRows"=dword:00000000

Note:  On a 64-Bit Windows server the registry keys will be found here:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel


Sunday, August 16, 2009 - 2:20:24 AM - sathyalan Back To Top (3890)

 Hi,

No need to have Conversion task.

Solving the Problem

So based on the error we need to convert the data types so they are the same types.

"If you right click on the OLE Destination and select "Show Advanced Editor" you have the option of changing the DataType from string [DT_STR] to Unicode string [DT_WSTR].  But once you click on OK it looks like the changed was saved, but if you open the editor again the change is gone and back to the original value.  This makes sense since you can not change the data type in the actual table."

Here is a catch. you can save this configuration.Go to the Destination task properties and change "ValidateExternalMetaData" to false.You are done. it was working for me.

 Hope this will help you.

 

Thanks,

Sathyalan

 

 


Monday, August 3, 2009 - 12:05:31 PM - sadewick Back To Top (3838)

I am so glad I came across this tip!! I was grappling with the conversion issue until I came across this solution using the data conversion component! Thank You very much!


Monday, March 2, 2009 - 8:00:06 AM - mjohn87 Back To Top (2909)

I was fighting with this issue for 4 hours until I see this post and the solution worked. Even though I had to figure out some screenshots since it is not elaborated much, I managed to leverage the information provided. Thanks a lot and hope your site will provide more solutions like this.

 Regards,
Mathew


Friday, August 29, 2008 - 1:19:06 PM - cincydba Back To Top (1722)

Your solution didn't work. I still have a column coming in with 90% NULLs.


Tuesday, May 13, 2008 - 4:22:27 AM - dolfa Back To Top (980)

 I read your tip, looks like very useful. But I am currently fighting with code page issues. I have a dbf file that I suspect been in different codepage than my database. Source connection cannot catch codepage, so I set AlwaysUseDefaultCodepage to True and set Codepage (I found this somewhere), added Data conversion and set codepage of the destination. But I am rewarded only with error, that conversion from source code page (852) to destination (1250 - czech btw) is not supported. Do you have tip somewhere how to solve this?

I am completely lost, I tried various approaches, searched tons of articles, still no luck :(

Thanks for any ideas 















get free sql tips
agree to terms