(Extracted from previous tickets)
Applicable versions: All released versions of DSP / Collect and CranSoft / DataGarage
Issue:
We currently have the requirement to connect to a Lotus Notes datasource. We've been able to install the latest Lotus Notes client and NotesSQL ODBC driver, which let's us connect to the database and view tables/views. However, there are a few datatypes that are causing issues for me in the SSIS wizard, including the following:
TIME (8), DATE (10), & LONG VARCHAR
I can also get the connection test to pass in Sys Admin / DataSources, but with a Database connection type that I know is incorrect, so when I try to create a manual Assemble / CranPort package, it gives an error message for the Lotus Notes ODBC driver as follows:
"ERROR [37000] [Lotus] [ODBC Lotus Notes] Table reference has to be a table name or an outer join escape clause in a FROM clause"
I am assuming this is due to the incorrect database type at the DataSource level. However, I can still "View Source" from Assemble / CranPort, which leads me to believe the connectivity is okay and there is potentially a datatype or SQL side issue. So, all that as background, I have the following questions:
1. Is there a Database Type / Connection Type yet in DSP/CranSoft that supports Notes SQL/ Lotus Notes/Domino?
2. If not, am I able to configure my own Database Type so that I can create conversions for the above datatypes within Collect (DataGarage) and Assemble (CranPort)?
3. Is there any way we could get DataGarage to build DTS packages for Lotus Notes?
Responses and Recommendations:
1. The simplest alternative approach is to push the data from Lotus Notes into a SQL Server database. A suggested method is described in detail here. You can then use this sdb as a Source to EzMap or DSW. If they exported to SQL Server on another server, then use Assemble / Cranport to import that SQL Server source.
2. To maintain control over the extracts you can make manual DTS / SSIS packages and register them in Collect / DataGarage, so the process is working nicely. The issues with the datatypes mentioned above were due to the ODBC driver used in SSIS. I tried this with DTS and chose the actual Lotus Notes Driver and that resolved the issue.
3. It is not possible to get Collect / DataGarage to build packages for Lotus Notes automatically. There are no system tables for Lotus Notes that Collect / DataGarage can read. This is why other clients found the direct export to SQL Server worked better. You can do manual SSIS or DTS.
4. This project ended up wanting to keep making manual DTS packages so that we had control over the extracts as opposed to relying on too many parties. Some tables worked quite nicely, but other tables had datatypes that the LotusNotes driver couldn't read; for those, the Domino DBAs helped us by creating views of the tables and this worked.
Additional Tips:
1. In Notes, create or have created views of the data. These will probably be based on Form views that already exist.
2. Install the Lotus Notes ODBC driver on the webapp server.
3. Create a dummy SQL Server database, example name: NotesExtract
4. Create a datasource in System Administration / Configuration / Datasources for this database.
This is necessary because even if you define a Manual SSIS package the application FIRST validates connectivity to the datasource. It cannot connect to the actual Lotus Notes datasource so it throws an error. But it will be able to connect to the dummy SQL data source. By naming it NotesExtract it looks good as a Source in Collect or Map.
5. Follow the instructions for defining a manual SSIS package. Save the package with the password for your CranSoft/DSP SQL Server login account.
6. Register the NotesExtract source to your sdbNotes target in Collect / DG.
7. Register your manual SSIS extracts.
Now you can use Collect/DG to extract the data from the Notes views.