Article by Andrew Karasev
In initial implementation Data Transfer Workbench is often helping you in data migration from legacy application to SAP B1. Data conversion typically has three steps. The first step is date export from your old accounting either via direct ODBC connection or via printing reports into text file. Second step is data cleansing. Finally you are preparing your data in CSV templates usually in Excel and trigger data migration in DTW. In ideal world you are done at this point however in reality you probably are in situation when you have to redo these three steps as you are seeing problems with the quality of the converted data. Data Transfer Workbench could be deployed in ongoing data import scenarios. Let’s take a look at the design and process:
1. Where could it be required? Popular requests are in custom Ecommerce shopping cart migration in the form or AR Invoice and plus where you have to move documents from external database into Business One. We have seen requests to update catalog and prices including recommended replacement items when customer is ordering something obsolete
2. CSV emulation method. DTW is pretty simple tool and it automatically recognizes its CSV template format. In our opinion it is not difficult to export records from your external database in CSV files format. Exchanging data via text files is often deployed in cross-platform scenarios where you are exporting documents from non-Microsoft platform something like Linux PHP and MySQL. When you are exporting in CSV this integration could be scheduled and for additional information please see Data Transfer Workbench documentation
3. ODBC connection method. In our opinion this method is more elegant and gives you additional flexibility. Let’s take a look at it. In this scenario you can create custom SQL View directly in Microsoft SQL Server Management Studio. This view is looking into external database and it could be cross platform meaning that it is pulling records from something like Oracle, DB2 or MySQL. You can also pull data from the text file via Openrowset or Linked Server constructions. In order to provide quasi real time or even real time integration we checked with SAP technical support back in 2008 if ODBC based integration could be scheduled or not. It was for version 2007A. The answer in time was that scheduled integration via ODBC method is not supported. Currently we have version 8.82 and we had not have a chance to test scheduled method with this release. Article is written in December of 2011
4. Important tips on ODBC connection. Workbench seems to be committed to Comma Separated Value template standard and in order for you to have it read SQL View you need to imitate exact format of the template file including column headers in the first row. Simply open required template file in Excel and imitate its structure in your SQL View
5. Beyond DTW. Real Time integration could be programmed directly in Microsoft Visual Studio in C# or VB projects. You have to have some familiarity with Software Development Kit. There are also third party tools available from ISV partners such as iBolt for example
6. What is not recommended in integration. Business One deploys three tiers architecture direct feeding via SQL Stored Procedure or Insert statement are not recommended as they are bypassing application server data validation and could cause data compromise. DTW validates business logic as well as SDK does. Please think about these tools first and try to cool down SQL programming enthusiasts
7. Please call us 1-866-304-3265, 1-269-605-4904, help@efaru.com. We have local presence in Chicagoland, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA and Canada nationwide and internationally via web sessions and phone conferences (Skype is welcomed)