| SAP B1 ERP and MRP platform should be | | | | 2. SQL View technology. If you move query |
| seamlessly integrated with your legacy | | | | design from Excel to SQL select statement, then |
| applications, such as Oracle, Microsoft SQL Server | | | | you are virtually breaking all the boundaries. We |
| custom databases, ODBC compliant sources: | | | | recommend you to consider the following path - |
| Excel, MS Access, CSV and tab delimited text | | | | import your source master records or |
| files. If you got initial training in SAP BO Data | | | | transactions into MS SQL Server based staging |
| Transfer Workbench, you should be already | | | | table (where you may consider adding identity |
| familiar with Microsoft Excel and templates | | | | insert column for uniqueness). Please consult your |
| concept. In this small publication we will cross the | | | | SQL DBA person to understand the limitations of |
| Excel limits and give you the highlights on SQL | | | | Excel and advantages of the SQL Server Views |
| queries, and this means virtually unlimited ability to | | | | and Stored Procedures |
| integrate any database platform and not in initial | | | | 3. 64 and 32 bit Windows 2003 Server dilemma. |
| data migration and conversion, but even in | | | | If you are reading your integration from the text |
| ongoing data integration: | | | | tab or comma separated values files, then you |
| 1. ODBC query anatomy. As Workbench is really | | | | should be aware about 64 bit platform limitations. |
| tuned for Excel CSV templates, where field | | | | You can't use such popular driver as |
| names are defined according to SB1 importing | | | | Microsoft.Jet.OLEDB.4.0. Instead you will have to |
| object rules, you should follow the same rules in | | | | import text file through Microsoft SQL Server |
| creating SQL view - please in view creation define | | | | DTS or data import wizard directly. If you are still |
| the same column names as in Excel template for | | | | on the old-good-days Windows 2003 Server 32 |
| the intended object. If you follow this rule, then | | | | bit, enjoy the advantage to deploy select |
| your object integration will recognize your columns | | | | statement from Microsoft.Jet.OLEDB.4.0 compliant |
| mapping automatically on the fly, and you will not | | | | text file. On the Windows x64 OLEDB seems to |
| need to change and save changes for DB schema | | | | be not yet available, or at least, there are |
| - the topic we would like to avoid in this executive | | | | technical challenges to use OPENROWSET, reading |
| and IT programmer level publication | | | | from the text file. |