Convert SQL Server uniqueidentifier to Oracle RAW via DTS

[Stefan Dipper]

Hi,

I want to create a DTS package that just plainly converts a SQL Server
Database into an Oracle Database, because we now additionally want to
deliver our product with Oracle.

Some tables that contain uniqueidentifier are converted without errors,
with others I get an error message saying

TransformCopy 'DirectCopyXform' validation error: ForceConvert not
specified but required for column pair 5 ( source column
'DATA_SOURCE_ID' (DBTYPE_GUID), destination column 'DATA_SOURCE_ID'
(DBTYPE_BYTES)).

The Transformation script looks like
---------------------------------------------
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("FIELD_NAME") = DTSSource("FIELD_NAME")
DTSDestination("TABLE_NAME") = DTSSource("TABLE_NAME")
DTSDestination("ALIAS_LABEL") = DTSSource("ALIAS_LABEL")
DTSDestination("ALIAS_ID") = DTSSource("ALIAS_ID")
DTSDestination("DATA_SOURCE_ID") = DTSSource("DATA_SOURCE_ID")
Main = DTSTransformStat_OK
End Function

----------------------------------------------
I have no clue what I could do in this case. The error even shows up for
one table that is currently empty.
I tried to truncate the length like

DTSDestination("DATA_SOURCE_ID") = left(DTSSource("DATA_SOURCE_ID"),
32)

but then I get an error
Error during transformation 'AxScriptXform' for row number 1.

Regards,
Stefan Dipper

[Michal Mikulik]

In fact, in Oracle is no data type functionally
corresponding to GUID. Probably char(36) will be best,but
you will have decide how to fill it from app. I reccommend
dont leave all conversion process on DTS, but design data
type mapping manually. Then create Oracle tables and copy
data.
Omitting GUID columns in Oracle can be also solution.

Hi
Michal

>.
>

[Stefan Dipper]

Michal,

thank you for your response. There is a expression for the
unique-identifier stuff in oracle 8.1.7, which is
the function SYS_GUID() returns a 16 byte GUID that should be stored in
a RAW column. So that is not the problem I have, my problem is how to
convert the existing uniqueidentifiers into this RAW(16) or RAW(32)
column via DTS, because it works for some tables, and it does not work
for other tables.

Regards,
Stefan

[Stefan Dipper]

Hi all,

Maybe someone is interested to read that I solved the problem by creating a
SQL Server View for each table that contains the uniqueidentifier, and in
the view definition I casted the uniqueidentifier to binary:

CREATE VIEW P800_WEB_SOURCES_V AS ( SELECT
CMP_ID ,
CAST(DATA_SOURCE_ID AS BINARY(16)) AS DATA_SOURCE_ID,
CASE WHEN MAIN_OBJECT= '' THEN ' ' ELSE MAIN_OBJECT END AS MAIN_OBJECT,
URL_ID , PRIORITY ,
CASE WHEN IS_DEFAULT= '' THEN ' ' ELSE IS_DEFAULT END AS IS_DEFAULT
FROM P800_WEB_SOURCES
)

The 2nd problem was, that ORACLE treats empty Strings ('') as NULL, so I
had to set a default of blank (' ') for the VARCHAR2 defined with NOT NULL
and the CASE in the SQL SERVER VIEW changes empty strings to blank.

Now the DTS converts the data from the SQL SERVER VIEW to the Oracle Table,
and all conversions that lead to problems are already done when reading
from the view. The DTS Package can then be created very easily.

Regards,
Stefan

---------------------------------------------------------------------------------------------------------