Invalid Characters from SQL SERVER 2005 (IS08859-1) to BW3.5

dataservices sap-erp-bw
1

Hello,

We have a problem loading data to SAP BW 3.5 .
Source = SQL SERVER 2005 Collation ISO8859-1.

Implication from SAP is that invalid chars are non-unicode.
We find that the SQL code below will remove these chars but it is too slow.

$Chr = 128;
WHILE ($Chr < 256)
begin
$V_FIXED_STRING = replace_substr_ext( $V_FIXED_STRING, chr($Chr),’’,NULL,NULL);
($Chr),’’);
$Chr= $Chr + 1;
end

We have tried a number of different code page settings from source to target but nothing works without the slow sql code …

Any advice on how to remove these invalid characters without reverting to the SQL code would be appreciated

We’ve attempted the following Datastore codepage combinations:-

  1. Source = CP1251; rows per commit 5000 ; - > BW Target = CP 1251 (Import unsupported data types flag unticked)
    2)Source = Unicode; rows per commit 5000 ; - > BW Target = CP 1251
    3)Source = ISO8859-1; rows per commit 5000 ; - > BW Target = ISO8859-1
  2. Source = ISO8859-1; rows per commit 5000 ; - > BW Target = UTF-8
    5)Source = Unicode ; rows per commit 5000 ; - > BW Target = UTF-8

:roll_eyes:

Wylie_Coyote_ (BOB member since 2011-10-03)

2

Tried writing to a flat file to determine that your output from sql server is correct?

Johannes Vink :netherlands: (BOB member since 2012-03-20)

3

Thanks for the reply.

I did think of the flatfile to validate the data but in the meantime managed to get it to work. :slight_smile:

I ended up setting the source and target codepage(s) to UTF-8 and switching the column data types for the problematic columns (free text in source system) to nvarchar.

Doing this has been successfull.

Thank you for the advice , much appreciated .

Wylie_Coyote_ (BOB member since 2011-10-03)

4

I should add that I made the change to nvarchar in my sql server 2008 staging database.

Wylie_Coyote_ (BOB member since 2011-10-03)