There are several powerful tools, developed by ISV channel and they are obviously pricy and require consulting budget. If you ask SAP B1 consultant on how we move our old accounting documents and master records into the new system, the answer likely to be that we will use Data Transfer Workbench
And what are the steps to prepare integration? They are first export data from legacy system, second save then in predefined CSV templates and the third pull these CSV files via Data Transfer Workbench. This way doesn’t look like a good option for large scale historical data import. However do not give up yet, we would like to give some highlights on how it really should be done with free tool, such as DTW:
1. ODBC connection option versus CSV or sometimes called Excel templates. It is available in Workbench. Let’s think about advantages in large scale historical data migration project. First of all you should expect migration to be tried in test environment with multiple attempts until you are satisfied with its quality and results. Second Excel is not designed to bring over millions of rows. Such migration volume should be done on the SQL platform. To our knowledge ODBC connection in WB is outside of the scope in basic SB1 certification classes. But let’s anyway try to open the hood of the application and see how it could be done
2. Create SQL View which replicates precisely the structure of CSV template, including the row of headers. You should be familiar with Union construction, where the first select statement is producing the headers and the second Select statement is pulling out the lines of data. We found some difficulties to create linked server on MS SQL Server 2008 64 bit platform. If you feel that it is also your issue, consider exporting your legacy documents into text file and then pull them into SQL via data import wizard or SSIS. When you have data in custom SQL tables, here is the time to create smart SQL View
3. Popular migration paths. We found out during our five plus years old SAP BO consulting practice that popular abandoned applications are Great Plains Accounting for DOS 9.5, 9.2, Great Plains Dynamics on Pervasive SQL Server 2000 version 7.5, 7.0, 6.0, 5.5, QuickBooks, Peach Tree. We also figured out that exporting records from your legacy accounting might be quite challenging and this is why we would like to give you some directions. Let’s take a look at mentioned applications one by one
4. Great Plains DOS, Windows or Mac versions. This is archaic Great Plains, not Dynamics. The database platform is Btrieve, however modern installs are mostly found on Pervasive SQL 2000 Server and single user workstation. In order to connect to Btrieve/Pervasive via ODBC you need to produce DDF files. The procedure is described in ODBC manual, available on GPA installation CD in PDF format. We recommend Pervasive Control Center, where you can issue Select statement to save results into text file. The challenge here is associated with the fact that GPA stores several tables in the same Btrieve file. This is why when you are looking at Select statement results you may find the rows filled with unknown characters. If you feel comfortable to work with GPA users to design the filter in SQL View to just get specific table results – great. Otherwise you need help from experienced consultant
5. Great Plains Dynamics on Pervasive SQL 2000. Table structure could be analyzed via user interface: Tools -> Resource Descriptions -> Tables. As it is Pervasive/Btrieve database you will need DDF (Data Definition File) to be available for connection either in Pervasive SQL Control Center or via ODBC compliant tools, such as probably Microsoft Access or SQL Linked Server
6. QuickBooks. As DB platform is proprietary we recommend printing reports into text files and then begin cleansing from there. Similar approach is recommended for Peach Tree and MYOB. Again good strategy is to import the reports into custom SQL tables and then build SQL View to weed out non structured rows, usually page number, report header, blank lines, notes
7. The author is enthusiast of various data import projects. He participated in migrations to Dynamics GP, AX, SL, SAP B1. You should understand the risks and advantages to get historical data migrated. If you are small company with limited budget, consider just having master records and GL beginning balances entered. In this scenario you keep your old accounting somewhere on the corner for data inquiry only in case if old-good-days but now forgotten customer calls. Author also believe in the idea that if you find this article via Google, Yahoo or MSN search chances are high that real expert is found. Internet is the market place and the place to be the arena of competitors. We do our community service job by publishing technical articles
8. Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representatives pick up the phone in Naperville and 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. Our core expertise is in International Business
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
