SQL2016
Scenario
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
As per my understanding when records get inserted from SQL1.table1 to SQL2.table1 then only those records should be inserted to a temporary table sql2.table1temp.
For this choose sequence container,set transaction option as required.Then place 2 data flow tasks inside the sequence container.Those two data flow tasks are connected through precedence constraints.Click the first data flow tasks,place oledb source and configure it (connect to SQL1.table1) and then place oledb destination and configure it(connect to SQL2.table1).In 2nd data flow task ,place oledb source place oledb source and configure it (connect to SQL2.table1 and then place oledb destination and configure it(connect to sql2.table1temp).Then run the package.Sequence container make sure that both the tasks run successfully if first one fails it will be rollback. So basically one data get inserted from SQL1.table1 to SQL2.table1 successfully then only those records would be inserted to SQL2.table1temp