Mark Polino shows some love for Microsoft Dynamics GP integration tools

October 26, 2011

Like Mark, a lot of the work I do centers around large and complex systems integration with Microsoft Dynamics GP. It’s good to see that Mark addresses some of the glories, pains, and considerations for systems selection and integration to Microsoft Dynamics GP in his new article Dynamics GP Integration Flexibility and Frustration over at his Community column Mad Mark, The GP Road Warrior. In addition, he points out some of the integration tools and methods he’s used to get these to happen.

And speaking of integrations…

Please come join me at the GPUG Summit 2011 for the Optional Academy Training Classes. I will be delivering a full day training class on Data Migration and Integration Techniques where I will be highlighting some of the issues I have faced throughout my career when migrating and integrating data from and into GP.

My training curriculum is almost locked down, but if you are attending this class and want to see a particular topic being discussed please do so by adding a comment at the bottom of this post.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Advertisements

>How to schedule Dynamics GP to automatically log in and run an Integration Manager integration – revisited

April 19, 2011

>With my newly found stride, thanks mostly to Microsoft Dynamics Convergence Atlanta 2011 and the number of interesting questions I fielded during the event, I thought it was time to revisit the issue of scheduling Integration Manager integrations as part of our deep dive session, DDGP03 Microsoft Dynamics GP Customization & Integration Tools Review – see Microsoft Convergence Atlanta 2011: Day 3 for more on what transpired that day.

If you have been a follower of my blog for sometime now, you will recall that back in January of 2009 (ok, I don’t expect you to remember this) I posted an article on the subject – see How to schedule Dynamics GP to automatically log in and run an Integration Manager integration. In short, the article looked at using the Windows Task Scheduler to launch a batch file, which in turn would launch Microsoft Dynamics GP, which in turn would use a macro to log into the system, then run a previously created shortcut to run the integration…in summary, a very complicated set of steps if you ask me. This method also presented a problem for users running Windows Server 2008 and above, since the Windows Task Scheduler no longer supports desktop interaction, which is required by the macro system to execute a macro.

Fast forwarding one year and almost half later, and I still get this question regularly: How can I schedule an Integration Manager integration?

Here is my new secret…

I use a (non-Microsoft) product called System Scheduler Professional by Splinterware. It turns out that System Scheduler has no ties to the Microsoft Windows Task Scheduler – none whatsoever! In turn, it allows the product to do some really cool things like running as a service application which is just exactly what’s needed if you are going to schedule stuff when locking down your computer before leaving for the day is a must.

System Scheduler – Event Setup

System Scheduler allows you to setup an event or a list of events to be executed and even more cool, it allows you to send key sequences to an application with a complete help file that illustrates what’s possible. In older systems (like Windows XP) you can even set it up to unlock the computer and lock it as part of the list of events. Due to restrictions in the way Windows 7 is built this is not possible, but still, not needed if you consider that System Scheduler can run as a service under the LocalSystem account or a named account.

System Scheduler – Advanced Options

You can download the product with the link below:

System Scheduler Professional by Splinterware.

The Professional version allows you to try it out for 30 days before you need to register it. It is really very simple to use and if you had had any exposure to Windows Task Scheduler then this should be a breeze. Now, instead of the complex steps mentioned in the previous article, you can schedule Microsoft Dynamics GP to launch with the typical parameters and use a macro to log you in (if not using Microsoft Dynamics GP 2010 to remember the user and company for you). Once GP is up and running, you can use a the SendKeys function to lunch IM (or IMRun) and execute the integration of your choice.

As it turns out, you can also have a multi-event schedule that first launches GP then launches IM with the integration as a parameter. Please try the tool out and let me know what you think.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


IM – Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager – Part 2

January 9, 2011

Part 2 of 2 – Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager.


In the previous installment, I explained the technique that I otherwise use to relate source systems records with imported records in Microsoft Dynamics GP for which a Use Default field rule has been set for the key field. This article demonstrates the key event scripts needed to obtain the record.

Before Integration event script

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

' Secure a connection against the company database we will be running
' the integration against.

Const adUseClient = 3

set oCn = CreateObject("ADODB.Connection")
oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
oCn.CursorLocation = adUseClient
GPConnection.Open(oCn)

' Make sure the connection is valid
If (oCn.Status = 1) Then
' Setup global connection variables
SetVariable "gblConn", oCn
SetVariable "gblInterID", GPConnection.GPConnIntercompanyID
Else
CancelIntegration
End If

The Before Integration event script will allow us to secure a connection against the company database we will be running our integration against. By opening the connection in this event script, we will avoid having to open a connection for each transaction being integrated, further on, creating any loss of performance in the process. It is important to highlight that we need to save the successful connection to a global variable, to be able to use it in other event scripts. In this case, we will assign the oCn connection variable to a global variable, gblConn, using the SetVariable statement.

Once Integration Manager has integrated the document, we will use the After Document event script to retrieve the record integrated. At this stage, we are assuming that the mapping of source fields to destination fields provisions a user-defined field or description field for the source key field. In the past, I have also used note fields to store these key fields when it has not been feasible to use a standard Microsoft Dynamics GP field.

Note: the After Document event script will only execute upon success of the document being integrated. If Integration Manager is unsuccessful integrating the document, the Document Error script will execute instead. This event can be used to report failure to the source system, which may facilitate new attempts to integrate from the source system by reporting different event statuses.

After Document event script

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

' Prepare the SQL statement and retrieve the assigned Sales Transaction number
Set oCmd = CreateObject("ADODB.Command")
With oCmd
.ActiveConnection = GetVariable("gblConn")
.CommandType = adCmdText

.CommandText = "SELECT SOPNUMBE FROM SOP10106 WHERE USERDEF05 = '" & SourceFields("mySourceQry.KeyField") & "'"
Set oRst = .Execute

If Not oRst.EOF Then
SopNumber = oRst!SOPNUMBE
End If
oRst.Close
End With

' From here on you can open a connection to your source system and update the
' some status flag and the column provisioned to track the GP document number

I hope you find this technique useful. Of course, this is a technique I have been using over the years. I would like to find out from you what methods you have used to accomplish the same.

Until next post!

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


Custom Serialization Assemblies for eConnect 2010

December 3, 2010

Yesterday it was Bryan Prince demonstrating in-memory XML serialization, today is Chris Roehrich over at Developing for Dynamics GP. Chris shows a technique to create a Custom Serialization Assembly for eConnect outside of the method exposed in the eConnect Programmer’s Guide.

Please be sure to visit Developing for Dynamics GP to read Chris’ article.

Until next post!

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


Integration Manager: Integrating journal entries with Analytical Accounting Information

November 4, 2010

My good friend, David Musgrave, somehow manages to get me involved in interesting topics circulating in his inbox. Just recently, he came across a fairly long thread among his peers, needing to work out some Integration Manager issues for journal entries with Analytical Accounting information. David was kind enough to involve me, as I had posted an answer on the newsgroups a long time ago on this same issue.

If you are one of the fervourous Integration Manager fans out there and have had to work on integrating journal entries with Analytical Accounting information, you may know this is only possible with the eConnect Adapter, not the Standard Adapter.

The eConnect Adapter was introduced with Integration Manager version 10, and replaces the old SQL Optimized Adapter available in prior versions of Integration Manager. The eConnect Adapter in turn, leverages eConnect components to deliver a robust transactional environment for high volume integrations using ADO.NET to access Microsoft Dynamics GP company databases.

eConnect Adapter – Journal Entry# field with Use Default rule value

However, the eConnect Adapter, though, while it provides a Use Default rule value for the Journal Entry# field, this setting causes the integration to fail, as eConnect (the component) requires a value to be supplied, this is, the actual journal number.

Of course the question now is, how do you retrieve the next journal number from your company database to supply this value to the Journal Entry# field to allow the integration to be successful and thereby, preventing you from having to manually reserve ? The answer is, scripting, of the VBScript type.

You can add VBScript code to the Before Document event script in Integration Manager to retrieve the next journal number from your company database, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Option Explicit

Const adCmdStoredProc = 4
Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adInteger = 3
Const adVarchar = 200
Const adBoolean = 11
Const adChar = 129
Const adDate = 7
Const adNumeric = 131

Dim SqlStmt
Dim objConnection, objCommand, NextJournal

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")

objConnection.Open _
    "Provider=SQLNCLI10;Server=MGB001\GP11;Database=TWO; Trusted_Connection=yes;" 

With objCommand
 .ActiveConnection = objConnection
 .CommandType = adCmdStoredProc
 .CommandText = "glGetNextJEWrapper" 'our wrapper stored proc

 .Parameters.Append .CreateParameter ("@IO_iOUTJournalEntry", adInteger, adParamOutput, 4)
 .Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)

 .Execute
 NextJournal = objCommand.Parameters("@IO_iOUTJournalEntry").Value
End With

SetVariable "gblJounal", NextJournal

Set objCommand = Nothing
Set objConnection = Nothing

The above code calls the stored procedure dbo.glGetNextNumberWrapper, which leverages the existing Microsoft Dynamics GP’s dbo.glGetNextJournalEntry stored procedure to retrieve the next journal number, stored in the dbo.GL40100 (General Ledger Setup) table. As this is a call to a standard Microsoft Dynamics GP stored procedure, we are avoiding the use of custom code to retrieve the journal number and increment the value at the same time.

It is also necessary to note that the above code uses a Trusted Connection to connect to the company database. You can change the connection string as you see fit, just keep in mind that if you are going to use a SQL login, it cannot be a Microsoft Dynamics GP user login as the password for these logins are encrypted on SQL Server.

The following is the code for the dbo.glGetNextNumberWrapper stored procedure called by the Before Document script:

IF OBJECT_ID('dbo.glGetNextJEWrapper') IS NOT NULL
 DROP PROCEDURE glGetNextJEWrapper;
GO
CREATE PROCEDURE glGetNextJEWrapper
 @IO_iOUTJournalEntry int OUTPUT,
 @O_iErrorState int OUTPUT
AS
DECLARE @l_tINCheckWORKFiles tinyint = 1, @I_iSQLSessionID int = USER_SID(), @O_tOUTOK tinyint;

IF @IO_iOUTJournalEntry IS NULL
 SET @IO_iOUTJournalEntry = 0

EXECUTE glGetNextJournalEntry
   @l_tINCheckWORKFiles
  ,@I_iSQLSessionID
  ,@IO_iOUTJournalEntry OUTPUT
  ,@O_tOUTOK OUTPUT
  ,@O_iErrorState OUTPUT
GO
GRANT EXECUTE ON glGetNextJEWrapper TO DYNGRP;

For more information on connection strings, visit http://www.connectionstrings.com/. Also, check the following article over at Developing for Dynamics GP on why does Microsoft Dynamics GP encrypts passwords.

Once the Before Document event script is implemented, you can then add a small field script to the Journal Number# field to retrieve the value stored in the gblJournal Integration Manager global variable, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
CurrentField.Value = GetVariable("gblJournal")

Integration Manager has great import capabilities when combined with the power of scripting and when you have a clear understanding of the underlaying technologies that support it.

Please enter your comments on this article or any methods you have used in the past to overcome similar issues.

Until next post!

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


Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM

August 16, 2010

I have been involved for over the past 6 months with an extensive project requiring complex integrations between Microsoft Dynamics GP 10.0, Microsoft CRM 4.0 and other custom operational systems. In the process of designing and implementing these integrations the client requested a very easy to use interface that could be maintained without having to hire an army of developers or even specialized resources.

The mission: insert/update customer addresses and inventory items from Microsoft Dynamics GP into Microsoft CRM’s Product and Customer Address entities. The client also requested the integration be done using the Microsoft CRM web services in order to ensure upgrade support.

Background

Beginning with SQL Server 2005, the components required to develop basic CLR database objects are installed with SQL Server. CLR integration functionality is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. A reference to this assembly is typically added automatically by both command line tools and Microsoft Visual Studio, so there is no need to add it manually.

The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:

System.Data
System.Data.Sql
Microsoft.SqlServer.Server
System.Data.SqlTypes

You can find more information on SQL Server CLR integration over at MSDN. Be sure to check the following articles:

Overview of CLR Integration
CLR Stored Procedures

Solution

The solution can be broken down into two parts:

1. Creating the assembly with the CLR stored procedures that would in turn instantiate the CRM web methods to open a connection and insert or update the Product and Customer Address entity records.

2. Configuring Microsoft SQL Server and registering the assembly, creating the triggers on the RM Customer Address Master (RM00102) and Item Master (IV00101) tables that would invoke the CLR stored procedures to pass the Microsoft Dynamics GP records.

This week’s series will outline the solution with the code to achieve this. The following topics will become available on the day of their release:

08/18/2010 – Creating a CLR assembly and working with CRM web methods

08/20/2010 – Configuring SQL Server and creating table triggers

Until next post!

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


eConnect Integration Service for Microsoft Dynamics GP 2010

July 9, 2010

One of the biggest improvements in eConnect 2010 is the addition of the new eConnect Integration Service. The eConnect installer now creates a new Windows Service application named eConnect for Microsoft Dynamics GP 2010 integration Service. The eConnect Integration Service is a Windows Communication Foundation (WCF) service that replaces the (very unstable) eConnect COM+ object available in previous versions of eConnect.

The eConnect Integration Service supports the operations of the eConnect .NET assemblies, the BizTalk adapter, and MSMQ interfaces. In addition, you can use the service directly from an application that adds a service reference to the eConnect Integration Service.

If you add a service reference, you do not need to add the Microsoft.Dynamics.GP.eConnect assembly and namespace to your development project.

To add the eConnect Integration Service to an application, you must first add a service reference to the Visual Studio project for that application.

To add a service reference to a Visual Studio project, the properties for the project must specify the target framework as .NET Framework 3.5. The following is a typical URL for the eConnect Integration service.

net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/

The interface provided by the service reference includes the same methods you find in the Microsoft.Dynamics.GP.eConnect assembly and namespace. Before you use a service reference to access eConnect Integration Service, you should become familiar with WCF development concepts.

Until next post!

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