Although it is possible to switch from a Database connection to an ODBC connection, the resultant extract may not function correctly. The main issue is around the conversion of date fields under Oracle. SQL Server tends to handle the date conversion better in most cases. When an extract occurs over an ODBC connection the date is converted in the extract from the source system into a standard ASCII format. This ASCII formatted date is then converted back when it is loaded into the load table. To resolve this problem place a transformation on any date fields. Examples of typical Oracle transformations are:

'to_date('''||to_char(ORDER_DATE,'YYYYMMDD')||''',''YYYYMMDD'')'

to_char(ORDER_DATE,'YYYY-MM-DD HH24:MI:SS')

where ORDER_DATE is the name of the column being extracted. In the first example the conversion string is enclosed within quote marks (') so that it is passed as a literal to the select statement that will be built. Quote marks within the conversion string are protected from premature evaluation by the inclusion of a second quote mark alongside the original.

There are several supplied APIs for changing schema and connections programmatically. See Callable Procedures (on page 811) for more details.