Integration Error after Upgrade : Unable to cast object of type System.Int64 to type System.String

Integration Error after Upgrade : Unable to cast object of type System.Int64 to type System.String

This KB is for Internal Purpose Only for CitiXsys

Environment :

iVend 6.6 with SAPB1 9.2

Problem Statement :

Customer found in integration monitor failure screen some records were stuck from SAP to iVend and showing with below error message :
"System.InvalidCastException Unable to cast object of type 'System.Int64' to type 'System.String'.
at System.Data.DataRowExtensions.UnboxT 1.ReferenceField(Object value)
at ReplicationSBOIntegrationWinSer.IntegrationObjects.Item. c. Replicate b__2_1(DataRow s)
at System.Linq.Enumerable.WhereSelectEnumerableIterator 2.MoveNext()
at System.Linq.Buffer 1..ctor(IEnumerable 1 source)
at System.Linq.Enumerable.ToArray[TSource](IEnumerable 1 source)
at ReplicationSBOIntegrationWinSer.IntegrationObjects.Item.Replicate(Direction From, Direction To, Int32 DatasetRowID)
at ReplicationSBOIntegrationWinSer.Integrator.MainStart(DateTime LastRetryAt, Boolean Retried)"

Symptoms : 

Not Applicable

Resolution/Work Around :

We have checked and verified CXSIntegration Database for the table IntegrationMapping has column "IVendID" datatype was bigint and not upgraded to nvarchar. Which we have corrected using sql statement and now all failure records are re-tried and integrated successfully from SAP B1 to iVend.

Alter PROCEDURE "CXSINTEGRATION"."AlterTable_Column_IntegrationMapping"
(
)
as
BEGIN   
DECLARE  ColumnCount INT:=0; 
select COUNT("SCHEMA_NAME")  INTO ColumnCount from SYS.COLUMNS WHERE  "COLUMN_NAME"='IVendID'AND "SCHEMA_NAME"='CXSINTEGRATION' 
AND "DATA_TYPE_NAME" ='BIGINT'; 
IF :ColumnCount>0 THEN          
CREATE TABLE "CXSINTEGRATION"."IntegrationMapping_New" ( "ObjectID" int NOT NULL,
"IVendID" nvarchar(1000) NOT NULL,"SBOID" int NOT NULL,
"DBName" nvarchar(100) NOT NULL, PRIMARY KEY ( "ObjectID","SBOID" ,"DBName" ) ); 
INSERT INTO "CXSINTEGRATION"."IntegrationMapping_New"(SELECT * FROM "CXSINTEGRATION"."IntegrationMapping");
DROP TABLE "CXSINTEGRATION"."IntegrationMapping";
CREATE TABLE "CXSINTEGRATION"."IntegrationMapping" ( "ObjectID" int NOT NULL,"IVendID" nvarchar(1000) NOT NULL,"SBOID" int NOT NULL,
"DBName" nvarchar(100) NOT NULL,PRIMARY KEY ( "ObjectID" ,"SBOID" ,"DBName" ) );                          
INSERT INTO "CXSINTEGRATION"."IntegrationMapping"
(SELECT * FROM "CXSINTEGRATION"."IntegrationMapping_New");                         
DROP TABLE "CXSINTEGRATION"."IntegrationMapping_New"; 
END IF ; 
END ;

Recommendations /Suggestion :

It should be done only by Support team as it includes SQL Queries and depends CXSIntegration Integration mapping data.