Remote queries and linked servers
Okay so here's what I think you need to do. I'm assuming since you are already using integration services you have both of your data sources set so I'm not going into detail on that. If you have any questions as to how to properly set those up I can help you to. So the first thing you need to do is create a new package in your Integration services solution. Then you need to create a dataflow task. You will find that in your You toolbox like so .



Once you have done that open the DataFlowTask and Create a new OLEDB Connection. Once again found in the toolbox.


Open that Up and set up your SQL command this will run locally on the machine and will therefore suffer no linked query costs.

Next Set up your OLEDB command the portion that will execute a SQL command with the result Set. First Select OLEDB Command from the toolbox.


Connect your OLE Connection to You OLE Command.


Next you create a stored procedure to handle this update. Its a fairly simple procedure to make it will ultimately look like this.

CREATE PROCEDURE UPD_CRM_TableName(
    @ID int,
    @Field int)

UPDATE TableName SET 
@Field = Field

WHERE @ID = ID


Then open you OLEDB command and select the appropriate connection in the connection tab.


Go to the Component properties tab click on SQL Command and the appropriate values to execute your stored procedure.



Then go to column mappings and make your parameters match you columns and you will be good to execute the package. Note that the update stored procedure will be sent to the server with the appropriate parameters required to update the table eliminating the need for your costly linked query.



Setting this up for any update you command will be incredibly easy to set up and will basically run as fast as your hardware and network can keep up. Make sure your integration services box has plenty of memory as that is where all of it's operations are executed. If you have any questions feel free to let me know.