How to use QODBC with SSIS
Either QODBC or QRemote is required on the machine where SSIS package will be developed, deployed or SSIS services are installed.
Development - On the machine, you are developing SSIS package - You should have QODBC installed.
Deployment - On the machine, you wish to deploy or run the SSIS Package - You should have QODBC installed.
The first step is to install and test QODBC.
Please refer Connecting QODBC Driver to QuickBooks for the First Time (Video)
If QuickBooks application is installed on another machine, then you can connect to QuickBooks from a remote machine using QRemote Server. You need to install QODBC on both machines.
Please refer Accessing QuickBooks Data Remotely using QODBC & QRemote. (Video)
When you are deploying SSIS package to SQL Server you need to configure QRemote client to connect with QuickBooks.
Please follow below steps to use QODBC with SSIS
1. Go to Start menu & open SQL Server Business Intelligence Development Studio from SQL Server.
2. Create new Integration Service Project by clicking on Integration Service Project & Enter Project Name, set project location and click OK.
The Project is created.
3. In this sample project, we will transfer QuickBooks data using the ADO.Net source to Flat File Destination. To perform we need to add Data Flow Task in Control Flow tab. You can add it by double click on Data Flow Task.
4. Now we need to add ADO.Net Source in Data Flow tab. You can add it by double click on ADO.Net Source.
5. Now we need to configure ADO.Net Source. By right click on ADO.Net Source & click on Edit.
Add new ADO.Net connection by click on New.
Add new Data connection by click on New.
Select Provider to .Net Providers\Odbc Data Provider, Select DSN to QuickBooks Data QRemote and click on Test Connection.
Test Connection Succeeded.
Click on OK & you will notice connection is added. You can select Data Access Mode as Table or view for accessing whole table data. You can also select Data Access Mode as SQL command for accessing table data with condition & accessing report data using sp_report. In this example, I have selected Table or view as Data Access Mode & selected Customer table.
You can preview output data by clicking Preview button.
Close the Preview by clicking the close button & click on OK to save configurations.
6. Now we need to add Flat File Destination in Data Flow tab. You can add it by double click on Flat File Destination.
7. Now we need to link ADO.Net Source with Flat File Destination by dragging link from ADO.Net Source to Flat File Destination.
8. Now we need to configure Flat File Destination. By right click on Flat File Destination & click on Edit.
Add new Flat File connection by click on New.
Select Flat File Format & click OK.
Select Destination file in which you want to store output using Browse button.
Enable Unicode & Column names in the first data row option & Click on OK to save configurations
You will notice OK button is disabled, To enable it you need to click on Mapping, then click OK to complete configuration.
9. Run SSIS package to perform transfer operation by clicking play icon.
SSIS Package execution completed & 163 rows are transferred from ADO.Net Source to Flat File Destination.
Here is a Flat text file which is containing ADO.Net Source result.
Please click here for downloading Sample SSIS Package.
I am evaluating your driver and have had success running SELECT queries against QB in SSMS where I was able to use the QRemote DSN. When setting up ODBC connection in SSIS, I get an error when trying to enumerate the columns.
Unknown Data type found on column
You need to select “ADO.NET Source.” instead of ODBC Source in SSIS package to resolve this error.