{"id":1760,"date":"2017-09-04T14:09:18","date_gmt":"2017-09-04T14:09:18","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=1760"},"modified":"2023-08-22T17:25:53","modified_gmt":"2023-08-22T17:25:53","slug":"export-data-from-salesforce-to-sql-server-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/export-data-from-salesforce-to-sql-server-using-ssis\/","title":{"rendered":"How to read Salesforce data in SSIS – Export to SQL Server"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a><\/p>\n

In this tutorial, you will learn how to read data from Salesforce to SQL Server\u00a0using SSIS<\/acronym>\u00a0Salesforce Source<\/a>.<\/p>\n

In this article, we will extract Salesforce contacts and import them into a SQL Server database table. Also, you will learn how to do basic filtering on the contacts and get only a subset of them. Finally, we will briefly touch some additional advanced options of Salesforce Source component. Namely, you will learn how to convert\u00a0datetime\u00a0columns to different formats.<\/p>\n

To achieve our goal we will use Salesforce Source SSIS component. Under the hood, it uses Salesforce API<\/a> to make calls to, but you don’t have to be concerned about that:<\/p>\n

\n\n\n\n
\"\"<\/a><\/td>\nSalesforce Source Connector<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

Prerequisites<\/span><\/h2>\n
    \n
  1. SSIS designer installed.\u00a0Sometimes it is referred as BIDS or SSDT (download it from Microsoft site<\/a>).<\/li>\n
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\n
  3. A Salesforce account.<\/li>\n
  4. Any SQL Server database.<\/li>\n
  5. ZappySys SSIS PowerPack<\/em>\u00a0installed<\/a>.<\/li>\n<\/ol>\n

    Step-by-Step\u00a0\u2013 Read Salesforce data in SSIS<\/span><\/span><\/span><\/h2>\n

    Get Salesforce Security Token<\/h3>\n

    The first thing you will need to do to sync Salesforce with SQL Server is to get the Salesforce Security Token. You will need it for Salesforce Source Connector<\/em> to connect to your Salesforce account:<\/p>\n

      \n
    1. Log in to your account using\u00a0https:\/\/login.salesforce.com<\/a>.<\/li>\n
    2. On the top right corner, press on your profile picture and click Settings<\/span>:\n
    3. On the top left corner, under My Personal Information\u00a0<\/em>select Reset My Security Token<\/span>\u00a0menu item:\n
    4. In the main window area click Reset Security Token<\/span>\u00a0button:\n
    5. Check your email for a new Security Token:\n
      \n
      \"Getting<\/a>

      Getting new Salesforce Security Token<\/p><\/div>\n<\/div>\n<\/li>\n<\/ol>\n

      Import Salesforce data into SQL Server<\/h3>\n

      Once you have Security Token, you can start getting contacts from Salesforce and load them into SQL Server database table:<\/p>\n

        \n
      1. Open SSIS designer.<\/li>\n
      2. Create a new SSIS package and drag a\u00a0Data Flow Task<\/em>\u00a0into the\u00a0Control Flow<\/em>\u00a0from the SSIS Toolbox:\n
      3. Open\u00a0Data Flow Task<\/em>\u00a0and then add Salesforce Source\u00a0<\/i>from SSIS toolbox:\n
      4. Start modifying Salesforce Source<\/em> component and then press New<\/span>\u00a0button to create a new Salesforce Connection<\/em>:\n
      5. In a new window enter your Salesforce credentials and Security Token<\/em> you have in your email. In advanced options group, you can select the environment in which the connection will work. Most likely, you want to use Production<\/span>, so just select it in Service Type<\/em> option.
        \n<\/a><\/p>\n
      6. Once you finished creating Salesforce Connection<\/em>, select Contact<\/span>\u00a0as table you want to get data from. Be sure to have Table<\/span>\u00a0option selected in Access Mode<\/em>. Also, below you can specify the size of the batch and the maximum number of rows (contacts in our case) to retrieve:\n
      7. Close Salesforce Source<\/em> configuration and in the Data Flow Task <\/em>drag OLE DB Destination<\/em>\u00a0and connect it to Salesforce Source<\/em>.\n
      8. Open OLE DB Destination<\/em> configuration, select or create a new connection; in this example, we are using LocalDB<\/em> and tempdb<\/em> database. Then select an existing table or create a new one by pressing New<\/span>\u00a0button. Finally, select Mappings<\/span>\u00a0option to map the columns and close the window:\n
      9. Run the package. In this example we extract 16 contacts and load them into a SQL table:\n
      10. Let’s open Salesforce page and take a look at the contacts source. We have 16 contacts here as well:\n
      11. After querying destination table using SSMS<\/acronym><\/a>, we see that everything worked like it had to:\n
      12. You are done!<\/li>\n<\/ol>\n

        What if things are more complicated? Queries and Data Filtering<\/h3>\n

        Instead of accessing data using Table<\/span>\u00a0mode, you can do it in Query<\/span>\u00a0mode. This will let you write T-SQL-like queries where you will be able to filter the data, group it, select the subset of it, etc. Let’s take the same Salesforce table Contact<\/span>\u00a0and do some basic filtering on it:<\/p>\n