Anexinet BI Blog: Accessing QuickBooks data via SSIS

The most direct method to access QuickBooks data outside of the application is through an ODBC driver. There are a handful of driver providers. We downloaded and tested two providers: QODBC and RBUS. Both of them seemed to have similar functionality and price. In the end we chose the driver from QODBC and suggested to the client that they purchase the full version of the software for $500.

1) You can’t have a QuickBooks file open in order for the connection to work.

2) If the QuickBooks application “hangs” or stays open it must be closed using Microsoft Task Manager. (application remained open after most failed attempts)

Ah this point we had SSIS packages that could be run within Visual Studio repeatedly. Now we would begin what we always knew would be the most challenging aspect of this project, the automation of the package.

Before we automated the packages using the QuickBooks files we attempted to use automatically-refreshing Excel files. We also wanted to have a backup plan in case we were not able to successfully connect to the QuickBooks file.

1) Optimization – Two months after the client signed off on all of the numbers produced by the Data Warehouse we got an email saying that both Balance Sheet and Income Statement accounts were off after a complete refresh of the data. This was odd because we had not made any changes to the code or the Excel file source in months. After about a week of investigation I learned that, on occasions, you need to “optimize” the driver.

We were instructed by the driver developer (FLEXquarters is the name of the company; QODBC is the name of the driver) to run the following two stored procs in the VB Demo application.

This fixed the data issue but it did not solve our automation issue. We did not include these stored procedures in the process because, according to FLEXquarters, optimization is only needed if the connection to the file is stopped mid-process. We included in the client’s documentation instructions on how to optimize the driver.

2) Proxy Account and the DCOM setting – We set SQL Agent to use the Proxy account to execute the job and its underlying packages. When it came to the step that accesses the QuickBooks file it was not using the proxy account to open the QuickBooks application, instead it used a real user account. After investigating the various driver settings, using mostly trial and error, we found that if you disable the DCOM setting it will use the proxy account. However, the process would fail immediately every time with the error “cannot make the connection.” We found the only way to get the package to work was to enable the DCOM setting and not use the proxy account to open the file.

3) Scheduling – We thought we were clear once the package was running using the proxy account. Our next roadblock was when we scheduled the package to run when no one was logged on to the machine. Until that point someone was always logged into the machine so there was always a user that could be used to open the application. When the process was scheduled at a point when no users were logged on it failed because it “could not connect to the QuickBooks file.” Someone had to be logged into the machine in order for the connection to work and that person needed access to the folder that contains the QuickBooks files.

4) Accessing QuickBooks - What took the most time to investigate was how the QODBC driver accesses the QuickBooks data. When the package executes an instance of QuickBooks is opened in the Task Manager. The application is not opened on a user’s machine; it only it opened in the background. Even though the proxy account is used to run the package it is not the default user to open the QuickBooks instance. The solution was to create a “QuickBooks” user on the machine and keep the user logged in indefinitely. This was all documented on the design document created for the client.

5) Package runs differently with Proxy Account, QuickBooks User and SQL Agent Scheduled – Both the runtime and memory used were significantly lower once the job was set up to run automatically. There were 4 total packages, using 2 QuickBooks files. It would take approximately 2 minutes for the 4 packages to run in Visual Studio, using my account to access QuickBooks. When the job was ultimately completed the runtime averaged around 20 minutes.

The CPU was also very different. In the development phase Task Manager showed CPU-usage to spike around 140K but once automated and scheduled the CPU would not exceed 100K.

In the end, we probably would have saved about a week’s worth of work if would have just let the package run while we were at lunch. There were times when we would watch the CPU stall for about 10 minutes. We ended the process and started over. Lesson learned for next time.

My suggestion to anyone who intends to hook up a QuickBooks file to an SSIS project would be to engage the driver provider immediately and make the automation your #1 priority. Also, if you made any changes to the driver’s settings make sure you take screen shots and communicate this information onto the technical documentation you give to the client. Even though the process runs perfectly once it is handed off, the sensitive nature of the driver all but guarantees that the process will fail at some point.

Set the Excel file to automatically refresh	Excel files can’t be refreshed unless they are opened.
Use Task Schedule to open, save and close the file.	The Excel files would not refresh.