Easysoft Blog

Wed, 12 April 2017

Using SQL Server Integration Services (SSIS) to Populate QuickBooks Records

The QuickBooks ODBC Driver allows you to insert data into QuickBooks by using an SSIS package.

Before you can use the QuickBooks ODBC Driver to connect SSIS to QuickBooks, you need to configure an ODBC data source. An ODBC data source stores the connection details for the target database (e.g. QuickBooks) and the ODBC driver that is required to connect to it (e.g. the QuickBooks ODBC driver).

You configure ODBC data sources in ODBC Administrator, which is included with Windows. To run ODBC Administrator, in the Windows Run dialog box, type:

%windir%\syswow64\odbcad32.exe

Use ODBC Administrator to create a QuickBooks ODBC Driver data source:

  1. 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 these fields Easysoft ODBC-QuickBooks Desktop Driver DSN Setup dialog box:
    SettingValue
    DSNQuickBooks Desktop
    Connection ModeSame As QuickBooks
    Application NameEasysoftQuickBooksODBC.
    Application IDEasysoftQuickBooksODBC.
    QB EditionSet this to be the same as QuickBooks Desktop. For example, if you have the US version of QuickBooks, set QB Edition to US.

Create the SSIS project:

  1. Create a text file named billingrate.txt with these contents:
    Name,FixedBillingRate
    "BR1A",5.00
    "BR1B",10.00
    "BR1C",15.00
    "BR1D",20.00
    "BR1E",25.00
  2. Start QuickBooks Desktop if it is not already running.
  3. In SSIS, create a new project named "QuickBooksBillingRate".
  4. Drag a Data Flow Task from the SSIS Toolbox to the Control Flow.
  5. Name the Data Flow Task "CreateBillingRate".
  6. Double-click "CreateBillingRate".
  7. In the Data Flow tab, double-click a Flat File Source. Name the Flat File Source "BillingRate".
  8. Double-click "BillingRate".

    The Flat File Source Editor dialog box is displayed.

  9. Choose New.

    The Flat File Connection Manager Editor dialog box is displayed.

  10. Browse for billingrate.txt. In the Text qualifier box, type ".
  11. In the left hand pane, choose Columns. Choose OK.
  12. In the Data Flow tab, double-click ODBC Destination. Name the ODBC Destination "BillingRateTable".
  13. Drag the blue arrow from BillingRate to BillingRateTable.
  14. Double-click "BillingRateTable"

    The ODBC Destination dialog box is displayed.

  15. Choose New.

    The Configure ODBC Connection Manager dialog box is displayed.

  16. Choose New.

    The Connection Manager dialog box is displayed.

  17. Choose your QuickBooks ODBC driver data source from the Use user or system data source name list. Choose OK.
  18. In the ODBC Destination dialog box, type "BillItemLine" in the Name of the table or the view box.
  19. QuickBooks prompts you to whether to allow the QuickBooks ODBC driver to access your QuickBooks data. In the QuickBooks - Application with No Certificate dialog box, choose "Yes, always allow access even if QuickBooks is not running". Choose "Allow this application to access personal data such as Social Security Numbers and customer credit card information". Choose Continue.

    The Confirm to Proceed dialog box is displayed.

  20. Choose Yes.

    The Access Confirmation dialog box is displayed. Choose OK.

  21. In the Mappings pane, map each input column to a destination column that has the same name. Choose OK.
  22. Choose the Control Flow tab, and then choose Start.

    The BillingRate table is populated with the new records.