SQL Server 1 on Domain 1
SQL server 2 on Domain 2
CRM Server on Domain 2
I have a data migrator that transfers live data from SQL1.table1 to SQL2.table1. When a record is created in SQL2.table1 a trigger inserts those records to a temporary table SQL2.table1temp to be processed into a CRM system
I have created a SSIS that takes an INT parameter and using KingswaySoft components, inserts that record into the CRM. This is executed from a stored procedure
Under studio management I can run everything fine, however, I want to execute the stored procedure from an 'After insert' trigger, but am running into a problem as I cannot run the SSIS as a user.
I'm sure that this is not the best or preferred way to try and run this, and would welcome any and all advice on how to accomplish my end goal. The data between both SQL server and the CRM needs to be in sync within in the shorted time possible.
I throw myself on the mercy of the clever people in here