Connecting To Quickbooks via SSIS

  • Hello everyone,

    Been doing some googling trying to find some info on connecting to Quickbooks desktop from SSIS.  Everything I'm getting back is third party options where I have to pay.  Trying to avoid that if I can.  Seems like some sort of ODBC driver or something should be available for this where I can just do it myself in SSIS.

    Need to be able to load data from Quickbooks to SQL server so I can begin doing other analytics and reporting on it.  Any assistance or point to the right direction would be greatly appreciated.

    Thanks!

    Strick

  • Thanks for your response Steve.  Purchasing it is the only way?  There's no drivers/ODBC where I can just program it myself?  Most of the ones I'm seeing want to charge $2k plus for a suite of SSIS components and all I want to do is connect to Quickbooks desktop.

  • depending on your version maybe this will work https://quickbooks.intuit.com/enterprise/customer-resource-center/odbc-drivers/

    one that isn't that expensive is Devart https://www.devart.com/odbc/quickbooks/ordering.html

  • It's been a long time since I've done it (circa 2000) but I believe that QuickBooks still has an export bit of functionality and a CLI to support it.  Why not just use what's there and pop the CLI from SSIS using a CmdExec task and import from the resulting file?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I'd go with Jeff's idea, if that works for you. A simpler import, in my mind.

  • Using SQL Server as a backup for critical business data provides an essential safety net against loss. In addition, backing up data to SQL Server enables business users to more easily connect that data with features like reporting, full-text search, analytics, and more.

    This example demonstrates how to use the QuickBooks ADO.NET Data Provider inside of a SQL Server SSIS workflow to transfer data directly from desktop editions of QuickBooks into a Microsoft SQL Server database. The same procedure outlined below can be used with any CData ADO.NET Data Providers to connect SQL Server directly with remote data via SSIS.

    • Open Visual Studio and create a new Integration Services Project.
    • Add a new Data Flow Task from the Toolbox onto the Control Flow screen.
    • Add an ADO.NET Source and an OLE DB Destination from the Toolbox.

      undefined

    • Add a new ADO.Data Connection, and in the .NET Providers menu, select CData ADO.NET Provider for QuickBooks.
    • In the connection manager, enter the connection details for your QuickBooks machine. This example uses a QuickBooks instance hosted on on the local machine.
    • Open the ADO.NET Source editor and set the following information:

      ADO.NET connection manager: In the Connection Managers menu, select the Data Connection you just created.

      Data access mode: Select 'SQL command'.

    • SQL command text: Enter a SELECT command, such as the one below
    • Close the ADO.NET Source editor and drag the arrow below the ADO.NET Source to connect it to the OLE DB Destination.
    • Open the OLE DB Destination and enter the following information in the Destination Component Editor.
    • Connection manager: Add a new connection. Select the SQL Server Native Client in the .NET Providers menu and enter your server and database information.
    • Data access mode: Set your data access mode to "table or view" and select the table or view to populate in your database.
    • Configure any properties you wish to on the Mappings screen.

      undefined

    • Close the OLE DB Destination Editor and run the project. After the SSIS Task has finished executing, your database will be populated with data obtained from QuickBooks.

    Greeting,

    Rachel Gomez

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply