Microsoft Dynamics GP Integration Manager module is pretty powerful and you can combine data massage techniques, such as moving text or ODBC compliant file into Microsoft SQL Server, transform them there with stored procedure and finally provide Dynamics GP IM with SQL View.
Ideally this small publication should help you with your Great Plains VAR, implementation partner and technical consultant selection, as well as to give you technical highlights on complex ongoing GP integration with your legacy system, including EDI
1. Advanced ODBC Data Source in Integration Manager. This is the one for you, if you plan to do union, group by, having and other SQL clauses. If you get CSV or tab delimited text file as the source, you should expect that ODBC text drive will have certain restrictions, and making reasonable probing efforts, if you feel that text driver based ODBC Source doesn’t do the job, you should consider switching to staging tables and view in MS SQL Server
2. SQL Staging Tables. We recommend you to deploy reading from text file as a table construction:
SELECT *
FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0′,
’Data Source=”c:\Integration”;Extended properties=Text’)…[FILE#txt]
If you do this, you should already have schema.ini file in Integration folder. The easiest way to create schema file is to create ODBC data source, based on Microsoft Text driver and reading from Integration folder
Using described above SQL select construction you can move your text file into SQL staging table
3. SQL data transformation. If you are moving data from raw text file, you may need data cleansing in your staging tables before exposing them to final integration. The best work does custom SQL stored procedure. Steps 2 and 3 could be easily implemented in the form of Data Transformation Services package
4. Final SQL View. If you already have staging tables in SQL , then the most feasible way to move data to Great Plains is SQL view creation and giving it as the source to Advanced ODBC Integration Manager source
About the author
Tags: consultant selection, data source, data transformation services, file txt, great plains, implementation partner, integration manager, jet oledb, legacy system, massage techniques, microsoft dynamics gp, microsoft jet, microsoft sql server, ms sql server, procedure steps, raw text, source c, table construction, technical consultant, union group