SSIS Tutorial: How to import data from Salesforce into SQL Server

SSIS Tutorials
,
1

Introduction

If you need to load Salesforce data into SQL Server for reporting, analytics, or backups, using SSIS is a practical and scalable solution. With the help of the ZappySys SSIS PowerPack, you can connect to the Salesforce API and easily extract, transform, and load (ETL) your data without writing complex code.

In this tutorial, you’ll learn how to import data from Salesforce into SQL Server using SSIS, the Salesforce Source, and the Upsert Destination component, both included in the ZappySys SSIS PowerPack.

Prerequisites

  • Download and install the ZappySys ODBC PowerPack.
  • A Salesforce account with valid credentials
  • SQL Server and SQL Server Data Tools (SSDT)

Steps

Step 1: Configure the Salesforce Source

  1. In the Control Flow, drag a Data Flow Task, then double-click to open the Data Flow.

  2. Drag the Salesforce Source component from the SSIS Toolbox into the Data Flow canvas.

  3. Double-click the Salesforce Source to create a new connection.

  4. Enter your Username, Password, and Security Token. If you don’t have the token, follow this guide.

    Salesforce connection basic
    Salesforce connection basic663×583 28.8 KB

  5. Alternatively, you can use OAuth 2.0 for authentication. Follow this article to obtain your client ID and secret.

    Salesforce connection OAuth
    Salesforce connection OAuth623×641 39 KB

  6. If you encounter an error related to the PKCE Code Verifier, enable the PKCE Code Verifier option under the Advanced tab.

    Enable PKCE option
    Enable PKCE option623×636 27.9 KB

  7. In the Salesforce Source, select the object (table) you want to extract, and click Preview to validate the data.

    Preview Salesforce data
    Preview Salesforce data888×605 38.1 KB

  8. You can also write a custom query to filter, group, or limit your results.

    Preview Salesforce query data
    Preview Salesforce query data888×605 55.8 KB

Step 2: Configure the Destination Component

  1. Drag and drop a destination component. In this example, we’ll use the Upsert Destination for both insert and update operations.

  2. Connect the Salesforce Source to the Upsert Destination, then double-click it.

  3. Choose the appropriate action.

  4. Create or select a connection to SQL Server.

  5. Check or uncheck the options insert and update

  6. Select the target SQL Server table and optionally preview its data.

  7. Ensure that the column mappings are correct (they will auto-map if the names match).

  8. Define at least one key column for the update check.

  9. In the Advanced tab, you can enable features like duplicate detection and more.

  10. Click OK to save.

    Upsert destination configuration
    Upsert destination configuration958×646 64.1 KB

  11. (Optional) Add extra destinations to log or analyze the number of inserted/updated records for monitoring purposes.

Step 3: Run the Package

  1. Save the SSIS package.

  2. Click Start or Debug to run the package.

  3. The Salesforce data will be imported and/or updated into your SQL Server database.

    Final Result
    Final Result443×372 26.3 KB

Conclusion

With the ZappySys SSIS PowerPack, importing data from Salesforce into SQL Server is efficient and intuitive. Whether you’re building daily ETL pipelines or running a one-time migration, this solution offers flexibility, speed, and low-code simplicity.

Use this method to centralize your Salesforce data for reporting, integrate it with other systems, or back it up for compliance and disaster recovery.

Visit our official page to explore more connectors, powerful automation features, and real-time data integration tools. Start building smarter, faster, and more scalable solutions today with ZappySys SSIS PowerPack.

References

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.