How to transfer data between two Microsoft Dynamics GP companies using Microsoft SQL Server Import and Export Wizard

One of the tasks that is often necessary in any Dynamics GP implementation is to get data across from the test company to the production “Go Live” company once we are ready to make the cut over. Those records are usually configuration and master records, but can also be beginning balances for subsidiary modules and General Ledger. This article will show you how to use Microsoft SQL Server 2008 Import and Export Wizard to transfer data across company databases.

The article assumes you are familiar with SQL Server Management Studio and that you know your way around databases and objects.

1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

2. In the Connect to Server window, click SQL Server Authentication in the Authentication list, and then type the sa user name and sa password. Click Connect.

3. In Object Explorer, double-click Databases. Right-click the database from which you want to export the data, point to Tasks, and then click Export Data.

4. In the Welcome window, click Next.

5. In the Data Source window, you can choose between Windows Authentication and SQL Server Authentication. It’s always been my preference to use Windows Authentication, especially if your Windows user belongs to the SQL Server sysadmin role. If you are not sure, click Use SQL Server Authentication in the Authentication area, and then type the sa user name and sa password. Click Next.

6. In the Destination window, type the server name in the Server Name area. In the Authentication area, click Use SQL Server Authentication, and then type the sa user name and sa password. In the Database list, click to select the destination database. Then, click Next.

7. In the Specify Table Copy or Query window, click Next to accept the default entries.

NOTE: The table(s) to which you are exporting data must exist in the destination database. If the table does not exist, you must follow the installation routines for the add-on or third party to which the tables are related. You can also use the SQL Maintenance window to identify and create the missing table(s). Start Microsoft Dynamics GP and go to File > Maintenance > SQL. While the SQL Server Import and Export Wizard has the ability to create the missing destination tables, this feature will not replicate certain table constraints and indexes that may exist in the source table.

8. Select the check boxes next to the tables that you want to copy to the destination database.

9. Highlight all tables to be exported, then click on the Edit Mappings button. Select Delete rows in existing destination tables and Enable identity insert. If you use Analytical Accounting, and you transfer Analytical Accounting tables, you cannot ignore DEX_ROW_ID for the following tables: Customer Master – RM00101, Vendor Master – PM00200, Item Master – IV00101, and Site Setup – IV40700. Hence, as a standard practice I enable this option to transfer data. Click on OK to continue.

In the Select Source Tables and Views window, click Next.

10. In the Save and Run Package window, click Next to accept the default entries.

11. Now, sit back and watch your data go across!

General Notes

These steps were performed using Microsoft SQL Server 2008. The Import and Export Wizard is fairly standard between this version of SQL Server and Microsoft SQL Server 2005 and you should be able to obtain very similar results. Consult your Microsoft SQL Server documentation for further information on the Import and Export Wizard feature.

Related Articles

KB article 874208 – How to transfer setup information between company databases by using SQL Server 2005 or SQL Server 2000.

Moving data between Microsoft Dynamics GP companies – This site, click here.

The often overlooked, yet powerful Table Import – This site, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to transfer data between two Microsoft Dynamics GP companies using Microsoft SQL Server Import and Export Wizard

One of the tasks that is often necessary in any Dynamics GP implementation is to get data across from the test company to the production “Go Live” company once we are ready to make the cut over. Those records are usually configuration and master records, but can also be beginning balances for subsidiary modules and General Ledger. This article will show you how to use Microsoft SQL Server 2008 Import and Export Wizard to transfer data across company databases.

The article assumes you are familiar with SQL Server Management Studio and that you know your way around databases and objects.

1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

2. In the Connect to Server window, click SQL Server Authentication in the Authentication list, and then type the sa user name and sa password. Click Connect.

3. In Object Explorer, double-click Databases. Right-click the database from which you want to export the data, point to Tasks, and then click Export Data.

4. In the Welcome window, click Next.

5. In the Data Source window, you can choose between Windows Authentication and SQL Server Authentication. It’s always been my preference to use Windows Authentication, especially if your Windows user belongs to the SQL Server sysadmin role. If you are not sure, click Use SQL Server Authentication in the Authentication area, and then type the sa user name and sa password. Click Next.

6. In the Destination window, type the server name in the Server Name area. In the Authentication area, click Use SQL Server Authentication, and then type the sa user name and sa password. In the Database list, click to select the destination database. Then, click Next.

7. In the Specify Table Copy or Query window, click Next to accept the default entries.

NOTE: The table(s) to which you are exporting data must exist in the destination database. If the table does not exist, you must follow the installation routines for the add-on or third party to which the tables are related. You can also use the SQL Maintenance window to identify and create the missing table(s). Start Microsoft Dynamics GP and go to File > Maintenance > SQL. While the SQL Server Import and Export Wizard has the ability to create the missing destination tables, this feature will not replicate certain table constraints and indexes that may exist in the source table.

8. Select the check boxes next to the tables that you want to copy to the destination database.

9. Highlight all tables to be exported, then click on the Edit Mappings button. Select Delete rows in existing destination tables and Enable identity insert. If you use Analytical Accounting, and you transfer Analytical Accounting tables, you cannot ignore DEX_ROW_ID for the following tables: Customer Master – RM00101, Vendor Master – PM00200, Item Master – IV00101, and Site Setup – IV40700. Hence, as a standard practice I enable this option to transfer data. Click on OK to continue.

In the Select Source Tables and Views window, click Next.

10. In the Save and Run Package window, click Next to accept the default entries.

11. Now, sit back and watch your data go across!

General Notes

These steps were performed using Microsoft SQL Server 2008. The Import and Export Wizard is fairly standard between this version of SQL Server and Microsoft SQL Server 2005 and you should be able to obtain very similar results. Consult your Microsoft SQL Server documentation for further information on the Import and Export Wizard feature.

Related Articles

KB article 874208 – How to transfer setup information between company databases by using SQL Server 2005 or SQL Server 2000.

Moving data between Microsoft Dynamics GP companies – This site, click here.

The often overlooked, yet powerful Table Import – This site, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to transfer data between two Microsoft Dynamics GP companies using Microsoft SQL Server Import and Export Wizard

One of the tasks that is often necessary in any Dynamics GP implementation is to get data across from the test company to the production “Go Live” company once we are ready to make the cut over. Those records are usually configuration and master records, but can also be beginning balances for subsidiary modules and General Ledger. This article will show you how to use Microsoft SQL Server 2008 Import and Export Wizard to transfer data across company databases.

The article assumes you are familiar with SQL Server Management Studio and that you know your way around databases and objects.

1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

2. In the Connect to Server window, click SQL Server Authentication in the Authentication list, and then type the sa user name and sa password. Click Connect.

3. In Object Explorer, double-click Databases. Right-click the database from which you want to export the data, point to Tasks, and then click Export Data.

4. In the Welcome window, click Next.

5. In the Data Source window, you can choose between Windows Authentication and SQL Server Authentication. It’s always been my preference to use Windows Authentication, especially if your Windows user belongs to the SQL Server sysadmin role. If you are not sure, click Use SQL Server Authentication in the Authentication area, and then type the sa user name and sa password. Click Next.

6. In the Destination window, type the server name in the Server Name area. In the Authentication area, click Use SQL Server Authentication, and then type the sa user name and sa password. In the Database list, click to select the destination database. Then, click Next.

7. In the Specify Table Copy or Query window, click Next to accept the default entries.

NOTE: The table(s) to which you are exporting data must exist in the destination database. If the table does not exist, you must follow the installation routines for the add-on or third party to which the tables are related. You can also use the SQL Maintenance window to identify and create the missing table(s). Start Microsoft Dynamics GP and go to File > Maintenance > SQL. While the SQL Server Import and Export Wizard has the ability to create the missing destination tables, this feature will not replicate certain table constraints and indexes that may exist in the source table.

8. Select the check boxes next to the tables that you want to copy to the destination database.

9. Highlight all tables to be exported, then click on the Edit Mappings button. Select Delete rows in existing destination tables and Enable identity insert. If you use Analytical Accounting, and you transfer Analytical Accounting tables, you cannot ignore DEX_ROW_ID for the following tables: Customer Master – RM00101, Vendor Master – PM00200, Item Master – IV00101, and Site Setup – IV40700. Hence, as a standard practice I enable this option to transfer data. Click on OK to continue.

In the Select Source Tables and Views window, click Next.

10. In the Save and Run Package window, click Next to accept the default entries.

11. Now, sit back and watch your data go across!

General Notes

These steps were performed using Microsoft SQL Server 2008. The Import and Export Wizard is fairly standard between this version of SQL Server and Microsoft SQL Server 2005 and you should be able to obtain very similar results. Consult your Microsoft SQL Server documentation for further information on the Import and Export Wizard feature.

Related Articles

KB article 874208 – How to transfer setup information between company databases by using SQL Server 2005 or SQL Server 2000.

Moving data between Microsoft Dynamics GP companies – This site, click here.

The often overlooked, yet powerful Table Import – This site, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Moving data between Microsoft Dynamics GP companies

I recently came across a question as to how to move data across Dynamics GP companies. It then became almost an instant challenge to find all the possible answers to this question and while I don’t claim to have them all, I will point out as many methods (and resources) I could think of. If you have used other methods I would certainly value your input to this post.

So here we go:

Method 1. SQL Server Backup/Restore.
This method assumes you will be creating an exact copy of the production data for either test purposes or as a simple fall back method for recovery purposes. See KB article 871973 for more information on how to create a mirrored copy of your GP data.

Method 2. Support Debugging Tool
You can use Support Debugging Tool’s XML Export and XML Import features to select specific tables to transfer. If transfering master tables, KB article 872709 lists the tables you will need to select for the transfer.

Method 3. Integration Manager
Integration Manager is still one of the best methods to transfer data between companies, especially at the end of an implementation, when you need to import master records and beginning balances into a production company that were previously recorded in a test company. Integration Manager can be found on your Microsoft Dynamics GP installation CDs. The following are links to the latest Integration Manager updates:

Microsoft Dynamics GP v9 – Click here
Microsoft Dynamics GP v10 – Click here

Method 4. SnapShot for Microsoft Dynamics GP
Written by David Musgrave, SnapShot works by copying the contents of selected tables to Ctree files in a separate folder, thus creating a SnapShot of the data. This separate folder can then be copied to a target system and the data inserted back into the actual tables. For more information on SnapShot and to download the product, visit the Developing for Dynamics GP website here.

Method 5. Company Setup Automated Solution
If the purpose is to transfer only setup information, then you will want to try the automated solution offered by Microsoft. The automated solution can be found here (CustomerSource/PartnerSource access required). For more information on Automated Solutions check out my previous article here.

Method 6. Table Import
Table Import is the forgotten tool. As many new and more robust tools are developed, this tool seems to drop to the bottom of the memory lane. Don’t forget that Table Import can bring data into specific GP tables that are not supported by the tools. Click here for more information about Table Import.

Hope you find these methods useful and contribute to the article with other methods you have used.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Table Import article continues to gain traction

The folks at MSDynamicsWorld.com have picked up on my Table Import article and are running a header in their customary “From the Blogs” section.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Developing for Dynamics GP – Weekly Summary

This was a very active week over at Developing for Dynamics GP. David Musgrave brings 6 excellent articles that, once again, cover a number of interesting topics from ways to execute scripts across multiple GP company databases, all the way to the impact Microsoft’s new Statement of Direction for Dynamics GP will have on everyday product features. So let’s get started!

Article 1: David first article — Running SQL scripts against all GP company databases — explores a batch file he had developed in conjunction with his friend Robert Cavill in the past. This batch file makes use of the OSQL command line utility application (OSQL.EXE) provided with SQL Server to execute a query against all Dynamics GP company databases. In summary, a T-SQL SELECT statement is executed against the Company Master table (DYNAMICS..SY01500) to retrieve the INTERID column values. These values are then used to execute a script repetitively for each company.

Remember, you can always achieve the same thing via T-SQL in Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005). However, this approach involves the use of cursors, as follows:

-- retrieves a list of customers from all companiesdeclare @companyID char(6)declare c_company cursor forselect INTERID from SY01500


open c_companyfetch next from c_company into @companyIDwhile @@fetch_status = 0begin  EXEC ("select * FROM " + @companyID + "..RM00101")  fetch next from c_company into @company IDend


close c_companydeallocate c_company

Article 2: How many times have you wished you could just record a macro and just execute it with a large dataset? Most of us, who have been in the trenches for a while, have known of a little dirty secret for quite some time now involving the use of Microsoft Office Word’s Mail Merge functionality to merge datasets into a macro file. In fact, macros are used to stress-test Dynamics GP and you can leverage this feature to do your own stress test.

David previously wrote KB article 953437, but have decided to bring it to the light on his blog site under the title How to Use Word Mail Merge and Macros to Import Data. Be sure to check this out as it is a unique chance to explore another interesting way of importing data and or stress testing your system.

If you are still looking for other ways of importing data into GP, please see my previous article on Table Import.

Article 3: Using VBA with Report Writer comes pack with a complete explanation of the Report Writer bands and how these are associated to Visual Basic for Applications (VBA) events. As David pointed out, “Most people are aware that you can use Visual Basic for Applications (VBA) with Microsoft Dynamics GP forms and the Modifier, but not everyone is aware that VBA can be used with the Report Writer as well.“, and it’s a shame because Report Writer, while very ‘primitive’ in it’s behaviour and architecture, still offers a wealth of possibilities over the more commercial reporting tools. Be sure to check out (as in try) the sample code and evaluate how this can be implemented in your future Report Writer projects.
Don’t forget to check other links posted by David in this same article with tons of examples on how to access data and expose that data onto Report Writer reports.

Article 4: Using ADO with VBA with Report Writer showcases a sample on accessing data stored in tables that cannot be easily linked using standard Report Writer table relationships. By now, many of you probably know that Report Writer only support one 1-to-Many table relationship on a report, which can be a serious limitation for more complex reports. However, the use of old fashioned calculated fields, little VBA, and the new UserInfo connection object can turn Report Writer into a very dangerous tool — just kidding about the dangerous piece .

If you are not to engraned with the terminology, be sure to check Microsoft’ ActiveX Data Objects (ADO) frequently asked questions page, or you can download the latest copy from here.

Article 5: I ran across this page last Saturday when working on my Table Import article, trying to dig up all SDKs and did not think of posting a blog about it. However, David was clever enough to put together blog with links to the Developer Documentation for Microsoft Dynamics GP page for releases 9.0 and 10.0.

Article 6: “Should I continue to develop in Dexterity?” That is the eternal question that customers and frankly speaking, developers around the world continue to ask as GP evolves more to a collaborative environment. David answers this by pointing out interesting features highlighted in the latest Statement of Directions for Microsoft Dynamics GP release, as it relates to developers.

From personal experience, let me tell you: Dexterity developers are in high demand! Even Microsoft is looking for a few good ones. So don’t get discouraged — but don’t fall asleep either — if you see everyone else shooting to learn Visual Studio. Dexterity is not going away anytime soon. I promise!

Hope you enjoy this explosion of articles and let David or myself know what you would like to see on our sites.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Developing for Dynamics GP – Weekly Summary

This was a very active week over at Developing for Dynamics GP. David Musgrave brings 6 excellent articles that, once again, cover a number of interesting topics from ways to execute scripts across multiple GP company databases, all the way to the impact Microsoft’s new Statement of Direction for Dynamics GP will have on everyday product features. So let’s get started!

Article 1: David first article — Running SQL scripts against all GP company databases — explores a batch file he had developed in conjunction with his friend Robert Cavill in the past. This batch file makes use of the OSQL command line utility application (OSQL.EXE) provided with SQL Server to execute a query against all Dynamics GP company databases. In summary, a T-SQL SELECT statement is executed against the Company Master table (DYNAMICS..SY01500) to retrieve the INTERID column values. These values are then used to execute a script repetitively for each company.

Remember, you can always achieve the same thing via T-SQL in Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005). However, this approach involves the use of cursors, as follows:

-- retrieves a list of customers from all companiesdeclare @companyID char(6)declare c_company cursor forselect INTERID from SY01500


open c_companyfetch next from c_company into @companyIDwhile @@fetch_status = 0begin  EXEC ("select * FROM " + @companyID + "..RM00101")  fetch next from c_company into @company IDend


close c_companydeallocate c_company

Article 2: How many times have you wished you could just record a macro and just execute it with a large dataset? Most of us, who have been in the trenches for a while, have known of a little dirty secret for quite some time now involving the use of Microsoft Office Word’s Mail Merge functionality to merge datasets into a macro file. In fact, macros are used to stress-test Dynamics GP and you can leverage this feature to do your own stress test.

David previously wrote KB article 953437, but have decided to bring it to the light on his blog site under the title How to Use Word Mail Merge and Macros to Import Data. Be sure to check this out as it is a unique chance to explore another interesting way of importing data and or stress testing your system.

If you are still looking for other ways of importing data into GP, please see my previous article on Table Import.

Article 3: Using VBA with Report Writer comes pack with a complete explanation of the Report Writer bands and how these are associated to Visual Basic for Applications (VBA) events. As David pointed out, “Most people are aware that you can use Visual Basic for Applications (VBA) with Microsoft Dynamics GP forms and the Modifier, but not everyone is aware that VBA can be used with the Report Writer as well.“, and it’s a shame because Report Writer, while very ‘primitive’ in it’s behaviour and architecture, still offers a wealth of possibilities over the more commercial reporting tools. Be sure to check out (as in try) the sample code and evaluate how this can be implemented in your future Report Writer projects.
Don’t forget to check other links posted by David in this same article with tons of examples on how to access data and expose that data onto Report Writer reports.

Article 4: Using ADO with VBA with Report Writer showcases a sample on accessing data stored in tables that cannot be easily linked using standard Report Writer table relationships. By now, many of you probably know that Report Writer only support one 1-to-Many table relationship on a report, which can be a serious limitation for more complex reports. However, the use of old fashioned calculated fields, little VBA, and the new UserInfo connection object can turn Report Writer into a very dangerous tool — just kidding about the dangerous piece .

If you are not to engraned with the terminology, be sure to check Microsoft’ ActiveX Data Objects (ADO) frequently asked questions page, or you can download the latest copy from here.

Article 5: I ran across this page last Saturday when working on my Table Import article, trying to dig up all SDKs and did not think of posting a blog about it. However, David was clever enough to put together blog with links to the Developer Documentation for Microsoft Dynamics GP page for releases 9.0 and 10.0.

Article 6: “Should I continue to develop in Dexterity?” That is the eternal question that customers and frankly speaking, developers around the world continue to ask as GP evolves more to a collaborative environment. David answers this by pointing out interesting features highlighted in the latest Statement of Directions for Microsoft Dynamics GP release, as it relates to developers.

From personal experience, let me tell you: Dexterity developers are in high demand! Even Microsoft is looking for a few good ones. So don’t get discouraged — but don’t fall asleep either — if you see everyone else shooting to learn Visual Studio. Dexterity is not going away anytime soon. I promise!

Hope you enjoy this explosion of articles and let David or myself know what you would like to see on our sites.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Developing for Dynamics GP – Weekly Summary

This was a very active week over at Developing for Dynamics GP. David Musgrave brings 6 excellent articles that, once again, cover a number of interesting topics from ways to execute scripts across multiple GP company databases, all the way to the impact Microsoft’s new Statement of Direction for Dynamics GP will have on everyday product features. So let’s get started!

Article 1: David first article — Running SQL scripts against all GP company databases — explores a batch file he had developed in conjunction with his friend Robert Cavill in the past. This batch file makes use of the OSQL command line utility application (OSQL.EXE) provided with SQL Server to execute a query against all Dynamics GP company databases. In summary, a T-SQL SELECT statement is executed against the Company Master table (DYNAMICS..SY01500) to retrieve the INTERID column values. These values are then used to execute a script repetitively for each company.

Remember, you can always achieve the same thing via T-SQL in Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005). However, this approach involves the use of cursors, as follows:

-- retrieves a list of customers from all companiesdeclare @companyID char(6)declare c_company cursor forselect INTERID from SY01500


open c_companyfetch next from c_company into @companyIDwhile @@fetch_status = 0begin  EXEC ("select * FROM " + @companyID + "..RM00101")  fetch next from c_company into @company IDend


close c_companydeallocate c_company

Article 2: How many times have you wished you could just record a macro and just execute it with a large dataset? Most of us, who have been in the trenches for a while, have known of a little dirty secret for quite some time now involving the use of Microsoft Office Word’s Mail Merge functionality to merge datasets into a macro file. In fact, macros are used to stress-test Dynamics GP and you can leverage this feature to do your own stress test.

David previously wrote KB article 953437, but have decided to bring it to the light on his blog site under the title How to Use Word Mail Merge and Macros to Import Data. Be sure to check this out as it is a unique chance to explore another interesting way of importing data and or stress testing your system.

If you are still looking for other ways of importing data into GP, please see my previous article on Table Import.

Article 3: Using VBA with Report Writer comes pack with a complete explanation of the Report Writer bands and how these are associated to Visual Basic for Applications (VBA) events. As David pointed out, “Most people are aware that you can use Visual Basic for Applications (VBA) with Microsoft Dynamics GP forms and the Modifier, but not everyone is aware that VBA can be used with the Report Writer as well.“, and it’s a shame because Report Writer, while very ‘primitive’ in it’s behaviour and architecture, still offers a wealth of possibilities over the more commercial reporting tools. Be sure to check out (as in try) the sample code and evaluate how this can be implemented in your future Report Writer projects.
Don’t forget to check other links posted by David in this same article with tons of examples on how to access data and expose that data onto Report Writer reports.

Article 4: Using ADO with VBA with Report Writer showcases a sample on accessing data stored in tables that cannot be easily linked using standard Report Writer table relationships. By now, many of you probably know that Report Writer only support one 1-to-Many table relationship on a report, which can be a serious limitation for more complex reports. However, the use of old fashioned calculated fields, little VBA, and the new UserInfo connection object can turn Report Writer into a very dangerous tool — just kidding about the dangerous piece .

If you are not to engraned with the terminology, be sure to check Microsoft’ ActiveX Data Objects (ADO) frequently asked questions page, or you can download the latest copy from here.

Article 5: I ran across this page last Saturday when working on my Table Import article, trying to dig up all SDKs and did not think of posting a blog about it. However, David was clever enough to put together blog with links to the Developer Documentation for Microsoft Dynamics GP page for releases 9.0 and 10.0.

Article 6: “Should I continue to develop in Dexterity?” That is the eternal question that customers and frankly speaking, developers around the world continue to ask as GP evolves more to a collaborative environment. David answers this by pointing out interesting features highlighted in the latest Statement of Directions for Microsoft Dynamics GP release, as it relates to developers.

From personal experience, let me tell you: Dexterity developers are in high demand! Even Microsoft is looking for a few good ones. So don’t get discouraged — but don’t fall asleep either — if you see everyone else shooting to learn Visual Studio. Dexterity is not going away anytime soon. I promise!

Hope you enjoy this explosion of articles and let David or myself know what you would like to see on our sites.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

The often overlooked, yet powerful Table Import

Before Integration Manager, eConnect, Web Services, SnapShot, SQL Server Data Transformation Services (DTS), SQL Server Integration Services (SSIS), or any of the supercharged, techno-geek tool you can quickly think of, there was Table Import. Considered by many at the bottom of the food chain when it comes to integration tools, the fact is, it still holds its weight in today’s XML-plagued world.

Background

In past releases of Microsoft Dynamics GP, Table Import was an absolute best (and fast!) approach to import data, especially because tools like the ones mentioned above have never been able to cover the entire spectrum of Microsoft integrating products and third party applications available. Take for example Manufacturing or Field Service. While these products have long been around, there’s little in the form of tools that can actually get data into their tables in a safe and validated way.

You may be thinking or asking, “well, how do I know what tables I need to import data into?”. The fact is, Table Import does require an understanding of GP’s table structures and their relations — this includes all integrating solutions too! However, Microsoft has put great emphasis in providing Software Development Kits (SDKs) that outline these tables and their columns, and in particular what values are required to be passed in a record for it to be valid.

Table Import Overview

The following is an example that will import a few customer records using the sample records in the Customer.txt file under the Integration Manager samples directory.

1) Open Table Import. Go to Microsoft Dynamics GP > Tools > Integrate > Table Import. This will open the Table Import Definition window. One advantage of the tool is its ability to save import definitions. For this example, I will use CUSTOMER.

2) Select a Source File Format. Table Import supports files that have been formatted as comma-delimited or tab-delimited. The sample Customer file is a tab-delimited file.

3) Choose the Source File. Click on the folder button, then locate the file to import. The source file for this example can be located under:

C:\Program Files\Microsoft Dynamics\Integration Manager 10\Samples\Customer.txt

4) Select a Destination table. Click on the Ellipses button to open the Chose a Table window. For this example, we will select the RM Customer MSTR table. All columns in the table will be displayed in the scrolling window.

NOTE: Knowing your tables in any product will facilitate this process.

5) Map each source column in the file to the destination column in the table. Highlight each row, then click on the ellipses button next to the Source prompt on the scrolling window header to select a column from the source file. If you need to map a constant value, enter it in the Constant field on the window, then click Add.

NOTE: For the customer class, I will use the constant USA-ILMO-T1.

Before processing the import, the definition window will look like this:

6) Process the import. Lets go ahead and click on Import to bring in our records, choosing to Save when prompt to save our import definition.

Table Import will provide a status of the import while creating a rejection file. The rejection file contains the records that could not be processed and can be used to re-import the exceptions.

7) Run Check Links on the appropriate tables to build any missing records in related tables. This is quite critical, since most tables are inter related. The check links process will attempt to build those missing references.

The above illustration shows a check links executed after the import. In this case, the customer summary records and address record have been created.

Summary
Table Import can be an effective way for the end-user with some tech savvyness to get some data quickly into GP. Don’t let the overwhealming amount of tools out there shy you away from using it, especially when these tools are not able to address parts of the application you are interested in integrating data into. Be cautious of the limitations — data validation being one of them — and arm yourself with all information possible before attempting any data import. Be sure to validate your data externally and apply common sense to ensure a safe import. Run check links and reconciliation where possible and if provided by the ISV or if importing into standard GP tables. But be sure to check the following resources.

NOTE: if importing data into third party tables, be sure to work closely with the product’s ISV. They are better equipped to guide you and help you through the process.

Software Development Kit (SDK) Resources

Microsoft Dynamics GP
Microsoft Dynamics GP v10 Software Development Kit — PartnerSource, CustomerSource
Microsoft Dynamics GP v9 Software Development Kit — PartnerSource, CustomerSource
Microsoft Business Solutions – Greate Plains 8.0 SDK — PartnerSource, CustomerSource

Field Service
Software Development Kit (SDK) for Field Service 8.0 — PartnerSource, CustomerSource

Manufacturing
Manufacturing Order Processing SDK for Great Plains 8.0 — PartnerSource, CustomerSource

Other Useful Resources

Many of my fellow MVPs have also blogged at some point on table integrations:

David Musgrave, MSFT. David recently published an article with 14 different ways of obtaining table information in Dynamics GP. This article also includes links to other blogs were this topic has been discussed.

Victoria Yudin, MVP. Check her series of articles on GP Reports. Victoria provides complete details on some of the tricky flags that exists in some of the GP tables, to be considered when importing more sophisticated data, such as Sales Orders, Purchase Orders, etc.

Mark Polino, MVP. Mark has a posted a few downloads on his DynamicAccounting.net blog page. Take a look at his GP 10 Table Reference and GP 9 Table Reference Microsoft Excel files.

Former MVP, Richard Whaley continues to deliver some of the best books in the market on everything GP. In particular, you won’t want to miss the Information Flow and Posting title. If you are interested, just click on the Accolade Publications link on the right of my blog.

[10/27/2008 - UPDATE] Tools Resources
This section has been added to include other tools that allow users to import/export data into GP, but that provide table information as well:

SnapShot – Click here to download SnapShot from David Musgrave’s blog site. SnapShot works by copying the contents of selected tables to Ctree files in a separate folder, thus creating a SnapShot of the data. This separate folder can then be copied to a target system and the data inserted back into the actual tables.

Support Debugging Tool – I have written extensively in the past about this debugging tool. SDT has an XML import/export feature that allows users to export the data of a table into XML format and reimport it back. Click here to find all links to download SDT.

I will continue to update this article with more and more resources, so be sure to check regularly. However, feel free to submit your own resources by dropping a comment with a link to them.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

The often overlooked, yet powerful Table Import

Before Integration Manager, eConnect, Web Services, SnapShot, SQL Server Data Transformation Services (DTS), SQL Server Integration Services (SSIS), or any of the supercharged, techno-geek tool you can quickly think of, there was Table Import. Considered by many at the bottom of the food chain when it comes to integration tools, the fact is, it still holds its weight in today’s XML-plagued world.

Background

In past releases of Microsoft Dynamics GP, Table Import was an absolute best (and fast!) approach to import data, especially because tools like the ones mentioned above have never been able to cover the entire spectrum of Microsoft integrating products and third party applications available. Take for example Manufacturing or Field Service. While these products have long been around, there’s little in the form of tools that can actually get data into their tables in a safe and validated way.

You may be thinking or asking, “well, how do I know what tables I need to import data into?”. The fact is, Table Import does require an understanding of GP’s table structures and their relations — this includes all integrating solutions too! However, Microsoft has put great emphasis in providing Software Development Kits (SDKs) that outline these tables and their columns, and in particular what values are required to be passed in a record for it to be valid.

Table Import Overview

The following is an example that will import a few customer records using the sample records in the Customer.txt file under the Integration Manager samples directory.

1) Open Table Import. Go to Microsoft Dynamics GP > Tools > Integrate > Table Import. This will open the Table Import Definition window. One advantage of the tool is its ability to save import definitions. For this example, I will use CUSTOMER.

2) Select a Source File Format. Table Import supports files that have been formatted as comma-delimited or tab-delimited. The sample Customer file is a tab-delimited file.

3) Choose the Source File. Click on the folder button, then locate the file to import. The source file for this example can be located under:

C:\Program Files\Microsoft Dynamics\Integration Manager 10\Samples\Customer.txt

4) Select a Destination table. Click on the Ellipses button to open the Chose a Table window. For this example, we will select the RM Customer MSTR table. All columns in the table will be displayed in the scrolling window.

NOTE: Knowing your tables in any product will facilitate this process.

5) Map each source column in the file to the destination column in the table. Highlight each row, then click on the ellipses button next to the Source prompt on the scrolling window header to select a column from the source file. If you need to map a constant value, enter it in the Constant field on the window, then click Add.

NOTE: For the customer class, I will use the constant USA-ILMO-T1.

Before processing the import, the definition window will look like this:

6) Process the import. Lets go ahead and click on Import to bring in our records, choosing to Save when prompt to save our import definition.

Table Import will provide a status of the import while creating a rejection file. The rejection file contains the records that could not be processed and can be used to re-import the exceptions.

7) Run Check Links on the appropriate tables to build any missing records in related tables. This is quite critical, since most tables are inter related. The check links process will attempt to build those missing references.

The above illustration shows a check links executed after the import. In this case, the customer summary records and address record have been created.

Summary
Table Import can be an effective way for the end-user with some tech savvyness to get some data quickly into GP. Don’t let the overwhealming amount of tools out there shy you away from using it, especially when these tools are not able to address parts of the application you are interested in integrating data into. Be cautious of the limitations — data validation being one of them — and arm yourself with all information possible before attempting any data import. Be sure to validate your data externally and apply common sense to ensure a safe import. Run check links and reconciliation where possible and if provided by the ISV or if importing into standard GP tables. But be sure to check the following resources.

NOTE: if importing data into third party tables, be sure to work closely with the product’s ISV. They are better equipped to guide you and help you through the process.

Software Development Kit (SDK) Resources

Microsoft Dynamics GP
Microsoft Dynamics GP v10 Software Development Kit — PartnerSource, CustomerSource
Microsoft Dynamics GP v9 Software Development Kit — PartnerSource, CustomerSource
Microsoft Business Solutions – Greate Plains 8.0 SDK — PartnerSource, CustomerSource

Field Service
Software Development Kit (SDK) for Field Service 8.0 — PartnerSource, CustomerSource

Manufacturing
Manufacturing Order Processing SDK for Great Plains 8.0 — PartnerSource, CustomerSource

Other Useful Resources

Many of my fellow MVPs have also blogged at some point on table integrations:

David Musgrave, MSFT. David recently published an article with 14 different ways of obtaining table information in Dynamics GP. This article also includes links to other blogs were this topic has been discussed.

Victoria Yudin, MVP. Check her series of articles on GP Reports. Victoria provides complete details on some of the tricky flags that exists in some of the GP tables, to be considered when importing more sophisticated data, such as Sales Orders, Purchase Orders, etc.

Mark Polino, MVP. Mark has a posted a few downloads on his DynamicAccounting.net blog page. Take a look at his GP 10 Table Reference and GP 9 Table Reference Microsoft Excel files.

Former MVP, Richard Whaley continues to deliver some of the best books in the market on everything GP. In particular, you won’t want to miss the Information Flow and Posting title. If you are interested, just click on the Accolade Publications link on the right of my blog.

[10/27/2008 - UPDATE] Tools Resources
This section has been added to include other tools that allow users to import/export data into GP, but that provide table information as well:

SnapShot – Click here to download SnapShot from David Musgrave’s blog site. SnapShot works by copying the contents of selected tables to Ctree files in a separate folder, thus creating a SnapShot of the data. This separate folder can then be copied to a target system and the data inserted back into the actual tables.

Support Debugging Tool – I have written extensively in the past about this debugging tool. SDT has an XML import/export feature that allows users to export the data of a table into XML format and reimport it back. Click here to find all links to download SDT.

I will continue to update this article with more and more resources, so be sure to check regularly. However, feel free to submit your own resources by dropping a comment with a link to them.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/