Oracle timestamp and SQL Server datetime2

dataservices job design
1

I’m trying to maintain one set of DS logic that can have SQL Server or Oracle as the source or target. The only datatypes that are giving me issues are dates. I want to maintain the precision of datetime2 in SQL Server and timestamp in Oracle.

Has anyone got this to work by just setting up multiple datastore configurations for a single datastore without having to reimport tables?

I’m thinking all DS logic will need to use timestamp, not datetime since this can truncate. DS timestamp to SQL Server datetime2 seems to work without truncate(there is a warning) BUT it requires a table re-import.

If I have a DS query(containing a timestamp mapping) writing to a SQL Server template table the columns are timestamp but the native type is datetime2. This works fine but importing the template table causes a truncation error. Not sure why… Reimporting the table obviously converts the timestamp columns to datetime and it works but I didn’t want to perform a re-import because now the table will need to be re-imported again for loading to Oracle so the datetime columns become timestamp.

Any comments or solutions are appreciated…

xdrex (BOB member since 2006-02-24)

2

Is this for a vendor application?

eganjp :us: (BOB member since 2007-09-12)

3

Yes. I want the flexibility to support Oracle or SQL Server without code changes.

xdrex (BOB member since 2006-02-24)

4

In that case what I would do is have two Dataflows in different .atl files. For SQL Server import the appropriate .atl file. For Oracle, the other .atl file. This would be at the time of install.

eganjp :us: (BOB member since 2007-09-12)

5

So which date datatype in Data Services should be used to support SQL Server datetime2 and Oracle timestamp as inputs and SQL Server datetime2 as output?

xdrex (BOB member since 2006-02-24)