Convert EBCDIC to ASCII Question?
Recently I was given 200 EBCDIC .dat files with 5 to 500 columns and various rows per file. This data then needs to be mapped to an existing db.table.column in sql r2.
I reviewed and article by http://mikedavissql.com/2011/08/31/...i-in-ssis/
which offers a solution. The problem is the time involved mapping column0 to column500 to a corresponding db.table.column in sql server r2.
What I would ideally like is to use is bcp (
bcp db1.eb.h_plan_type in C:\fmt\ebcidic_1.dat -f C:\fmt\ ebcidic_1.xml -S -T) or sql
FROM OPENROWSET(BULK 'C:\fmt\ ebcidic_1.dat',
FORMATFILE=' f C:\fmt\ ebcidic_1.xml’,CODEPAGE = 437(tried every combination)
) AS t1;
to convert the EBCDIC to ASII. My dilemma is when I use the bcp or sql it doesn’t translate it. It just places unreadable characters in the EBCDIC collated database which SSIS doesn’t. If I try to change the collation either through sql or ssis from EBCDIC database to SQL_Latin1_General_CP1_CI_AS database it still remains encoded. I’ve tried it seems every field COLLATION and datatype in my format xml and at the database. The only thing that works is the above article
How can I use bcp or sql to convert the EBCDIC to ASII?