Data Transfer Workbench is part of the SAP B1 basic training and certification exam. However certified consultant is familiar with only limited part of the data import power of DTW.
They know that it is good for limited scope initial data migration, where you need to fill in CSV templates, often referred as Excel templates among consultants. If you need to move historical data or setup ongoing data import from legacy system, we recommend you this small publication. It is not intended to end programmer, it is rather for IT manager or project manager, responsible for SAP BO implementation project results. We assume that such terminology as ODBC connection, SQL Queries and Views are common knowledge and if you got your Bachelor or MBA degree in the university – you probably have SQL class taken and consider yourself computer literate. Let’s come down to the details:
1. CSV Template compliant SQL View. It seems like Data Transfer Workbench is pretty simple in its data connection – it is tuned to process CSV templates. It has the option of connection to external data source via ODBC, however this is not documented in great details. For numerous customers we helped with ODBC connection to enable initial conversion or ongoing import from ODBC complaint DB platforms, including MS SQL Server, MS Access, Oracle, MySQL/PHP. How to open ODBC compliant data to DTW? Well, next paragraph has some clues
2. SQL View which replicates the CSV template rows. Well the answer might look like natural, however we saw numerous SB1 integration projects failed and customers asked for second opinion. Ok, here how you can accomplish the job. Consider creating Linked Server in MS SQL Server or use OPENROWSET construction to connection to external ODBC compliant database. Use Union clause to produce the first row as the headers (by whatever reason in some cases we had to produce two rows of header, prior to appending data sensitive lines) and the second select statement should match the document fields. In the header row, please use exactly the same names for the columns as in CSV template. If you are working with MS SQL Server 2008 64 bit and SB1 version 8.81 it is known issue that Linked Server is not working as you would expect. The alternative is SSIS package. Obviously SSIS is not something that you could call in DTW. We believe that Microsoft will fix the issue in the future. For now we recommend you to think about moving your legacy Accounting tables into MS SQL 2008 via Data import wizard and then create SQL view with unionized header and lines directly in SQL
3. Linux, PHP and MySQL. We noticed some trends that SAP B1 is popular in alternative Operating System platforms, meaning not Microsoft Windows, such as Linux. Popular free programming environment on Linux is PHP and free database platform is MySQL. There are popular ecommerce shopping carts dedicated to Linux/PHP. One of the most popular is Magento. If you are looking for small business ecommerce solution to integrate Magento shopping cart to SB1 Sales Invoice with customer deposit, there is the way to do export to text file from Magento and then process it via Data Transfer Workbench on demand. We tried scheduled DTW ODBC integration and it didn’t work on version 2007A. After appealing to SAP technical support we got the reply that it is not supported at this time (request was sent in 2009)
4. Historical Data Migration. It is not practical to insert millions of rows into CSV Template. Excel is designed to serve accounting people with reasonable restrictions associated with human document reading limitations. If you have to import millions of historical Sales Invoices the job should be done via SQL database
5. Message to international customers of SAP Business One. It is possible to do the whole data conversion or ongoing integration setup project via web session and remote desktop connection to your user workstation or the server. SB1 is marching across the world with good success in Europe, South America with Brazil, Asia including India and China. There is good progress in USA, Canada and Mexico. If you are working in the local facility of multinational company where in the headquarters they are deploying SAP or maybe Microsoft Dynamics AX or GP then B1 is a good candidate for your local accounting. It exports everything to Excel and you can suggest to do consolidated financial reporting in FRx or Microsoft Management Reporter via Excel worksheet of exported GL trial balance
6. 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
