Trying to Export Data from an unusal Access Database with Synonyms

Question

We use a product called SalesOutlook which is a type of CRM system. I need to export data from this using SSIS or some other program.

When you install the program, it creates a DSN on my system called SalesOutlookReports. It use the Microsoft Access Driver (*.mdb).

When I try to look at it using Crystal Reports I see a list of tables like:

SchemaAccount Contacts
SchemaAccount Profiles
SchemaNotes

And then under that is another folder called Synonyms and it contains

Account Contacts
Account Profiles
Notes

If I open the synonyms, I get my data, if I open the schema tables I get the meta data about the table.

My question is this. How can I easily import information from the tables I need into SQL Server for manipulation? How can I designate the synonyms instead of the "tables" so I can import the data? I cannot find a way to do it using the import/export wizard with SQL Server nor in SSIS. Perhaps I could use a linked server from SSMS?

Answer 1

ODBC connection in SSIS Data Flow Task should do the trick. Place a Data Flow task and then inside that you can select an ADO.NET data source. You can connect to an ODBC connection using that datasource. Select your MS Access database and voila you have a source. You can add derived columns if you need to adjust the data type and finally connect it to an OLEDB destination, which would be SQL Server.