Installation

To install and run SSIS Integration Toolkit for QuickBooks, your system must have the following components installed. 

  • A supported SSIS design-time or run-time, which can be one of the following:
    • SSIS 2016
    • SSIS 2014
    • SSIS 2012
    • SSIS 2008 R2
    • SSIS 2008
    • SSIS 2005

    For SSIS runtime, the installation should be done by using the corresponding SQL Server installation media (most likely in CD/DVD format), and you must select the "Integration Services" component during the installation (as shown below).

    Integration Services Runtime Selection

    Note that for SSIS runtime, SQL Server Standard Edition or above is required to run our software. SQL Server Express or Web editions are not supported due to their own limitations.

    Note that when using SQL Server 2014, a cumulative update is required (a recent service pack, such as one of the following, is more preferred) in order to run our software during runtime.

    For SSIS design-time, you should be installing the version of SSDT (SQL Server Data Tools) or BIDS (Business Intelligence Development Studio) that aligns with the SQL Server version that you plan to use for your final deployment (the runtime).

    • When targeting SSIS 2016, you would use SSDT for Visual Studio 2015 available for download at https://msdn.microsoft.com/mt186501.aspx
      • Note that this SSDT installation can be used to target SSIS 2014 or 2012 as well.
    • When targeting SSIS 2014, you can use either one of the following:
    • When targeting SSIS 2012, you can use any one of the following:
    • When targeting SSIS 2008 R2 or earlier, you would have to install the Business Intelligence Development Studio that is shipped with the installation media of the corresponding SQL Server version. 
  • .NET Framework 3.5
    • For any version before v1.1, .NET Framework 3.5 is required. For v1.1 or later, .NET Framework 3.5 is only required when you use SSIS 2005.
      • If you are using Windows Server 2003, Windows Server 2008, Windows Vista, Windows XP operating systems, you would install .NET framework by downloading it from Microsoft website.
      • If you are using Windows Server 2008 R2 or 2012 family of operating systems, .NET framework 3.5 should be installed using Server Manager program by adding .NET Framework 3.5.1 features.
      • If you are using Windows 8, go to Control Panel -> Programs and Features -> Turn Windows features on or off, then select .NET Framework (includes .NET 2.0 and 3.0).
  • Windows Installer 4.5
    • If you are using Windows Server 2008, Windows Server 2012 family of operating systems or later (including Windows Vista, Windows 7, Windows 8, Windows Server 2012 R2, Windows Server 2016), you do not need to do anything since the latest Windows Installer has been installed by the operating system.
    • If you are using Windows Server 2003 family of operating systems (including Windows XP), you should install Windows Installer 4.5 by downloading it from Microsoft website.

When you have confirmed that your system satisfies the above prerequisites, you can navigate to the KingswaySoft website at http://www.kingswaysoft.com to download the installation package. In the download page, you will find two download links. One for x86 systems and one for x64 systems. Make sure to choose and download the right bit for your system.

Download options

After you have downloaded the package, you can install the software by following the instructions on your screen.

Permissions

The QuickBooks Connection Manager retrieves tokens from the QuickBooks app, once you give authorization. These tokens are then stored in Registry so that once they have expired, our component will automatically request new tokens from QuickBooks to minimize user effort, and replace the existing tokens with these new ones in Registry. In order to achieve this, there must be proper permissions given to the KingswaySoft folder in Registry.

Start by opening the Registry Editor, and navigate into the following folders:

  • HKEY_LOCAL_MACHINE > SOFTWARE > KingswaySoft > SSIS Integration Toolkit for QuickBooks

Registry Folder

  • Right-click on the SSIS Integration Toolkit for QuickBooks in the KingswaySoft folder and select the Permissions option. Enable the following permissions to give yourself the permissions to modify this folder.

Give Permissions

Setting up a QuickBooks Connection using the QuickBooks Connection Manager

SSIS Integration Toolkit for QuickBooks includes a SSIS Connection Manager component to help you establish connections to QuickBooks web services.

To add a new QuickBooks connection to your SSIS package, right-click the Connection Manager area, and choose "New Connection..." from the context menu. You will be prompted the "Add SSIS Connection Manager" window, where you can select "QuickBooks (KingswaySoft)" and click "Add..." to create the connection.

New Connection

Add QuickBooks connection

Configuration

The QuickBooks Connection Manager contains the following tabs

  • Connection
  • Advanced Settings
  • More Info

Connection

The Connection tab of the QuickBooks Connection Manager allows you to specify connection string settings. QuickBooks Online utilizes OAuth authentication to gain access to data, and requires a series of keys and tokens values.

QuickBooks Connection Manager

Base URL

The Base URL field allows you to specify the URL of the QuickBooks Online API endpoint.

  • Production instances - https://quickbooks.api.intuit.com/v3/
  • Sandbox environment - Select the 'Using Sandbox' option - https://sandbox-quickbooks.api.intuit.com/v3/
Token Location

QuickBooks retrieves tokens from the QuickBooks app, once you give authorization. The tokens are stored in Registry so that once they expire, our component will automatically request new tokens from QuickBooks to minimize user effort, and replace the existing tokens with the new ones in Registry. You have two options to choose where in registry you would like to store these tokens:

  • Local Machine - HKEY_LOCAL_MACHINE
  • Current User - HKEY_CURRENT_USER
Company ID

The Company ID is an integer number that uniquely represents your QuickBooks Online company subscription or QuickBooks Sandbox Company instance. You would need to make sure you have Tokens created. To create your tokens, select the 'Edit Company Information' button. 

Edit Company information

After clicking this button, it will take you through the entire OAuth authentication process inside of the toolkit. All you need is your login information to QuickBooks and once you login, you will authorize our app to generate your QuickBooks Tokens.

Timeout (secs)

The Timeout (secs) option allows you to specify a timeout value in seconds for the connection. The default value is 120 seconds.

Test Connection

After all the connection information has been provided, you may click "Test Connection" to test if the user can successfully authenticate with QuickBooks Online.

Advanced Settings

The Advanced Settings tab of QuickBooks Connection Manager allows you to specify some advanced and optional settings for the connection.

QuickBooks Connection Manager

Retry on Intermittent Errors

This is an option designed to help recover from possible intermittent outages or disruption of service. It prevents the integration process from stopping due to temporary issues. Enabling this option will allow service calls to be retried upon certain types of failure. A service call may be retried up to 3 times before an exception is fired. Retries occur after 0 seconds, 15 seconds, and 60 seconds.

Warning: We have designed our retry feature carefully such that the retry should only occur when it is deemed safe to do so; however, in some occasions such retry service calls could result in the creation of duplicate data.

Proxy Server

Using the Proxy Server option, you can provide a proxy server to connect to the web service.

Port

The Port option allows you to specify port number of the proxy server for the connection.

Username (Proxy Server Authentication)

The Username option (under Proxy Server Authentication) allows you to specify the proxy user account.

Password (Proxy Server Authentication)

The Password option (under Proxy Server Authentication) allows you to specify the proxy user's password.

NOTE: The Proxy Password is not included in the QuickBooks connection manager's ConnectionString property by default. This is done by design for security reasons. However you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be ProxyPassword=myProxyPassword; (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString.

More Info

The More Info tabs shows some basic information about where to find support and additional information about the toolkit. In this page, you can find the version information of the toolkit.

QuickBooks Connection Manager

Adding SSIS Toolkit Components to the SSIS Toolbox

Before you can use the toolkit's data flow components, they must be added to your SSIS development environment's toolbox.

  • If you are using SQL Server 2008 R2 or earlier, you must add them to SSIS toolbox by following the instructions below.
  • If you are using SQL Server 2012 or later, you should not need to do so, as SQL Server 2012 (or a later version) automatically lists all available pipeline components by scanning the system. Under certain circumstances, your SSIS toolbox might appear empty, in which case, you should click the SSIS Toolbox button (shown below) on the top-right corner of your Control Flow or Data Flow view.

    SSIS Toolbox

Adding Data Flow Components to the SSIS Toolbox (SQL Server 2008 R2 or earlier)

In BIDS 2008 or 2005 (depending on which SQL Server version you are using), create a new data flow if you do not have one yet, and switch to the data flow page. Then right-click on the toolbox area to bring up the context menu and select "Choose Items..." option as shown below.

Choose items

You are now presented with a window called "Choose Toolbox Items". Switch to the "SSIS Data Flow Items" tab, and check the QuickBooks components from the list.

Add QuickBooks SSIS components

You should now be able to use the component by dragging and dropping a component to the design surface of your SSIS data flow task.

Using the QuickBooks Source Component

The QuickBooks Source Component allows you to read data from QuickBooks Online. The component allows you to specify a source entity to read from, and filter for desired records.

General page

The General page is where you will configure most of the setup details.

QuickBooks Source Editor

Connection Manager

The QuickBooks source component requires an active connection to QuickBooks Online. You can specify a Connection Manager here to facilitate that connectivity.

Batch Size

The Batch Size lets you specify how many records to retrieve per service call to QuickBooks Online.

Source Type

You can specify how you would like to retrieve data from QuickBooks Online. There are 2 options, Entity or Query.

Source Entity

If you chose Entity for the Source Type drop down, you can specify which entity to read from the Source Entity drop down.

Include Inactive Records

If you chose a Name list entity (e.g. Customer) from the Source Entity drop down, the Include Inactive Records checkbox is revealed. This option lets you retrieve records that have been Soft Deleted (Active field set to false).

QuickBooks Source Editor

QuickBooks Query

If you chose Query for the Source Type drop down, you can specify a QuickBooks query to read data from.
If you have an Intuit Developer account, you may want to read the Querying data documentation.

QuickBooks Source Editor

Utilizing the Line Items Output of the Source Component

QuickBooks Source Editor

In the Source Component, you can specify which type of Line items you want to read by checking off each Line Details Type. Each Line Details Type you check off will add an additional output to the Source Component, as well as additional fields related to the Detail Type checked. Line items data will then be written to the output if they match the detail type specified.

Columns

The Columns page lists all the available metadata fields for the entity that you wish to read from.

QuickBooks Source Editor

Using the QuickBooks Destination Component

The QuickBooks Destination Component is an SSIS data flow pipeline component that can be used to write data to QuickBooks Online. You may create, update, and delete records with this component.

General page

The General page of the QuickBooks Destination Component allows you to specify the general settings of the component.

QuickBooks Destination Editor

Connection Manager

The QuickBooks destination component requires an active connection to QuickBooks Online. You can specify a Connection Manager here to facility that connectivity.

Action

The Action option allows you to specify how the data should be written into QuickBooks. There are four (4) action types available.

  • Create - Create new record(s) in QuickBooks
  • Update - Update existing record(s) in QuickBooks
  • HardDelete - Hard Delete record(s) from QuickBooks
  • SoftDelete - De-Activates record(s) from QuickBooks
Destination Entity

The Destination Entity option allows you to specify which QuickBooks entity to write data to. A drop down with available entities are listed here.

Batch Size

The Batch Size allows you to specify how many entities should be written to from a single service call. QuickBooks Online has a maximum limit of 25 batched operations per service call.

Refresh Component button

By clicking this button, the component will retrieve the latest metadata from QuickBooks' web services. After clicking this button, you will receive a status message indicating how many fields have been updated, added, or deleted.

Map Unmapped Fields button

By clicking this button, the component will map any unmapped fields by matching their names with the input columns from the upstream component. This is useful when your source component has recently added more columns, in which case you can use this button to automatically establish associations between input columns and unmapped fields.

Clear All Mappings button

By clicking this button, the component will remove all field mappings. You can use this button to reset your mappings.

Hard Delete vs Soft Delete

A Hard Delete can be performed on QuickBook's Transaction entities (e.g. Invoice). A Hard Delete will permanently delete the record. If you attempt to perform a Hard Delete, and get a Operation Delete is not Supported error message, it means that the entity you specified does not support a hard delete operation. Most likely it is a Name list entity that you are attempting to hard delete.

A Soft Delete can be performed on QuickBook's Name list entities (e.g. Customer). A Soft Delete essentially performs an update on the record to set the Active field to false. If you attempt to perform a Soft Delete, and get a Property Name:Unrecognized field "Active" error message, the entity that you are trying to soft delete does not contain a field called Active, and therefore cannot be soft deleted. Most likely it is a Transaction entity that you are attempting to soft delete.

Utilizing the Line Items Input of the Destination Component

QuickBooks Destination Editor

If you want to write line items data to a QuickBooks entity, you can connect an inbound component containing line items data to the Secondary Inputs. Once you have made a connection to a Secondary Input, you must specify the type of Line you are adding. Each input must be sorted, and each input must specify the Key column that will be used as a key to match with the primary records. Matching will only perform correctly when data from all inputs are sorted in ascending order.

NOTE: If you wish to write line items data with the secondary line items input, make sure the 'Line' attribute in the primary input is not mapped. Otherwise, you will be writing line items data from 2 sources. Data from the Secondary inputs, and data from the mapped 'Line' column

Columns page

The Columns page of the QuickBooks Destination Component allows you to map the columns from upstream components to fields for the destination entity.

QuickBooks Destination Editor

In the Columns page, you will see a grid with four columns as seen above.

  • Input Column - You can select a column from your upstream component to be used as the input of the corresponding field.
  • QuickBooks Field - The field that you are writing data.
  • Data Type - This column indicates the type of value for the current field in QuickBooks. Typically you would need to pass in the value using the format indicated in Data Type column.
  • Unmap - This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.

Error Handling page

The Error Handling page allows you to specify how errors should be handled when they happen.

QuickBooks Destination Editor

There are three options available.

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to write to QuickBooks will be redirected to the 'Primary Error Output' of the Destination Component. The 'ErrorMessage' output column found in the Error Output may contain the error message that was reported by QuickBooks or the component itself.

NOTE: Use extra caution when selecting Ignore error option, since the component will remain silent for any errors that have occurred.

Using the QuickBooks Token Manager

The QuickBooks Token Manager is a utility external to SSIS that allows you to add, remove, import, and export your QuickBooks Tokens from machine to machine.

QuickBooks Token Manager

Once you have developed your integration package, and you are getting ready to deploy, this utility will assist you in getting your QuickBooks tokens from your development environment to your integration server:

  1. Open the QuickBooks Token Manager on your development machine
  2. Select the tokens you wish to export
  3. Click the 'Export' button
  4. Save the 'tokens.json' file
  5. Move the file manually to your integration server
  6. Open the QuickBooks Token Manager on your integration server
  7. Click the 'Import' button
  8. Select the 'tokens.json' file

This is the simplest way to get your tokens from your development environment to your integration server but in some cases you may need to add tokens manually, in which case simply click the 'Add' button. Adding tokens in the QuickBooks Token Manager is exactly like adding tokens in the QuickBooks Connection Manager.

You may also need to remove tokens from your machine. Simply select the tokens you wish to remove and click the 'Remove' button.

Overall the QuickBooks Token Manager is a simple and secure utility that helps make your integration easy and safe

Contact Us

If you need any further assistance with the toolkit, please don't hesitate to contact us.