Introduction

      The following documentation was written by Andrew Rachmiel at BrightStar Partners, Inc., to guide in setting up a Microsoft SQL server environment to store the QuickBooks database tables.

      The purpose of writing this document was to share the information learned in our setup of the environment in-house and will provide a tremendous start for anyone interested in implementing this technique. Note that this was written in order to provide a SQL server database environment for creating reports off of QuickBooks data with Cognos software. BrightStar Partners is a Cognos implementation partner, and the Cognos content will be excluded from this document but is available by contacting him at arachmiel@brightstarpartners.com.

System Environment

      This was written using QuickBooks 2004 Professional Services Edition, QODBC Driver for QuickBooks 7.00.00.207, and SQL Server 2000.

Setup of QODBC

      It is assumed that the documentation "How to create Link Server with MS SQL 2000" provided on this forum for setting up your connection to QuickBooks has been followed. Therefore, it will not be described in this document.

Microsoft SQL Server 2000

Database Creation

      The first step in this process is to create a new Microsoft SQL Server database.

      I created a database called QB, and I would recommend, if possible, that this name is used so that the DTS scripts work with little to no customization. Please refer to the Microsoft SQL Server 2000 documentation for information on how to create a new database.

Database Tables

      Attached is a .sql file that can be executed in Microsoft SQL Query Analyzer. This script should be run against the newly created database to ensure that the tables are created in this database.

      Note that we have several custom fields in our environment that will be created as attributes in the applicable tables, and their existence will NOT impact any environment that does not have them.

     The whole script can view by clicking here.

DTS Setup

     In order to load the data from QuickBooks, a Microsoft DTS job has been created. The job will truncate all tables, reload each table one at a time, and also load transaction details (which is used by us in our Cognos report creation). The transaction detail load leverages a QB stored procedure that was learned through the QODBC.com website. The remainder of the loads is straight Select * from xxx statements.

    For the jobs to run in a rapid fashion, it is essential that the tables are loaded via the Optimize feature in the QODBC driver. A batch file has been created to facilitate this and the execution of the DTS process.

1. Within SQL Server Enterprise Manager, navigate to the Data Transformation Services. Right, Click and select Open Package…

2. Navigate to the provided .dts package file and select it.

3. At the following screen, select the 2/8/07 version and OK. This will open it in DTS.

4. Save the new DTS Package with whatever name you desire.

5. Modify the connections in the package to ensure that they work in your environment.

    a. Connection 1

    b. Connection 2

    c. Connection 3

    d. Connection 4

6. Save and close the package.

Using Microsoft DTS with QODBC

Automating the Load

     A batch file can be created to automate the load, and it can be scheduled in any scheduling tool to run as frequently as desired. Note that QuickBooks must be open with the company file you are using before running the batch.

1. Copy the contents below into notepad and save them as RunQODBC.bat

    C:
    REM Updating the QuickBooks Optimization file
    cd "C:Program FilesQODBC Driver for QuickBooks."
    qodbcful.exe
    REM
    REM Executing the DTS Package
    dtsrun /S <SQL SERVER> /E /N "<DTS PACKAGE NAME>"
    exit

2. Ensure that the items in <SQL SERVER> and <DTS PACKAGE NAME> are replaced with your environment information.

To download the DTS package and SQL file, click here (6Mb).