FAQ

SSIS Integration Toolkit for QuickBooks

Frequently asked questions (FAQ) on Development, Licensing, and Deployment

QuickBooks

Does your toolkit support QuickBooks Desktop version?

Yes, as of v3.0 our SSIS Integration Toolkit for QuickBooks supports integration with QuickBooks Desktop.

Development

How do I upgrade the software? What precautions should I take?

We always recommend keeping our software up-to-date. There are new features and enhancements along with bug fixes that are implemented into our products every release. In order to upgrade a licensed system to the latest software release, you need to make sure that you have up-to-date software maintenance for that particular license, which you can find out by checking the Maintenance Expiry Date after launching the KingswaySoft License Manager program. You need to make sure that your maintenance date is greater than the release date of a particular version that you want to upgrade to. In case that your software maintenance has expired, you can reach out to us for a renewal quote, we will be happy to assist you. Note that software maintenance is only applicable to perpetual licenses, not subscription licenses, as software maintenance is always included in subscription license, in which case you can upgrade to any version as long as your subscription has not expired. For the free developer license, you have the same freedom to upgrade, but we typically recommend you stay with that same version that you use for your production environment to maintain the maximum compatibility when you deploy SSIS packages.

Once you have confirmed you have an up-to-date maintenance that qualifies you for the upgrade, then you can proceed with the upgrade process. Best practices would be to uninstall the old version before installing the new version (our software does support in-place upgrade without having to uninstall first, but there are cases the in-place upgrade doesn't clean up the old version properly). We usually try to maintain maximum backward compatibility so your packages will continue to work after the upgrade. However, you will want to pay attention to any Breaking Changes that may have been introduced between versions, in which case you may need to make changes to your SSIS packages to work with the new version that you are upgrading to. It is highly possible that those Breaking Changes (if any at all) may not even affect you, in which case you do not need to do anything with your packages to have them work with the newer version.

We generally recommend you try out our new release in a DEV or test environment before implementing the new version in your production environment.

Information on any Breaking Changes can be found on our Change Log page. Some of those changes can be addressed by simply refreshing the component, while the others may require some additional efforts.

All those Keys and Tokens on the Connection Manager are confusing me, how do I connect to QuickBooks Online?

Base URL:
If you are trying to manipulate data on your production environment use url https://quickbooks.api.intuit.com/v3
If you have a developer account and wish to test the toolkit on your Sandbox environment use url https://sandbox-quickbooks.api.intuit.com/v3

Consumer Key & Consumer Secret:
The Consumer Key and Consumer Secret Tokens come from the vendor's application. In this case, KingswaySoft is the vendor, and our App is the SSIS Toolkit for QuickBooks. See below table to determine what values to use. For example, to pull data from a production instance of QuickBooks Online, use Consumer Key value of qyprdOVqB6ncSPWx5nFoYfAr6WDJw7 and a Consumer Secret of iU9uM30wPzl9lD0kfN1Vcjk2e2byV4D1PbLj8eD2.
If you have a developer account and wish to create your own 'App' to run with our toolkit, it should work also.

Environment Consumer Key Consumer Secret
Development qyprdbH39u2vFU6bYhI0Vg5TDh0PNQ QnwBOM1gNSYhCamBEn5XWiT1L78PBXizTVLPuOC1
Production qyprdOVqB6ncSPWx5nFoYfAr6WDJw7 iU9uM30wPzl9lD0kfN1Vcjk2e2byV4D1PbLj8eD2

Access Token & Access Token Secret:
The Access Tokens are the tokens that you get from QuickBooks when you authorize the toolkit to access your QuickBook company's data. The easiest way to obtain Access Tokens is to go to Intuit's OAuth Playground for IPP Developers.

I am having problems with the Update action. I get the following error message: Detail=Stale Object Error : You and John Doe were working on this at the same time. John Doe finished before you did, so your work was not saved.

This error occurs when you did not map the SyncToken field to a proper value. If you do not know what the value is, you may need to first perform a Read operation in an upstream component, merge your updates, then feed that into the Update component.

Details for the Update action's Required fields

  • Id - This is the Id to the record that you would like to update
  • SyncToken - This is the Version number of the entity.
  • sparse - This is a boolean value. True = partial update of fields for the record. False = full update of the record.
I am having problems writing to fields that expect a Json object. I get a similar error to this: Could not parse the Json object in field 'PrimaryPhone' Newtonsoft.Json.JsonReaderException: Additional text encountered after finished reading JSON content

This error may occur when your input data contains double quotes (") in the Json object. There may be problems trying to parse the input as a Json Object. Try using single quotes instead.

E.g. {"MyField": "MyValue"} ==> {'MyField': 'MyValue'}

How do I maximize the throughput when writing data to QuickBooks Online?

Depending on your deployment, the strategy of optimization can be a little different.

Beware that QuickBooks Online sets thresholds on the number of services call you can perform in a given time period. See Intuit's Developer portal for further details

  • You may consider using BDD Functionality to write data to your server in a parallel fashion.
What else do I need to do if I want to use the BDD? Is there anything else I should be aware of?

To make full use of the BDD, you need to increase the connection limit that is imposed by the Microsoft .NET framework, which is a maximum of 2 connections per host (e.g. server) for service calls.

In order to overwrite this limit, you must modify DTExec.exe.config and DtsDebugHost.exe.config files under DTS\binn folder by adding the following connectionManagement section.

<configuration>
  ...
  <system.net>
    <connectionManagement>
      <add address="*" maxconnection="100"/>
    </connectionManagement>
  </system.net>
</configuration>

The above configuration allows up to 100 connections per host at the same time. You may change the number based on your needs.

If you are running a 64-bit system, you must make changes to the files under "Program Files" and "Program Files (x86)" folders. 

When you use the BDD functionality, there are a couple of things that you should be aware of. 

  • You may not want to use too many BDD distributions in your data flow tasks, as it could overload your server. 
  • If you use the BDD component, it uses SSIS internal buffer to manage the distribution, which is a preset size defined at the data flow task level. The default buffer size is 10,000 rows, which means BDD only starts to distribute incoming rows after one branch has used up the buffer. For instance, the BDD component's second output will not receive anything if you have less than 10,000 records (9,947 records actually), and the third output will not receive anything until you have more than 19,894 records. If you are working on a small load, you must change the data flow task's DefaultBufferMaxRows property and give it a smaller number so that the BDD distribution happens sooner. To overcome this limitation you can implement BDD functionality using the SSIS Productivity Pack as discussed in this blog post.
  • SSIS has an undocumented limitation on the number of active buffers for each data flow task, which is 5. Because of this limitation, you should not have more than 5 BDD branches in one data flow task, as doing so will not provide any performance benefits on top of having 5 BDD branches. If you want to use more than 5 BDD components, you would need to split your source data into multiple data flow tasks, and have up to 5 BDD components in each data flow task. For instance, you can have 3 data flow tasks that write to 5 destination components simultaneously, which gives you a total of 15 concurrent threads using 15 BDD branches in total.

Licensing

Do I need a trial license to evaluate the software? 

You do not need to acquire a license if you just want to test out the software functionality within the development tools (SSDT-BI, BIDS - Business Intelligence Development Studio, or Visual Studio). After installation, the software will operate under the free developer license by default. With the developer license, you can create and develop data flows within the development tools, including executing test loads without requiring a commercial license. 

If you want to evaluate whether the software functions properly outside of the development tools (SSDT-BI, BIDS, or Visual Studio), you can acquire a trial license using the License Manager program which is installed along with the software. After you have acquired a trial license, you will be able to run SSIS packages on a scheduled basis, or from any other Windows process such as command line. The trial software will operate fully-functional for the trial period, which is generally 14 days. When the trial period expires, the software will revert back to the free developer license. 

What are the limitations of the free developer license? 

The free developer license is fully-functional within your development tools (SSDT-BI, BIDS, or Visual Studio). The main limitation is the inability to run the software outside your development tools. In addition, you should not use the free development license for production purposes (mainly production data extraction or data load).

What's the difference between subscription license and perpetual license? 

There is no functionality difference between subscription license and perpetual license. However, with a subscription license, your capability to run the software outside the development tools (such as running SSIS packages on a scheduled basis) is limited to your subscription period. With the perpetual license, you can run the designated version of software within or outside the development tools (SSDT-BI, BIDS, or Visual Studio) for as long as you want.

Deployment

When I attempt to deploy an SSIS package or when executing an SSIS Package on SQL Server Management Studio for SQL Server 2014, I receive the following error message 'Unexpected Termination' (or System.AccessViolationException).

When using SQL Server 2014, a cumulative update is required (cumulative update 2 or later, or any recent service packs). The following is the list for the download links.