How to build SSIS package to UPSERT/DELETE data from Salesforce to SQL Server database?

I need to pull data from Salesforce to a SQL Server 2017 instance (one way) using SSIS+ (third party - CozyRock).

Now the issue is to retrieve the data and not duplicate it in the SQL Server database?

I was wondering also if I can build a button on Salesforce that make a stored procedure or job to run this SSIS package, is this possible?

I've checked this from Andy Leonard post but haven't gotten the solution yet.

Short Answer.

In order to achieve this, we need a field to track the last time a record was updated and the following.

Step 1:

• Set Sources:
  • Source from where the data comes. Set it as Source.
  • Source where the data will be stored. Set it as a Destination.

Step 2:

• Sort Data streams by their Id field.

Step 3:

• Merge Join, Merge them using a full outer join.
• First, select the Source field and secondly the Destination fields.
• Rename the output with Source_ prefix to the source fields and Destination with Destination_ prefix.

Step 4:

• Use a conditional split to split the records by change type.
• The logic in here is, use those mandatory fields in the record (E.g. Id).
• First conditional split:
  • New Records: !ISNULL([Source_Id]) && ISNULL(Destination_ID)
  • Deleted Records: ISNULL([Source_Id]) && !ISNULL(Destination_ID)
  • Set default ouput as Existing Records.

Step 5:

• Insert New Records: use an ODBC/OLE Destination and do the corresponding set up and mapping.

Step 6:

• Update Records: use another conditional split to find which reports have been updated.
• Use this logic inside the conditional split: Updated Values: ([Source_Id == Destination_Id])&&(([Source_LastUpdate != Destination_LastUpdate]))
• Use a OLE Command: set the connection and build an update query in properties and map it accordingly.

Step 7:

• Delete Records: use an OLE DB Command to delete the records which Id = Destination_Id

Related Documentation