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

Question

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.

Answer 1

There are two ways you can solve the duplicate issue.

1) Get only incremental data, if the table you are ingesting from salesforce have update_at timestamp, you can use that to pull only new/updated records.

2) Use MERGE instead of insert or update, it will handle the duplicates more gracefully enter link description here

yeah, you can create a button in salesforce and call a shell script , but it will be much easier to schedule the ssis job to run for every 5 mins and capture the updates from salesforce

Answer 2

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