June 17, 2014 at 4:16 am
HI All,
I am currently in the process of migrating Sybase data to sql server where Sybase is having Japanese Collation set up (SJIS).
We have set up equivalent collation in Sql server DB also (Japanese_XJIS_CA_AS) and have tried to migrate data using SSIS. Howefer, while checking the data coming from SSIS OLE DB source preview, the column that is having Japanese characters are getting convereted to junk characters. Looks like something to do with SSIS that mis-understands the japanese characters. Could any one please help me to fix this issue?
I have tried to apply code page (Japanese collation) to the problematic column in data conversion column and got another column mapping error saying more than two code page mentioned. I could not fix this issue with different options tried.
Please help.
Thanks.
Regards,
Suresh Arumugam
June 17, 2014 at 4:17 am
What is the data type used in the SSIS data flow?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 4:48 am
Hi Koen,
SSIS OLE DB Source by default has taken the data type as DT_STR which I did not change. I have tried with AlwaysUseDefaultCodePage property also TRUE. Still the OLD DB source preview data does not come properly and showing up junk characters for Japanese data. Please help.
Thanks.
Regards,
Suresh Arumugam
June 17, 2014 at 4:56 am
Suresh Arumugam (6/17/2014)
Hi Koen,SSIS OLE DB Source by default has taken the data type as DT_STR which I did not change. I have tried with AlwaysUseDefaultCodePage property also TRUE. Still the OLD DB source preview data does not come properly and showing up junk characters for Japanese data. Please help.
Thanks.
And which code page does DT_STR use?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 5:03 am
I have so far tried with 50222 and 932 code pages for OLE DB Source and still see the junk data issue.
Thank you for taking time to help me.
Regards,
Suresh Arumugam
June 17, 2014 at 5:14 am
Suresh Arumugam (6/17/2014)
I have so far tried with 50222 and 932 code pages for OLE DB Source and still see the junk data issue.Thank you for taking time to help me.
According to this query, it seems code page 932 is needed.
select name, COLLATIONPROPERTY(name, 'CodePage') as Code_Page, description
from sys.fn_HelpCollations()
where name like 'Japanese_XJIS_%'
Can you verify on your system if the query returns the same result?
Maybe you can convert the data to unicode in the source query on Sybase?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 5:55 am
Koen Verbeeck (6/17/2014)
Suresh Arumugam (6/17/2014)
I have so far tried with 50222 and 932 code pages for OLE DB Source and still see the junk data issue.Thank you for taking time to help me.
According to this query, it seems code page 932 is needed.
select name, COLLATIONPROPERTY(name, 'CodePage') as Code_Page, description
from sys.fn_HelpCollations()
where name like 'Japanese_XJIS_%'
Can you verify on your system if the query returns the same result?
Maybe you can convert the data to unicode in the source query on Sybase?
Thanks. Let me try the option of Unicode convertion in Sysbase side.
Also, Could you please confirm if I have to convert the target Sql server column data type to NVarchar from Varchar (Sysbase source is varchar only)? We are not interested to make this change but most of the online suggestions are to make the datatype change. Please share your thoughts on this.
Thanks.
Regards,
Suresh Arumugam
June 17, 2014 at 6:00 am
NVARCHAR is when you store your data as unicode.
I expect this might give less issues, but you still need to do the conversion at sybase or in SSIS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 18, 2014 at 5:23 am
Hi Koen,
Just an update from myside.
I have seen a different behaviour when using ADO.NET source rather than OLE DB source in SSIS and the data with Japanese character from Syabse had come as the same in Preview unlike OleDB source task. ADO.NET source is using ODBC DSN to connect to SyBase and not sure if this makes a diference.
By the way, even after i get data properly, ADO.NET source is showing data type for the Sybase varchar columns as unicode string which I have to manually convert using Data conversion task to non-unicode (we do not want to change the Sql Server data type to NVarchar). This adds lot of manual work considering the no. of tables in Sybase.
Could you please share your thoughts if I can avoid the data conversion (considering Sql Server column to be varchar only NOT NVarchar) work in SSIS? Why SSIS ADO.NET source task shows sybase source column data type as unicode when its varchar only?
Thanks in advance.
Regards,
Suresh Arumugam
June 18, 2014 at 5:29 am
If the source - for whatever reason - thinks the columns are unicode and the destination is non-unicode, you'll have to do a conversion.
Either in the source query (which seems the easiest) or by adding a data conversion component.
No idea why the ADO.NET thinks it is unicode.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 11, 2014 at 1:10 pm
FWIW, a third-party solution may be helpful here. IRI NextForm is a DB migration tool I’ve connected to Sybase and SQL Server, et al, in Eclipse. It discovers and defines the source metadata. In the wizard I define cross-table mapping rules; in this case it might be data type conversions from SJIS to Unicode. At the end it auto-creates the target table DDL or BCP script. The 4GL job script (or XML workflow) runs from the GUI or in batch / command line.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply