2 Integrating Salesforce with Excel, SQL Server, and QuickBooks
Salesforce ODBC driver for Windows, Linux and UNIX platforms. Download now.

Integrating Salesforce with Excel, SQL Server, and QuickBooks

Excel spreadsheet containing Salesforce and QuickBooks data.

Contents

Overview

This article shows how to pull, push, and merge Salesforce data with data stored in other applications.

This data integration process is managed by SQL Server Integration Services (SSIS).

Although SSIS is a SQL Server component, it can extract, transform, and load data from other data sources, and provides a graphical user interface for doing this. SSIS packages (where SSIS information is stored, for example, the data source details and transformations to run) are developed in Visual Studio, but can be run on any machine with SQL Server installed, including the free edition, SQL Server Express.

Prerequisite Software

To work through the Salesforce and QuickBooks data merge, the QuickBooks ODBC driver is also required.

Prerequisite configuration

Configure a Salesforce ODBC data source

Before you can use the Salesforce ODBC driver to connect SSIS to Salesforce, you need to configure an ODBC data source. An ODBC data source stores the connection details for the target database (in this case, Salesforce) and the ODBC driver that is required to connect to it (in this case, the Salesforce ODBC driver).

You can configure a user ODBC data source, which is only available to the user who creates it, or a system ODBC data source, which is available to all users on the machine. You configure ODBC data sources in ODBC Data Source Administrator, which is included with Windows.

There are two versions of ODBC Data Source Administrator. The version of ODBC Data Source Administrator that you need to run depends on whether you have a 32-bit or a 64-bit version of SQL Server. To find out which version of SQL Server you have, connect to your SQL Server instance, and then run this SQL statement:

select SERVERPROPERTY('edition')

If you have the 64-bit version of SQL Server and want to use a linked server with the Salesforce ODBC driver, you need to run the 64-bit version of ODBC Data Source Administrator. To do this, in the Windows Run dialog box, enter:

odbcad32.exe

If you have the 32-bit version of SQL Server or want to use SSIS with the Salesforce ODBC driver, you need to run the 32-bit version of ODBC Data Source Administrator. To do this, in the Windows Run dialog box, enter:

%windir%\syswow64\odbcad32.exe

Use ODBC Data Source Administrator to create your Salesforce ODBC driver data source:

  1. Do one of the following:
    • To create a user data source, choose the User DSN tab, and then choose Add.

      –Or–

    • To create a system data source, choose the System DSN tab, and then choose Add.
  2. In the Create New Data Source dialog box, choose Easysoft ODBC-Salesforce Driver, and then choose Finish.
  3. Complete the Easysoft ODBC-Salesforce Driver DSN Setup dialog box:
    Setting Value
    DSN Salesforce
    User Name The name of your Salesforce user. For example, myuser@mydomain.com.
    Password The password for your Salesforce user.
    Token The security token for your Salesforce user, if required.

    To find out whether you need to supply a security token, choose the Test button. If the connection attempt fails with an error that contains LOGIN_MUST_USE_SECURITY_TOKEN, you need to supply one.

    Salesforce emails the security token to the email address associated with your Salesforce user account. If you have not received a security token, you can regenerate it. Salesforce will then email the new security token to you. To regenerate your security token, log in to Salesforce and then choose Setup from the user menu. Search for "security token" in the Quick Find box. Click Reset Security Token in the Reset Security Token page. When you receive the token in your email client, copy it and then paste it into the Token field.
  4. Use the Test button to verify that you can successfully connect to Salesforce.

Configure a QuickBooks ODBC data source

This section is only relevant if you want to work though the Salesforce and QuickBooks data merge.

Use ODBC Data Source Administrator to create your QuickBooks ODBC driver data source:

  1. Do one of the following:
    • To create a user data source, in the User DSN tab, choose Add.

      –Or–

    • To create a system data source, choose the System DSN tab, and then choose Add.
  2. In the Create New Data Source dialog box, choose Easysoft ODBC-QuickBooks Desktop Driver, and then choose Finish.
  3. Complete the Easysoft ODBC-QuickBooks Desktop Driver DSN Setup dialog box.
  4. Use the Test button to verify that you can successfully connect to QuickBooks.
Salesforce ODBC driver for Windows, Linux and UNIX platforms. Download now.

Example: Incremental data push from Excel into Salesforce

The SSIS package in this section compares product records in an Excel spreadsheet against a reference data set stored in an SSIS cache file. If a record is present in the spreadsheet but not in the cache, it's a new record and SSIS inserts the new record into Salesforce. If a record is present in both the cache and the spreadsheet, SSIS checks whether the record in the spreadsheet is different to the one in the cache. If the record has changed, SSIS updates the record in Salesforce.

To create this package yourself, you need to create a linked server that connects to your Salesforce instance.

Data Flow tab: Excel Products -> Check Cache -> Lookup No Match Output -> Insert Salesforce Products. Data Flow tab: Excel Products -> Check Cache -> Lookup Match Output -> Convrt Description To Unicode -> Has Description Changed -> Description has changed -> Update Salesforce Products.

Step 1: Create the Excel data

  1. Create a new Excel spreadsheet.
  2. In the first worksheet, add these sample products:
    Name Description Family
    Easysoft ODBC-Salesforce Driver ODBC driver for Salesforce, Force.com, Database.com Easysoft Data Access
    Easysoft ODBC-SQL Server Driver ODBC driver for SQL Server, SQL Azure Easysoft Data Access
  3. Save the file as a Excel 97-2003 format spreadsheet.

Step 2: Retrieve the Excel data

  1. In Visual Studio, create a new Integration Services Project.
  2. Drag a Data Flow Task from the Toolbox to the Control Flow tab.
  3. Choose the Data Flow tab.
  4. Drag an Excel Source from the Toolbox to the Data Flow tab, and then press Return.

    Excel Source is under the Other Sources list.

  5. In the Excel Source Editor dialog box, choose New.
  6. In the Excel Connection Manager dialog box, use the Browse button to locate the Excel spreadsheet that contains the records you want to push into Salesforce.
  7. In the Name of the Excel sheet list, choose Sheet1$, and then choose OK.
  8. Name your Excel Source Excel Products.

Step 3: Create the cache

  1. Drag a Cache Transform transformation from the Toolbox to the Data Flow tab, and then press Return.

    Cache Transform is under the Other Transforms list.

  2. In the Cache Transformation Editor dialog box, choose New.
  3. In the Cache Connection Manager dialog box, select Use file cache, and then enter a cache file name in the space provided. For example, mynewcache.caw.
  4. In the Columns tab, change the Index Position of the name column to 1. Then use the OK button to return to the Data flow tab.
  5. Choose the Control flow tab, and then name the Data Flow task Create Cache.
  6. Create a copy of Create Cache and name the copy Check Cache.
  7. Select Check Cache, and then press Return.
  8. In the Data flow tab, delete the Cache Transform transformation (which is a redundant copy).

Step 4: Check the cache for changes

  1. Drag a Lookup transformation from the Toolbox to the Data Flow tab, below Excel Products.

    The Lookup transformation is under the Common list.

  2. Select Excel Products and then drag the blue arrow over to the Lookup transformation.
  3. Select the Lookup transformation, and then press Return.
  4. In the Lookup Transformation Editor, in the Cache mode list, choose Full cache.
  5. In the Specify how to handle rows with no matching entries list, choose Redirect rows to no match output.
  6. Click Columns.
  7. Drag the Name column from the Available Input Columns list over to the Name column in the Available Lookup Columns list.
  8. In the Available Lookup Columns list, select the check box that's next to the Description column.

    Lookup Transformation Editor.

  9. In the Output Alias field, enter LookupDescription. Choose OK.
  10. Name your Lookup Transformation Check Cache.

Step 5: Insert new records

  1. Drag an ODBC Destination from the Toolbox to the Data Flow tab, below and to the left of Check Cache.

    ODBC Destination is in the Other Destinations list.

  2. Select Check Cache. Drag the blue arrow over to the ODBC Destination.
  3. In the Input Output Selection dialog box, in the Output list, select Lookup No Match Output. Then choose OK.
  4. Select the ODBC Destination, and then press Return.
  5. In the ODBC Destination dialog box, choose New.
  6. In the Configure ODBC Connection Manager dialog box, choose New.
  7. In the Connection Manager dialog box, choose your Salesforce ODBC data source, and then use the OK button to return to the ODBC Destination dialog box.
  8. In the Name of the table or the view list, choose Product2.
  9. Choose Mappings.
  10. Map the Name, Description, and Family columns in the Input list to the same columns in the Destination list. Then choose OK.
  11. Name your ODBC Destination Insert Salesforce Products.

Step 6: Update changed records

  1. Drag a Data Conversion transformation from the Toolbox to the Data Flow tab, below and to the right of Check Cache.
  2. Select Check Cache. Drag the blue arrow over to the Data Conversion transformation.
  3. Select the Data Conversion transformation, and then press Return.
  4. In the Data Conversion Transformation Editor, in the Available Input Columns, select LookupDescription.
  5. In the Data Type column, choose Unicode string [DT_WSTR].
  6. Name your Data Conversion transformation Convert Description To Unicode.
  7. Drag a Conditional Split transformation from the Toolbox to the Data Flow tab, below the Data Conversion transformation.

    The Conditional Split transformation is in the Common list.

  8. Select the Data Conversion transformation. Drag the blue arrow over to Conditional Split transformation.
  9. Select the Conditional Split transformation and press Return.
  10. In the Conditional Split Transformation Editor, in the Output Name box, enter Description has changed.
  11. In the Condition box, enter Description != [Copy of LookupDescription]. Then choose OK.
  12. Name your Conditional Split transformation Has Description Changed.
  13. Drag an OLE DB Command from the Toolbox to the Data Flow tab, below Has Description Changed.

    The OLE DB Command transformation is in the Common list.

  14. Select Has Description Changed. Drag the blue arrow over to the OLE DB Command.
  15. In the Input Output Selection dialog box, in the Output list, choose Description has changed.
  16. From the Project menu, choose New Connection Manager.
  17. In the Add SSIS Connection Manager dialog box, in the Connection manager type list, choose OLEDB. Then choose Add.
  18. In the Configure OLE DB Connection dialog box, in the Data connections field, choose the SQL Server instance from which you created the linked server to Salesforce. Then choose OK.
  19. Select the OLE DB Command and press Return.
  20. In the Advanced Editor for OLE DB Command dialog box, in the Connection Manager list, choose your new Connection Manager.
  21. In the Component Properties tab, in the SqlCommand field, enter the following statement: 
    update <linked_server>.SF.DBO.Product2
set Description = ?
where Name = ?
  22. In the Column Mappings tab, drag the Name column from the Available Input Columns list over to the Param_1 parameter in the Available Destination Columns list.
  23. Drag the Description column from the Available Input Columns list over to the Param_0 parameter in the Available Destination Columns list. Then choose OK.
  24. Name your OLE DB Command Update Salesforce Products.

To populate the reference cached data set, navigate to the Create Cache Data Flow task and choose the Start button. To prevent the cached data set from being updated, in the Control flow tab, turn off Create Cache. To insert new or changed records, run the Check Cache Data flow task.

Salesforce ODBC driver for Windows, Linux and UNIX platforms. Download now.

Example: Incremental data pull from Salesforce into SQL Server

Control Flow tab: Check Last Load Date -> Data Flow Task -> Update Last Load Date. <strong>Data Flow</strong> tab: Salesforce Product2 -> SQL Server Product2.

The SSIS package in this section shows how to retrieve and insert new Salesforce records into SQL Server. A SQL Server table is used to record each time the package is run. Only those Salesforce records that have been added since the package was last run are retrieved and inserted.

To create this package yourself, you will need to create a linked server that connects to your Salesforce instance.

Step 1: Create the SQL Server tables

  1. In Microsoft SQL Server Management Studio, connect to the SQL Server instance that serves the database where you want to insert your Salesforce data.
  2. Choose New Query, and then run the following SQL statements: 
    create table ControlTable (SourceObject nvarchar(50) primary key not null,
                           LastLoadDate datetime not null)
insert into ControlTable values ('Product2', 01/01/1900)
create table Product2 (Id nvarchar(18), Name nvarchar(255), ProductCode nvarchar(255),
                       Description nvarchar(max), IsActive smallint,
                       CreatedDate datetime, CreatedById nvarchar(18),
                       LastModifiedDate datetime, LastModifiedById nvarchar(18),
                       SystemModstamp datetime, Family nvarchar(40),
                       IsDeleted smallint)

Step 2: Initialise the load date

  1. In Visual Studio, create a new Integration Services Project.
  2. Choose Variables from the SSIS menu.
  3. Click the Add Variable button. button.
  4. Set the Variable settings to match those shown in the following table:
    Name Data type Value
    Product2LoadDate DateTime 01/01/1900

Step 3: Check When the Package was Last Run

  1. Drag an Execute SQL Task from the Toolbox to the Control Flow tab.
  2. Select the Execute SQL Task and press Return.
  3. In the Execute SQL Editor, in the Connection field, choose <new connection...>.
  4. In the Configure OLE DB Connection Manager dialog box, select the SQL Server instance that serves the database where you created the control table. Then choose OK.
  5. In the SQLStatement field, enter the following query: 
    select LastLoadDate from ControlTable where SourceObject = 'Product2'
  6. Choose Result Set in the left pane, and then choose Add.
  7. In the Result Name field, enter LastLoadDate.
  8. In the Variable Name field, enter User::Product2LoadDate. Choose OK.
  9. Name the Execute SQL Task Check Last Load Date.
  10. Drag a Data Flow Task from the Toolbox to the Control Flow tab, below Check Last Load Date.
  11. Select Check Last Load Date and then drag the green arrow over to the Data Flow Task.

Step 4: Retrieve the New Records

  1. Choose the Data Flow tab.
  2. Drag an OLE DB Source from the Toolbox to the Data Flow tab, and then press Return.

    OLE DB Source is under the Other Sources list.

  3. In the OLE DB Source Editor, choose New.
  4. In the Configure OLE DB Connection Manager dialog box, choose New.
  5. In the Connection Manager dialog box, choose the SQL Server instance from which you created the linked server to Salesforce. Use the OK button to return to the OLE DB Source Editor dialog box.
  6. In the Data access mode list, choose SQL command.
  7. In the SQL command text box, enter the following query: 
    select * from <linked_server>.SF.DBO.Product2 where CreatedDate > ? and
                                                    CreatedDate < ?
  8. Choose Parameters.
  9. In the Set Query Parameters dialog box, set the parameter mappings:
    Parameters Variables Param direction
    Parameter0 User::Product2LoadDate Input
    Parameter1 System::StartTime Input

    The first parameter ensures that only those Salesforce records that were created since the package was last run are retrieved. The second parameter excludes records that were added while the package is running.

    Then use the OK button to return to the Data Flow tab.

  10. Name the OLE DB Source Salesforce Product2.

Step 5: Insert the new records

  1. Drag a SQL Server Destination from the Toolbox to the Data Flow tab, below Salesforce Product2, and then press Return.

    SQL Server Destination is under the Other Destinations list.

  2. Select Salesforce Product2 and then drag the blue arrow over to the SQL Server Destination.
  3. Select the SQL Server Destination and press Return.
  4. In the SQL Server Destination Editor dialog box, choose the SQL Server instance that serves the database where you created the Product2 table.
  5. In the Use a table or view list, choose the Product2 table.
  6. Choose Mappings.
  7. Set the Mappings as follows:
    Input column Destination column
    Id Id
    Name Name
    ProductCode ProductCode
    Description Description
    <ignore> IsActive
    <ignore> CreatedDate
    CreatedById CreatedById
    <ignore> LastModifiedDate
    LastModifiedId LastModifiedId
    <ignore> SystemModstamp
    Family Family
    <ignore> IsDeleted

    Note It is possible to insert the "ignored" columns into SQL Server, however that would require a data conversion transformation to be applied to them, and that is beyond the scope of this tutorial.

  8. Name the SQL Server Destination SQL Server Product2.
  9. Select the Control Flow tab.
  10. Drag an Execute SQL Task from the Toolbox to the Control Flow tab, below the Data Flow task.
  11. Select the Data Flow task and then drag the green arrow over to the Execute SQL Task.
  12. Select the Execute SQL Task, and then press Return.
  13. In the Execute SQL Task Editor, in the Connection list, choose the SQL Server instance that serves the database where you created the ControlTable.
  14. In the SQLStatement box, enter the following statement: 
    update ControlTable
set LastLoadDate = ?
where SourceObject = 'Product2'
  15. Choose Parameter Mapping in the left pane, and then choose Add.
  16. In the Variable Name field, choose System::StartTime.
  17. In the Data Type field, choose Date.
  18. In the Parameter Name field, enter 0. Then choose OK.
  19. Name the Execute SQL Task Update Last Load Date.
  20. Choose the Start button to retrieve and insert the Salesforce data into SQL Server.
Salesforce ODBC driver for Windows, Linux and UNIX platforms. Download now.

Example: Merging Salesforce and QuickBooks data

Merging Salesforce and QuickBooks Data in SSIS.

This example shows how to use the Easysoft's Salesforce ODBC driver and Quickbooks ODBC driver to merge information stored in the Salesforce Account table and the Quickbooks Customer table. Because SSIS is used to control the merge, it's possible to change the solution and alter what data gets merged or merge different data. Salesforce and Quickbooks themselves are not changed, that is, no tables are created in either product to facilitate the merge.

The resultant merged data is written to a delimited file, which can then be imported into other applications such as Microsoft Excel. This scenario is illustrated in the image shown at the start of the article.

Step 1: Retrieve the data

  1. In Visual Studio, create a new Integration Services Project.
  2. Drag a Data Flow Task from the Toolbox to the Control Flow tab.
  3. Choose the Data Flow tab.
  4. Drag an ODBC Source from the Toolbox to the Data Flow tab, and then press Return.

    ODBC Source is under the Other Sources list.

  5. In the ODBC Source Editor, choose New.
  6. In the Configure ODBC Connection Manager dialog box, choose New.
  7. In the Connection Manager dialog box, choose your Salesforce ODBC data source from the Use user or system data source name list. Use the OK button to return to the ODBC Source dialog box.
  8. In the Data access mode list, choose SQL command.
  9. Enter the following query in the SQL command text box: 
    SELECT U1.name AS "Account Owner", A1.name AS "Account Name",
        A2.name AS "Parent Account", A1.AccountNumber AS "Account Number",
        A1.Site AS "Account Site", A1.Type AS "Type", A1.Industry AS "Industry",
        A1.AnnualRevenue AS "Annual Revenue",
        A1.BillingStreet AS "Billing Address Line 1",
        A1.BillingCity AS "Billing Address Line 2",
        A1.BillingState AS "Billing Address Line 3",
        A1.BillingPostalCode AS "Billing Address  Line 4",
        A1.BillingCountry AS "Billing Address Line 5",
        A1.CustomerPriority__c AS "Customer Priority",
        A1.SLAExpirationDate__c AS "SLA Expiration Date",
        A1.NumberofLocations__c AS "Number of Locations", A1.Active__c AS "Active",
        U2.name AS "Created By", A1.CreatedDate AS "Created On",
        A1.Description AS "Description", A1.Rating AS "Rating", A1.Phone AS "Phone",
        A1.Fax AS "Fax", A1.Website AS "Website", A1.TickerSymbol AS "Ticker Symbol",
        A1.Ownership AS "Ownership", A1.NumberOfEmployees AS "Employees",
        A1.Sic AS "SIC Code", A1.ShippingStreet AS "Shipping Address Line 1",
        A1.ShippingCity AS "Shipping Address Line 2",
        A1.ShippingState AS "Shipping Address Line 3",
        A1.ShippingPostalCode AS "Shipping Address Line 4",
        A1.ShippingCountry AS "Shipping Address Line 5",
        A1.SLA__c AS "SLA", A1.SLASerialNumber__c AS "SLA Serial Number",
        A1.UpsellOpportunity__c AS "Upsell Opportunity",
        U3.name AS "Last Modified By", A1.LastModifiedDate AS "Last Modified On"
FROM Account A1 LEFT OUTER JOIN Account A2 ON A2.Id = A1.ParentId,
     "User" U1, "User" U2, "User" U3
WHERE A1.OwnerId=U1.Id AND A1.CreatedById=U2.Id AND A1.LastModifiedById=U3.Id;

    This SQL query retrieves the information shown in the Account detail page in Salesforce.

  10. Choose Preview, and then choose OK to exit the dialog boxes.
  11. Name your ODBC source Salesforce Account.
  12. Drag an ODBC source from the Toolbox to the Data Flow tab, to the right of Salesforce Account, and then press Return.
  13. In the ODBC Source Editor, choose New.
  14. In the Configure ODBC Connection Manager dialog box, choose New.
  15. In the Connection Manager dialog box, choose your Quickbooks ODBC data source from the Use user or system data source name list. Use the OK button to return to the ODBC Source dialog box.
  16. Enter the following query in the SQL command text box: 
    SELECT CompanyName, OpenBalance AS "Open Balance", CreditLimit AS "Credit Limit",
        ListId AS "Customer List Id"
FROM Customer

    This SQL query retrieves the open balance and credit limit for the Salesforce account. This information is stored in the QuickBooks customer list.

  17. Choose Preview, and then choose OK to exit the dialog boxes.
  18. Name your ODBC source QuickBooks Customer.

Step 2: Sort and merge the data

  1. Drag a Sort transformation from the Toolbox to the Data Flow tab, below Salesforce Account.

    The Sort transformation is under the Common list.

  2. Select Salesforce Account and then drag the blue arrow over to the Sort transformation.
  3. Select the Sort transformation, and then press Return.
  4. In the Sort Transformation Editor, select the check box that is to the left of the Account Name column.
  5. Select Remove rows with duplicate sort values and then choose OK.
  6. Name your Sort transformation Sort by Account Name.
  7. Drag a Sort transformation from the Toolbox to the Data Flow tab, below QuickBooks Customer.
  8. Select QuickBooks Customer and then drag the blue arrow over to the Sort transformation.
  9. Select the Sort transformation, and then press Return.
  10. In the Sort Transformation Editor, select the check box that is to the left of the Company Name column.
  11. Select Remove rows with duplicate sort values and then choose OK.
  12. Name your Sort transformation Sort by Company Name.
  13. Select QuickBooks Customer and then drag the blue arrow over to Sort by Company Name.
  14. Drag a Merge Join transformation from the Toolbox to the Data Flow tab, below and between the Sort transformations, and then press Return.

    The Merge Join transformation is under the Common list.

    The Merge Join transformation allows data in different data sources to be merged.

  15. Select Salesforce Account, and then drag the blue arrow over to the Merge Join transformation.
  16. In the Input Output Selection dialog box, choose Merge Join Left Input from the Input list, and then choose OK.
  17. Select QuickBooks Customer, and then drag the blue arrow over to the Merge Join transformation.
  18. Select the Merge Join transformation, and then press Return.
  19. In the Merge Join Transformation editor, make sure that the Join Key check box, to the right of Account Name, is selected.
  20. In the Sort by Account Name box, select the check box in the top left corner.
  21. In the Sort by Company Name box, select the check box to the left of Open Balance, Credit Limit and Customer List Id. Then choose OK.

    Merge Join Transformation Editor.

  22. Name your Merge Join transformation Join Account Name Company Name.

Step 3: Output the merged data

  1. Drag a Flat File Destination from the Toolbox to the Data Flow tab, below Join Account Name Company Name.

    Flat File Destination is in the Other Destinations list.

  2. Select Join Account Name Company Name, and then drag the blue arrow over to the Flat File Destination.
  3. Select the Flat File Destination, and then press Return.
  4. In the Flat File Destination Editor dialog box, choose the New button.
  5. Accept the default format when prompted.
  6. In the Flat File Connection Manager Editor dialog box, in the File name box, enter the name of the text file you want the Salesforce and QuickBooks data to be written to.
  7. Make sure that the Unicode check box is selected.
  8. Choose Columns.
  9. Choose Tab {t} from the Column delimiter list.

    The default delimiter, Comma {,}, is not appropriate because some of the Salesforce data may contain commas.

  10. Choose OK to return to the Data Flow tab.
  11. Name the Flat File Destination Tab Delimited Salesforce and QuickBooks Data.
  12. Choose the Start button to retrieve and merge the Salesforce and QuickBooks data.
Salesforce ODBC driver for Windows, Linux and UNIX platforms. Download now.