QuickBooks Connection

Overview

QuickBooks Connection Manager is SSIS Connection Manager for establishing QuickBooks connections. Use the API to build components based on QuickBooks Connection Manager.

Setup

Use the QuickBooks Connection Manager dialog to specify properties for connecting to QuickBooks service.

Server Options

Deployment (1.6 SR-3)

Select QuickBooks deployment type. This property has the options listed in the following table.

Value Description
Online QuickBooks Online deployment.
Desktop QuickBooks deployed on premise (default).
Token file (1.6 SR-3)

Select QuickBooks Online connection token file. If token file doesn't exist, you have to create New... token.

Token password (1.6 SR-3)

Specify token file decryption password.

Server

Specify the name or IP address of the QuickBooks service.

Login

Specify login to access QuickBooks. The default for QuickBooks Online Edition: ssis.cozyroc.com

Key

Specify connection key to access QuickBooks. For QuickBooks Online Edition (QBOE), review "How to get QuickBooks Online Edition connection key?" below.

Application ID

Specify application identifier to access QuickBooks. For QuickBooks Online Edition this is mandatory parameter. The default identifier: 374225725.

Time-out (secs)

Specify the number of seconds before timing out session connect. The default value of this property is 100 seconds.

RetainSameConnection

Specify if all clients should use the same connection. This property has the options listed in the following table.

Value Description
True All clients use same connection (default).
False Every client use different connection.
Test Connection

Confirm connection manager configuration by clicking Test Connection.

Proxy Options

Host

Specify the name or IP address of the proxy server. If not specified, proxy server is not used.

Port

Specify the port number on the proxy server to use for the connection.

User name

Specify user name to access the proxy server.

Password

Specify password to access the proxy server.

How to trace the web services calls in use?

You can use the regular .NET network tracing facilities to trace the web services calls. Follow these steps to enable tracing:

  1. Go to DTS\Binn folder and open DtsDebugHost.exe.config file in your favorite text editor.
  2. Include following settings inside configuration element:
        <system.diagnostics>
          <trace autoflush="true" />
          <sources>
            <source name="System.Net" maxdatasize="1024" tracemode="protocolonly">
              <listeners>
                <add name="MyTraceFile" />
              </listeners>
            </source>
          </sources>
          <sharedListeners>
            <add
              name="MyTraceFile"
              type="System.Diagnostics.TextWriterTraceListener"
              initializeData="c:\System.Net.trace.log" />
          </sharedListeners>
          <switches>
            <add name="System.Net" value="Verbose" />
          </switches>
        </system.diagnostics>
    

initializeData attribute controls where the trace log file is saved. Now when you execute a package inside Visual Studio, "c:\System.Net.trace.log" file will contain the web services tracing information. If you want to trace when you execute packages outside Visual Studio, include the settings above in DTExec.exe.config configuration file.

Make sure you comment or remove the tracing settings after you finish testing. Otherwise this may slow down your package execution speed considerably.

How to get QuickBooks Online Token

The token setup process is organized as a wizard consisting of two steps:

  1. Step: Authorize COZYROC SSIS+ to connect to specific QuickBooks instance. If the token retrieve succeeds, the second wizard step will have the access token, secret and realm automatically populated in the second step.
  2. Step: If the automatic token retrieve fails, you can manually specify the access token, secret and realm. If all required information is populated, you can specify the token file name and password to save it.

Tips for successful access token retrieve:

  • Disable the Windows Firewall.
  • Explicitly Run As: Admin Visual Studio.
  • Try running the token retrieve from a different machine.
  • Manually get the access token using this Intuit page. Follow these steps:
    1. Enter the following parameters:

      • Consumer Key: qyprdCc0eW0p56eWWmzUBtc6rcvKfU
      • Consumer Secret: uiLUsLvwyPhStetxutjUrDcTFnmVCi5Tz0Ne6Yhz

      Press 'Get Request Token using Key and Secret' button.

    2. Press 'Authorize Request Token' and authorize access to the QuickBooks Online instance.
    3. Copy-and-paste the retrieved access token and secret in the second step of the token setup wizard.

QuickBooks Desktop Edition Gateway

The installation includes application for remote web access of QuickBooks Desktop Edition database. The application is found in COZYROC installation sub-folder Tools. The name of the application is QbConnector.exe. Before you are able to work with the data for the first time, you have to enable QbConnector application to access the data in your QuickBooks Desktop application. Please review your QuickBooks documentation how to enable third-party access to your data. Use the options below to setup the gateway.

Port

Specify network port. This port will be used by the remote clients to communicate with the server.

Log

Specify log file to trace communication. Enable only during test because it will slowdown the communication.

Mode

Specify QuickBooks database connection mode. This property has the options listed in the following table.

Value Description
Default Connect in the mode QuickBooks is already open (default).
Single Connect in single user mode.
Multi Connect in multi user mode.
Accept

Specify what remote client addresses are allowed to connect to gateway. This property has the options listed in the following table.

Value Description
Any Any remote client can connect to gateway (default).
Address Only specified remote client can connect to gateway.
Users

Use Users tab to setup credentials of users, which can access the gateway. Optionally select user-associated company database file. If database file is not specified, the user will connect to the database currently open in QuickBooks.

Security

Specify to use secure (HTTPS) protocol for communication. This property has the options listed in the following table.

Value Description
Disabled The secure communication is disabled (default).
Embedded Certificate The secure communication is enabled using the embedded X.509 certificate.
User-defined Certificate The secure communication is enabled using the user-defined X.509 certificate.
Inactive sessions close (1.6 SR-2)

Specify after how many minutes of inactivity the QuickBooks database session is closed.

DEPRECATED: How to get QuickBooks Online Edition connection key?

To get connection key to your QuickBooks Online data, follow the steps below:

  • Go to the following address: https://login.quickbooks.com/j/qbn/sdkapp/confirm?serviceid=2004&appid=APP_ID

    Make sure to replace APP_ID with the application identifier you used above.
  • A wizard will appear, which will guide you thru the setup process. Grant the access rights you will need.
  • IMPORTANT: Right before the end, the wizard will ask you for Login Security. Make sure you turn it off. Otherwise your SSIS package will not be able to execute in automated process.
  • A connection key will be generated at the end of the setup process. Copy-and-paste the connection key in the QuickBooks Connection Manager setup dialog.