Knowledgebase
[QODBC-Desktop] How to use DTS to Load QuickBooks Data into MS SQL Server
Posted by Juliet (QODBC Support) on 14 April 2010 06:21 AM

Introduction

      The following documentation was written by Andrew Rachmiel, at BrightStar Partners, Inc. in order to provide guidance 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 create 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.

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

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 select OK. This will open it in DTS.

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

5. Modify the connections set up 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 prior to running the batch.

1. Copy the contents below into notepad and save 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. Make sure 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).

(163 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).