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 

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

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

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!

 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

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

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

This tip was helpful for me

Thank you very much for your

"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.

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

HI

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

Thanks

Taruna

You saved my a$$ !

THANK YOU for this article!

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

Thanks .. It resolved my issue

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

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

NO it did not work out for me

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.  

@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.

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.

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

@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.

@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.

@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!

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

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

Works fine , Thanks a bunch sir. :)

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?

Language : (Xhosa - South Africa)

Message : Enkosi Kakhulu !!

Sobz,

Happy we can help.

Thank you,
Jeremy Kadlec
Co-Community Leader

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

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.

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.

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

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