Microsoft Dynamics GP to Microsoft Dynamics CRM Scribe publisher issues
Posted Friday, January 27th, 2012
While setting up an integration for a client, it became apparent that there is a problem with the MSGP Publisher that will only allow you to publish messages to the scribepublisherqueue for specific tables. After adding a table to the publisher and choosing options, I tried to save the publisher and it threw the error “Error when setting up GP Publisher: Sequence contains more than one matching element”. I never did figure out what that meant. After doing my diligence and checking the knowledgebase and forums on Open Mind to no avail, Scribe support was contacted for some assistance. They informed me that there is a known issue with many tables not being accounted for by the publisher and that it is a defect and there is no fix at this time. This left me with no resolution or workaround (thanks Scribe!) While Scribe gave us what they considered an acceptable answer to the problem, it didn’t solve anything for our client’s integration which left us to figure out one on our own. Here is what I came up with:
First, create a query publisher for each table in GP that you want to push data from into CRM. This publisher generates XML files that enter the scribe message queues just like the MSGP Publisher does. Next, create the DTS mappings in the workbench from the queue generated XML to the Microsoft Dynamics CRM adapter for the desired entity. After that, set up a query integration process in the console that will process the messages using your DTS. Turn it on and you are ready to go…
If you choose to use this process, there is one BIG issue that you will need to take into account. GP has some weird processes where it will fire updates on tables based on many different criteria (like stored procedures, linking of records through lookups, etc…) that cause updates to the tables even when no changes have been made to records in the table your query publisher is firing on. If you leave the integration as is, it will work, but it will process thousands of unnecessary rows constantly and fill up your execution log in a matter of days as well as chew up valuable server resources. This makes it necessary to make changes to your new query publisher process. First – you will need to set up a ‘comparison’ snapshot to limit publishes based on a key. You can do this by using a unique identifier such as customer number or index and a datetime field. If the table doesn’t have something like this already, you will have to come up with another work around. A good solution in this situation is to do what I did and create a shadow table for the table you are using and set the query publisher to look at that table instead of the original table. Create a SQL trigger on the original table (firing on update, insert and or delete) that will populate the shadow table with the unique identifier and datetime stamp. See code below for trigger. Second – you will need to change your source configuration to point at the shadow table and join out to the original table to retrieve the data for your XML file. You don’t have to redo the mappings or XML document if you don’t pull any fields from the shadow table. Turn your integration back on and you should be set!
Side note: If you are using the GP Publisher for the tables that it actually does support, you may still need to address duplicate entries for the same rows being processed for the same reason as mentioned above about GP processes. To fix this issue, you will need to modify the triggers that Scribe puts on the tables to exclude rows that already have a record in the scribepublisherqueue table. If you do modify these triggers, keep in mind that each time you modify the publisher settings in the console it can update the triggers back to default and erase your changes, so you will need to stay on top of that as well. Good luck with your integrations!
USE [CMS]
GO
/****** Object: Trigger [dbo].[trig_scribeTechDateModified] Script Date: 01/27/2012 15:55:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: ibis inc
– Create date:
– Description: for scribe integration process, holds modified date of record
– =============================================
CREATE TRIGGER [dbo].[trig_scribeTechDateModified]
ON [dbo].[SVC00100]
AFTER INSERT,UPDATE
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
declare @moddate datetime, @techid nvarchar(11)
select @moddate = GETDATE(),@techid = i.TECHID from inserted i
IF NOT EXISTS(SELECT TECHID FROM dbo.svc00100datemod where techid = @techid)
BEGIN
INSERT INTO dbo.svc00100datemod (TECHID, DATEMODIFIED)
SELECT @techid, @moddate
END
ELSE
BEGIN
UPDATE dbo.svc00100datemod
SET datemodified = @moddate
WHERE techid = @techid
END
END
GO
Tags: Scribe Data Integration
Posted in Data, Dynamics CRM, Dynamics GP, Integration, Scribe Insight, SQL Server, SQL Statements
Jeremie Wride
Need Expert Help With Microsoft CRM?
Get the support you need to effectively manage your business.
Learn more about our Microsoft CRM support options
