I've suddenly got a requirement (in truth it's a colleague's problem but I'm trying to help out) to get data out of an OODB and into a reporting system. TBH, I know very little about OODBs and I'm not sure how best to go about this.
My thoughts are to use an ODBC connection or stage the data in some way.
The reason I'm posting here is because SSIS might be an option.
1 OODBMS Server (Jade).
1 SQL Server for reporting. Cognos and TM1 are on this server.
Reporting tools are Cognos and TM1. Both need to access the data.
Tables in question measure in the hundreds of thousands of records. But should allow for millions. Assume a handful of tables, say 5. I.e. really not that much data.
We do have Jade ODBC drivers but the 64 bit driver for TM1 doesn't work. (I don't really know all the details).
Failing that, and since both TM1 and Cognos need to access the data, staging seems to be a valid idea.
Option 1, Jade provides a replication service that will load the data into a relational database. This has licencing and configuration requirements which we are looking into.
Option 2, some sort of data extract (CSV I guess) and a bulk load into SQL Server. A linked server doesn't do OODBs, right?
Option 3, SSIS.
Option 3 is the option I'm primarily interested in here (though if anyone has comments on anything else those are welcome also).
Does SSIS seem like a good solution here?
Does anyone know of a custom source component prebuilt for this purpose?
Would it otherwise be a case of writing a script component (as a source) to talk to Jade?