[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
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 email@example.com.
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
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.
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.
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.
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
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).