Help Manual

SSIS Integration Toolkit for QuickBooks Help Manual

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 2017
    • 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 2017 or 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 General tab of the QuickBooks Connection Manager allows you to specify connection string settings. It supports connections to both QuickBooks Online and QuickBooks Desktop. In order to connect to QuickBooks for Desktop, the QuickBooks Desktop Gateway needs to be installed and running on the machine running QuickBooks for Desktop.

Deployment

The Deployment option allows you to specify what type of QuickBooks instance you want to connect to. There are two options available:

  • Online instance
  • Desktop instance
Deployment - Online

QuickBooks Connection Manager - Online

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.

Deployment - Desktop

QuickBooks Connection Manager - Desktop

Service URL

The Service URL field allows you to connect to the QuickBooks Desktop Gateway. A Service URL is made up of the machine name or IP address along with the configured port number. ex: http://MyQuickBooksMachineName:7777.

Username
The Username field is used to connect to the QuickBooks Desktop Gateway as a particular user. On the QuickBooks Desktop Gateway, you can associate users to company files on the machine. This allows you to differentiate connections to different company files on the same machine.
Password

The Password field is used to authenticate the QuickBooks Desktop Gateway user.

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. 

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 Mode
Proxy Mode option allows to specify how you want to configure the proxy server setting. There are three options available.
  • No Proxy
  • Auto-detect (Using system configured proxy)
  • Manual
Proxy Server
Using the Proxy Server option, you can provide a proxy server to connect to the QuickBooks.
Port
The Port option allows you to specify the port number of the proxy server for the connection.
Username
The Username option allows you to specify the proxy user account.
Password
The Password option allows you to specify the proxy user’s password.

NOTE:   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 page 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. 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 or QuickBooks Desktop Gateway. 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.

Source Type

You can specify how you would like to retrieve data from QuickBooks Online. There are 2 options, Entity or Query. This option is not available for QuickBooks Desktop connections as it will always use Entity.

Source Entity

If you chose Entity for the Source Type drop down (Or are using a QuickBooks Desktop connection), 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). This option is only available for QuickBooks Online connections.

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. This option is only available for QuickBooks Online connections.

QBXML Options
The QBXML option allows you to specify a QBXML filter string to only retrieve data that meets specified criteria. For example, using the <FromModifiedDate> and <ToModifiedDate> filters to query for objects modified during a specified range of dates. The best place to find further information on QBXML is to refer to the Creating Queries Chapter of the Intuit QuickBooks Programmer's Guide. This option is only available for QuickBooks Desktop connections.

Utilizing the Child Entities Output of the Source Component

QuickBooks Source Editor

In QuickBooks, some entities have child entities. If a source entity has child entities they will appear as entities in the Child Entity combo box, and you can select any you wish to output. Each child entity that is selected will add an additional output to the QuickBooks Source component, as well as additional fields related to the child entities checked.

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. You can specify a Connection Manager here to facilitate that connectivity.

Action

The Action option allows you to specify how the data should be written into QuickBooks, there are different actions types available depending on what instance type of your QuickBooks system is.

  • For QuickBooks online instance, there are four (4) action types available:
    • Create - Create new record(s) in QuickBooks online
    • Update - Update existing record(s) in QuickBooks online
    • HardDelete - Hard Delete record(s) from QuickBooks online
    • SoftDelete - De-Activates record(s) from QuickBooks online
  • For QuickBooks desktop instance, there are four (4) action types available:
    • Create - Create new record(s) in QuickBooks desktop
    • Update - Update existing record(s) in QuickBooks desktop
    • Delete - Delete record(s) from QuickBooks desktop
    • Void- Void record(s) in QuickBooks desktop
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. For example, when working with  QuickBooks online instance, it 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 an 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 Child Input of the Destination Component

QuickBooks Destination Editor

If you want to write child entities data to a QuickBooks entity, you can connect an inbound component containing child entities data to the Secondary Inputs. Once you have made a connection to a Secondary Input, you must specify the Child Entity 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 child entities data with the secondary child entities 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 Desktop Gateway

The QuickBooks Desktop Gateway is a utility used to bridge the gap between SSIS and QuickBooks for Desktop (on-premise). This is done by installing the QuickBooks Desktop Gateway on the machine running QuickBooks Desktop then connecting to it with the QuickBooks Connection Manager using "Desktop Deployment" settings.

NOTE: To install the QuickBooks Desktop Gateway, download and install the Integration Toolkit and select "QuickBooks Desktop Gateway" as the Setup Type during installation. After the installation is complete, version 13 of QBFC (The QuickBooks SDK) will be installed on the machine as well. This is a mandatory component for the QuickBooks Desktop Gateway to function. If it fails to install or you wish to uninstall the SDK, you can manually run the installer at: "C:\Program Files (x86)\KingswaySoft\SSIS Integration Toolkit for QuickBooks\QBFC13_0Installer.exe" for 32-bit installations or "C:\Program Files\KingswaySoft\SSIS Integration Toolkit for QuickBooks\QBFC13_0Installer.exe" for 64-bit installations.

The QuickBooks Desktop Gateway has 3 pages of settings:

The settings for the QuickBooks Desktop Gateway are saved in a file by the name of "QbDesktopGatewayByKingswaySoft.conf" in the same directory as the gateway executable. After configuring the settings, use the "Start", "Stop", and "Restart" buttons to manage the gateway.

General page

The General page allows you to specify general connection properties to QuickBooks Desktop.

QuickBooks Desktop Gateway

Port Number
The Port Number option allows you to specify the port number of the QuickBooks server.
Mode
The Mode option allows you to specify the QuickBooks database connection mode. There are three options available:
  • Default
  • Single-User
  • Multi-User
Allow IP Addresses
The Allow IP Addresses option allows you to specify the IP Addresses of the users who are allowed to connect to QuickBooks server.

Security page

The Security page allows you to specify security properties to QuickBooks Desktop.

QuickBooks Desktop Gateway - Security Page

Users
The Users grid allows you to add and remove users. When adding a new user, you have the option to associate a QuickBooks company file to this user. If no company file is specified then the user will access whatever company file is open at the time of the request. This makes differentiating connections to different company files on the same machine possible while adding an additional layer of security.
Certificate
To enable https you will need to use a custom certificate and provide a path to the certificate file along with the password. 

Advanced Page

The Advanced page allows you to specify advanced connection properties to QuickBooks Desktop.

QuickBooks Desktop Gateway - Advanced

Enable Logging
When enabled, this option will log requests made to the QuickBooks Desktop Gateway and log them in the file specified by Path to Log File.
Path to Log File
The file to log requests to.
Paging Cache Lifespan
This option is used for reading operations that page. It specifies how long the nextPageTokens and the block of records they point to will be available for in minutes. 

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.