Importing 725,000 Records Into Dynamics GP In 30 Seconds

At the MS Dynamics World Decisions Fall 2011 virtual conference, I gave a presentation titled Importing 10 Million Transactions Into Dynamics GP.  While the project discussed in that presentation was challenging, I was recently faced with a slightly different high-volume integration challenge.

The client works with several large trading partners, each of which provides a consolidated monthly payment, along with a data file that is effectively the remittance advice for the payment, indicating how the check should be applied to thousands of customers.  But this isn't a typical cash receipt.  A single payment may be up to $4 million, and the remittance advice is a 140 megabyte text file containing 725,000 rows of detailed data relating to thousands of invoices.  And that is just one of their trading partners.

The accounting staff have been manually processing these massive data files in Excel, manually entering thousands of transactions each month, then manually applying the payments to outstanding invoices, and then manually entering adjustments or memos for over-payments or under-payments.  Due to the highly detailed format of the remittance data file and quirks in the data, there was no way to directly import the file using a tool like Integration Manager, and even if they did manage to import the cash receipts, they would still have to apply the payments and manually enter the adjustments.

Although I have developed automated integrations that can process tens of thousands of transactions over the course of a day, I don't think I've ever had to process hundreds of thousands of records at once--while a user was waiting and watching the user interface.  It was obvious that performance was going to be a challenge.

I thought about a few different designs, and came to the conclusion that the best approach would be to get the raw data into a SQL table first, and then process it further from there.  I briefly considered loading the entire file into memory and then import it, but I need to perform several operations and transformations on the data that would be impractical and inefficient using ADO.NET.

So, I need to load a 140 megabyte CSV file into SQL Server programmatically.  Although I loved DTS, I am not a fan of SSIS, so programmatically running an SSIS job to bring the file in did not appeal to me--not worth the hassle.  However, I do have a C# class that does an excellent job of parsing delimited text files, and is blazing fast (it can load the 140MB CSV file into memory in 2 seconds).  So, I can read the text file and get it into a DataTable in memory, but then what?  How do I push that massive data table into SQL?

Performing over 700,000 separate insert statements is clearly out of the question--I didn't even bother to test it since I knew that it would take far too long.  I needed something faster...much faster...

After doing a little searching, I came across the .NET SqlClient SqlBulkCopy class.  Faaaaantastic.  All of the brains of the famous bcp utility, but in a stylish .NET package.  Just 5 lines of code.

using (System.Data.SqlClient.SqlBulkCopy bulkcopy = new System.Data.SqlClient.SqlBulkCopy(gpConn))
{
    bulkcopy.BulkCopyTimeout = 300;
    bulkcopy.DestinationTableName = tableName;
    bulkcopy.WriteToServer(dataTable);
    gpConn.Close();
}

With those few magical lines of code, SqlBulkCopy pushes all 725,000 records into SQL Server in 30 seconds (on my relatively modest virtual server).

Because the client (thankfully) doesn't want to actually import 725,000 separate cash receipts, they summarize the data by customer and month, which results in 2,100 summary cash receipts.  Because I have the data in SQL now, I'm able to use a query to summarize the data.  But even there I run into a performance issue.

Because there is so much data to summarize, my query initially took several minutes to run, and sometimes even caused my .NET app to receive a command timeout error.  Having read many articles about SQL query optimization, I knew that my best bet was to create a covering index on my data table.  Sure enough, after adding the covering index, the query dropped to 4 seconds.

So in roughly 45 seconds, the 140MB file is loaded and converted into 2,100 cash receipt transactions, ready to be handed off to eConnect.  eConnect then becomes the bottleneck, taking a few minutes to create the cash receipts, apply the payments to invoices, and then create debit and credit memos for any over or under payments.

Needless to say the customer is amazed and thrilled with the new solution.  Compared to the manual process that they have been performing to get the data into GP, the custom integration is like magic.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

Depositing Direct Deposit Information Directly

How's that for a tongue twister? When working on small-scale ( less then 250 employees) payroll implementations, manual entry is not necessarily a deal-breaker. And in some cases, it can be a good thing in terms of cleaning up and reviewing data. However, when working on larger implementations including one I am finishing up with over 1500 employees between three companies, manually entry is not possibly in many cases.

So, okay, fine. We use integration manager to import a lot of data including employee maintenance info, deductions, benefits, and pay codes. But what about direct deposit information? This is critical and subject to entry mistakes, so even if keying is possible...I don't know that I want to risk an incorrect account number or routing number. So, I use table import.

DISCLAIMER :) Always thoroughly test table imports in a test environment first, and also always make a backup prior to performing a table import.

Generally, I am not a fan of table imports because they can be prone to issues if you aren't completely confident of the table and field requirements. But this is one I have done repeatedly and with great success. Thousands of records can be loaded in seconds, and you can avoid carpal tunnel from the manually entry :)

Table import is located under Microsoft Dynamics GP>>Tools>>Integrate>>Table Import. And it should be noted that the import definitions are stored locally in the SY50o00.dat, SY50000.idx, SY50100.dat, and SY50100.idx files stored in \Program Files\Microsoft Dynamics\GP\Data. So if you plan to use the table import from more than one machine, you will need to copy these files from the machine used to create the import. Also, make sure you back up these files after creating the import in case they are accidentally overwritten during a new installation or are lost when a workstation is replaced.

To import to Cards>>Payroll>>Direct Deposit, you must create two table imports. One for the header and one for the detail record.

The destination table for the header record is Direct Deposit Employee Master. And the upload file should contain one record per employee with the Employee ID. The file can then be mapped to the header fields as follows:

• Employee ID= Employee ID (from file)
• Inactive= 0 (Constant)
• DD Remainder=1 (Constant, always will use first account for remainder of DD. Can be supplied in file instead if different settings are needed.)
• Index=0 (Constant)

Now for the detail records, the destination table is the Direct Deposit Employee Accounts Master. The upload file should contain the employee ID, index (incremental per employee, so if the employee has two accounts then the first one would be labeled 1 for the index and the second one would be 2 and so on), percentage (make sure this is formatted as a number like 10 for 10%), amount (only one of these fields should be completed for each line- percentage or amount), account number, routing number, type (23 for checking, 33 for savings). The file can then be mapped as follows for the import:

• Employee ID=Employee ID (from file)
• Index=Index (from file)
• Inactive= 0 (Constant)
• Deduction Method= 0 (Constant)
• Deduction= -none-/blank (Constant)
• DD Prenote= 1 (Constant)
• DD Prenote Count= 1 (Constant)
• DD Percent= Percentage (from file)
• DD AmountDir=Amount (from file)
• DD Account Number= Account Number (from file)
• DD Transit Number= Routing Number (from file)
• DD Transaction Code= Type (from file)

After importng, I run check links (Microsoft Dynamics GP>>Maintenance>>Check Links>>select Payroll) on the Payroll Direct Deposit Master. The records will have a status of Prenote (Cards>>Payroll>>Employee) and when you are ready to make all records active you can run the following script against the company database in SQL Server Management Studio.

UPDATE DD00200 SET DDPRE=0

This will update all direct deposit records to an Active status.

Hope some of you find this helpful, and as always, please share your comments, questions, and experiences.

Parsing Addresses: Importing Unparsed Addresses Into Dynamics GP - Part 1

Several years ago, I worked on a large Dynamics GP implementation that involved the migration of over 30,000 customer records from an old, custom, AS400 system.  The client was able to export the customer data to text files for us, but even though we had all of the data, we still had a problem. 

The data in the AS400 was stored in free form text fields, such as ADDRESS1, ADDRESS2, ADDRESS3, NOTE1, NOTE2, etc.  The system did not have separate fields for city, state, or zip, and there was no validation on any of the fields.  Contact names and phone numbers were placed in different fields.  And of course, there were alot of data errors, missing information, random notes, inconsistent formatting, and other oddities in the data that made a clean import nearly impossible.

At the time, we looked for tools to try and parse and validate the data, but we couldn't find any good, and economical, options.  The client ultimately chose to develop their own routines to parse the data just enough to import it into Dynamics GP's separate address and contact fields.  It wasn't pretty, but we got through it.

Fast forward to last month, when I was asked to develop an integration that would import customers from Excel.  Pretty basic, except for some fun caveats:  All of the address data will be in one column in Excel, and company names, contacts, and e-mail addresses are stored inconsistently in other columns.  Oh, and did I mention that the customer information was international?  Yup, the data included addresses from Michigan to Mongolia (literally, as in Ulaanbaatar).  Once again, I was in address parsing purgatory.  (Virgil, is that you???)

Here's one example of an actual customer address that I received (details modified slightly to protect the innocent):

Neal Hutchins/19 Colomberie, St Helier/Jersey/Channel Islands/JE5 7SY/United States/neal.hutchins@telecom-solutions.com, Narissa.Atista@telecom-solutions.com

Try parsing that bad boy.  If you try hard enough, you'll find that the address is not in the United States, but is actually somewhere in the UK.

With this fun challenge, I did some fresh research to see if anything had changed in the world of address parsing.  Given the nature of the problem, there is no single magical solution, but I learned of a few new options.

First, of course, were recommendations to try parse the data "manually" through code.  In some cases, this may be feasible (i.e. US addresses only), but given the data that I needed to parse, I knew that I didn't have enough time to write an address parsing routine that would be particularly accurate.

The next recommendation was to utilize mapping web services to "geocode" the address information.  This is a very good article and code sample on SQLServerCentral.com with a nice overview of the process, as well as a great code sample.  While this is a very slick solution that might be a great option for certain situations, there were a few significant limitations for my project. 

The first is that the geocoding option seems to only handle address information, not "contact" information, such as company name, contact, phone numbers, and e-mail addresses.  The second issue is that the geocoding option does not seem to do a very good job of partially parsing an address.  Many of my test addresses simply failed, and returned no results. 

And finally, the deal killer, which is unfortunately not mentioned in the SQL Server Central article, is that the Google Terms of Service heavily restricts the use of the API for separate (non-map related) or commercial applications.  I'm not a lawyer, so reading the terms of service didn't enlighten me much, but this is what others have informed me.

http://groups.google.com/group/google-contacts-api/browse_thread/thread/65fc032082d5e374

http://code.google.com/apis/maps/documentation/geocoding/#Limits

http://code.google.com/apis/maps/faq.html

So with those two options being ruled out, I started to look for other options that would specifically address my requirements.

I then learned that there are services that you can use to manually parse, scrub, and validate addresses.  While potentially effective, these involve paying for a service (presumably expensive) and sending data in batches.  I needed a real-time programmatic parsing and validation solution for my integration.

After more searching, I finally came across a solution.  I'll discuss it in more detail in part two.