Data Transfer Workbench is traditional data import tool and consultants are trained to prepare so-called CSV or Excel templates filled with data rows from your legacy accounting.
Workbench does perfect job where your import volume is modest and you only importing several tables. However it is not always possible to keep legacy accounting system for data inquiry. Especially when your legacy accounting is licensed on subscription base and you lose connection as soon as subscription expires. If you have to do historical documents conversion or would like to have automatic ongoing data migration we would like to give you some ideas how to do it in Data Transfer Workbench:
1. CSV Template and its imitation in SQL View. If you tried to look at connection options in Workbench you probably noticed that CSV is not the only option and you can connect to ODBC compliant source as well. DTW seems to require template format in ODBC source including such attributes as column names in the first two rows (we discovered by experimenting and we have no clue why two rows should be the same and contain column headers). Now some ideas about SQL View and its connection to the legacy database
2. SQL View could be cross-platform or sometimes referred as heterogeneous. This is where you use such constructions as Linked Server or Open Row Set in your Select statements in the view. For example you can join tables from such databases as MySQL, Pervasive SQL/Btrieve, Ctree/Faircom, Microsoft Access. In SQL 2005 and current 2008 Linked Server seems to become less recommended and emphasis is on SQL Server Integration Services or SSIS. Helping with data export from Great Plains DOS on Pervasive SQL 2000 we found it difficult to establish permanent connection and filter the content of the DAT file. First of all you have to produce DDF files in order to connect to Pervasive tables. Second problem lays in the fact that GPA stores several relative tables in the same file and when you are connecting via ODBC substantial percentage of rows doesn’t look as legitimate. Consider the option to import tables via SSIS (SQL Data Import Wizard) into custom SQL database and consider cleansing in the view over this custom database
3. SQL View instead of filled CSV file. The idea is simple just imitate. Use union construction to combine at least two select statements. First one should have two rows of CSV column names and the second select should provide data rows themselves
4. Advantages of the View versus CSV filling out. First advantage is the fact that the View is backed by SQL Server power and doesn’t have limits on the number of rows versus limitation of Excel. Microsoft Excel is designed to serve a human being and you should not expect human operator to type in millions of rows in MS Excel or another spreadsheet editing tool. Secondly SQL View is reusable versus CSV file which should be recreated again and again in integration iterations. When you are doing data migration you should expect the process to be repeated several if not to say multiple times until the results are good enough. The third advantage is the ability to join cross-platform database tables and in some ongoing integrations it is an advantage
5. Second opinion or what to do if your data migration project failed. First of all we apologize for the possible emotions toward this small business ERP platform. But the best ERP application implementation project could be screwed up by consultants who are in learning curve especially in such complex things as data cleansing and importing. Go back to the consulting market and make a research who is the best candidate out there to provide second opinion and estimate the recovery budget
6. Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representative pick up the phone in St. Joseph, MI call center). help@efaru.com We have local presence in Chicagoland, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico, Brazil nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian, Chinese. We specially stress our experience helping multinational companies in their Corporate ERP implementation
Dexterity Customization for Dynamics GP Evaluation Level Paper
When you are developer it is always a good idea to read technical manuals. But if you was just assigned to the IT team to decide if Dexterity is the right tool to customize your ERP application then first you need something which is in style of ‘easy reading papers’ or FAQ
Planning Dynamics GP Customization in Large Corporation
If you are reading this page then chances are high that you were not able to find ISV add-on and need customization project. Let’s talk about planning, quality assurance and future event such as version updates.
Dynamics GP Invoice Logo Attributed to Specific Company or Crossing the Borders of Three SOP Forms
Initial Great Plains Dynamics architecture had three SOP Invoice forms: Long, Short and Blank. Modern GP is popular in scenarios where you have more than three companies under one business entity umbrella
