Create ETL with Excel 2003 file as data source and SQL 2005 Dev Edition as data destination
Hi there For the past week I have battled to get data converted in order to import the data into my database. I have found numerous posts on the topic ranging from "You cannot import from Excel to SQL, rather use flat file" to "It relates to a Windows Registry problem". I was not able to find an answer to my question anywhere. The problem is also that SSIS is unable to convert DT_NTEXT directly to DT_STR, which is the data type that SQL is expecting. I am using two Data Conversion Transformations. The first converts the data from DT_NTEXT to DT_TEXT. The next one converts the data DT_TEXT to DT_STR. After which I do the Data Import. Is this best practice? Are there any better ways of performing the data conversion? Thanx HennieIt's not the blowing of the wind that determines your destination, it's the set of the sail. J Rohn
April 15th, 2011 2:35am

You can try to set the datatype directly in the advanced properties of the Excel Source. Look for "Output Columns".MCTS, MCITP - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
April 15th, 2011 4:42am

can you please post some sample data from excel file from your NTEXT column, I will try see.If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVR
April 15th, 2011 4:49am

Using data conversion component DT_NTEXT to DT_STR is not supported. You can use a derived column component and use this expresion: (DT_STR,400,1252)(DT_TEXT,1252) [ColName]Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
April 15th, 2011 5:08am

Using data conversion component DT_NTEXT to DT_STR is not supported. You can use a derived column component and use this expresion: (DT_STR,400,1252)(DT_TEXT,1252) [ColName] I am also doing exactly same thing you did but I am using a derived column component. Nitesh Rai- Please mark the post as answered if it answers your question
April 15th, 2011 5:12am

Thanx Koen I used Outpu columns to change the data type to DT_STR. But as I expected, I still get the error message:"Column "abc" cannot convert between unicode and non-unicode string data types." This is my main problem, and hence I am using 2 conversion steps. Even using this approach to change the data type from DT_NTEXT to DT_TEXT, does not work. I see no other way, but to use the two Data Conversion Transformations. But my question remains, why is Microsoft making things more difficult for us rather than easier? There seems to be no easy way to concert the data to back into SQL, where they originated from. It's not the blowing of the wind that determines your destination, it's the set of the sail. J Rohn
Free Windows Admin Tool Kit Click here and download it now
April 15th, 2011 5:15am

AS for the registry is concerned, there is a key called TypeGuessRows for the excel which should be changed to 0 from 8. This makes the SSIS to consider all the rows in the excel to determine the data type. When the value of the key is 8, the SSIS dtermines the data type based on the first 8 rows. Also the connection should contain IMEX = 1. Regards and good Wishes, Deepak.
April 15th, 2011 5:21am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics