0

I have a CRM consisting of 1200 tables and I want to move them into Access Database using SSIS. Please note that I have no tables existing in Access Database. What is the most efficient way for doing this?

I know we can do this by Access Wizard for connecting with external ODBC source but I want to do this by SSIS.

Sahil Tiwari
  • 159
  • 2
  • 12
  • What RDBMS does the CRM use? From our interaction earlier today, I'm not sure I understand the value of going from the CRM database to Access, when you ultimately want to move the data to SQL Server. I realize you didn't mention SQL Server here, but I'm just trying to help you with your ultimate goal. https://stackoverflow.com/questions/52820634/how-can-we-combine-more-than-2-connectors-in-ssis – Jeremy J. Oct 15 '18 at 20:26
  • Actually this CRM is Sage100. So far I have seen in their forums, it uses a providex database( I have no idea about it). Also their forum is a paid service which our company doesn't pay for. Earlier I tried to move data from Sage 100 to SQL server but I was able to do that by linkedserver. https://stackoverflow.com/questions/52782237/i-am-getting-index-was-outside-the-bounds-of-the-array-while-migrating-data-frThen I found other way of moving the data as demonstrated in this video: https://www.youtube.com/watch?v=D63Qi5BIgqE – Sahil Tiwari Oct 15 '18 at 20:44
  • Also I want to automate this process daily. – Sahil Tiwari Oct 15 '18 at 20:49
  • I seriously doubt you want all 1200 tables. You can probably cut that number in half immediately by not considering the temporary transactional tables that hold entries in process and focus on the posted tables. It's a much better idea go from SAGE with your ODBC to SQL Server with OleDB. Unfortunately, you will need a data flow for each data set (this can be a view or a group of tables in a query) – KeithL Oct 16 '18 at 11:12
  • Hi Keith, even I doubt we want those 1200 tables. I haven't worked on this task before. Do I need to write queries for each and every considered table? If yes, this will take months to write for all these 600 tables. – Sahil Tiwari Oct 16 '18 at 12:55

0 Answers0