Hi, i am using SSIS package to load data into a table and the data is coming from spreadsheet, having superscript data For example : when i load this data, it appears in table as CCRxSM. I want to load it as it is with that superscript words. can anybody help me with that?

Not sure why you see the TM sigh as CCRxSM. For example you can insert into Excel TM char code 99 (ASCII(hex)). The same ASCII you need to see in the table. Did you check what is being picked up from the Excel file? You may enable a data viewer to see what is coming up the pipe.Arthur My Blog

Thanks for your responce Arthur, i checked in Data viewer the data is coming as CCRxSM. I have Varchar(1023) datatype on that column.

if i just try to insert record "New York and Texas have which is Federally approved" it inserts "New York and Texas have CCRx? which is Federally approved". can anybody tell me what changes i should do in my package so i could load the data?

The service mark character is Unicode code point 8480. You'll need to use nvarchar instead of varchar. Note the difference in the following results. --service mark changed to ? SELECT 'New York and Texas have CCRx which is Federally approved'; --service mark retained SELECT N'New York and Texas have CCRx which is Federally approved'; Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Hello Dan, Thanks that was helpful. But how do i update this in my package? I tried changing the datatype of that field to nvarchar(1023)but didnt work. i have an excel connection source-->two data flow tasks-->Oledb destination

You'll need to update the data type in the destination table. The data is already flowing through your package as Unicode as is evident by the Data Viewer. Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Thanks Dan, So if i change the data type of that field to nvarchar. what should i put its datatype in Data Conversion task? i tried with DT_WSTR, it loads all data to destination but i still see the same record "New York and Texas have CCRxSM which is Federally approved"

DT_WSTR is Unicode (in contrast to DT_STR with code page) so I would expect the character to load correctly if the target column is also Unicode. Did you change the target column to nvarchar? Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Yes i changed the target column to nvarchar and while loading data to target table i have data type for that column DT_WSTR in data conversion task. but its not working.

Exactly what does the Data Conversion task do? The columns from the Excel source should already be DT_WSTR so no conversion should be needed unless you have other requirements. I ran a quick test and successfully imported the Unicode string from an Excel spreadsheet to an nvarchar column. The service mark character was properly retained. Is the Data Viewer you mentioned earler placed immediately before the SQL Server destination? Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/