Advanced ODBC Source - IM 11

Recently I had pulled my hair out with the issue of using Advanced ODBC in Integration Manager 11.   A client we were performing an upgrade to GP2010 for had an integration setup to pull in data from a CSV file and the update did not seem to work correctly for this source and had to be updated again.

An external system was creating unchangeable CSV files which was then used to create sales transactions in Dynamics GP 2010 The nature of the text file meant that a grouping of data was required for the header source and a sum of an amount column.

Sample file
InvoiceNo, description, Amt1,Amt2
100,sales 1,100.00,0.00
100,sales 1,55.00,0.00
101, sales 2, 88,76,10

The result of previewing the file should return the following results

InvoiceNo, description, Net,VAT
100,sales 1,155.00,0.00
101, sales 2, 88,76,10

Using the Advanced Query I was unable to build a query with the Query Builder.  The advise is to not bother if you are pulling from a text driver. 

I have found the following rules apply
1. Simply enter the query in the SQL Statement field
2. save changes and then preview

ERROR: System.Data.SqlClient.SqlError: Procedure 'taAnalyticsDistribution' expects parameter '@I_vDOCNMBR'

When you use Integration Manager for Microsoft Dynamics GP to run a General Journal integration with Analytical Accounting data mapped, you receive the following error message: ERROR: System.Data.SqlClient.SqlError: Procedure 'taAnalyticsDistribution' expects parameter '@I_vDOCNMBR'

Resolution: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYYMYQKSYMXOSOOUMLVPZRNUSTZQRSNZUXRVTRPLZRSWRPMUYR

Integration Manager 10 and Analytical Accounting

Integration Manager 10 using the Dynamics GP eConnect adaptor GL Journal, is able to import Dynamics GP Journals with Analytical Accounting Dimension Codes. You must specify the journal number however in your source file to prevent issues. Other adaptors such as Sales Journals for SOP also have this ability. I know this was a problem before and really relieved that that this is available

Integration Manager Error on 64bit Windows 2008 server

Running integration manager for payable transactions import we get the following error:

"DOC 1 ERROR: Unable to cast COM object of type
'MSScriptControl.ScriptControlClass' to interface type
'MSScriptControl.IScriptControl'. This operation failed because the
QueryInterface call on the COM component for the interface with IID
'{0E59F1D3-1FBE-11D0-8FF2-00A0D10038BC}' failed due to the following error:
Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A
(TYPE_E_CANTLOADLIBRARY)).
DOC 2 ERROR: Unable to cast COM object of type
'MSScriptControl.ScriptControlClass' to interface type
'MSScriptControl.IScriptControl'. This operation failed because the
QueryInterface call on the COM component for the interface with IID
'{0E59F1D3-1FBE-11D0-8FF2-00A0D10038BC}' failed due to the following error:
Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A
(TYPE_E_CANTLOADLIBRARY)).
Integration Failed"

To solve run the following command

RUN CMD AS ADMINISTRATOR
regsvr32 C:\Windows\SysWOW64\MSScript.ocx