SQLServerCentral Article

Connect QuickBooks to SQL Server through SSIS

,

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 QuickBooks and/or QuickBooks Online into a Microsoft SQL Server database. The exact same procedure outlined below can be used with any CData ADO.NET Data Providers to connect SQL Server directly with remote data via SSIS.

  • Step 1: Open Visual Studio and create a new Integration Services Project.
  • Step 2: Add a new Data Flow Task from the Toolbox onto the Control Flow screen.
  • Step 3: In the Data Flow screen, add a DataReader Source and an OLE DB Destination from the Toolbox.

  • Step 4: Add a new Data Connection, and select your provider as .NET Providers\CData QuickBooks Data Provider.
  • Step 5: Enter the connection details for your QuickBooks machine. In this example, we are using a separate machine to host QuickBooks on.

  • Step 6: Open the DataReader editor. Go to the Connection Managers tab and select the Data Connection you just created.
  • Step 7: Keeping the DataReader Source open, go to the Component Properties tab and enter your SQL select command.

  • Step 8: Close the DataReader editor. Now drag the arrow below the DataReader Source to connect it to the OLE DB Destination.
  • Step 9: Open the OLE DB Destination and add a New Connection. Enter your server and database information here. In this example, we are using a separate machine running SQLExpress.
  • Step 10: Set your Data access mode to "table or view" and select the table or view to populate in your database
  • Step 11: Configure any properties you wish to on the Mappings screen.

  • Step 12: 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.

SSIS Sample Project

To help you with getting started using the QuickBooks Data Provider within SQL Server SSIS, download the fully functional sample project. You will also need the QuickBooks ADO.NET Data Provider to make the connection. You can download a free trial here.

Note: Before running the demo, you will need to change your connection details to fit your environment as detailed in Step 5 and Step 9.

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.55 (11)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.55 (11)

You rated this post out of 5. Change rating

Related content

Technical Article

Reproduced with kind permission from the blog of Ashvini Sharma (MSFT)

InfoPath forms can be saved to XML, these XML Files can later be used in SSIS XMLSource adapter to pull out the data in tables and columns. However, there are some common problems you may meet in these scenarios. This article describes how to work around these potential problems. The issues mentioned in this article is not only specific to InfoPath files, it can also be referenced in other similar situations as well.

2005-06-20

1,328 reads

External Article

How to Asynchronously Execute a DTS package from ASP or ASP.NET

The Data Trasformation Services are a powerful tool, and sometime its features are so useful that you’d like to invoke a DTS package not only from SQL Server but from an external program.

To do this you have several choices: you can use the DTSRun.exe tool or you can do it leveraging the SQL-DMO features.

Unfortunately if you’re developing a web application (ASP, ASP.Net or whatever you use) none of them seems to be the right choice: too much problems, too much effort and a very modest results. In addition none of these solutions can be called asynchronously: if you just need to implement a “fire-and-forget” technique, you just cannot do that!

2005-05-24

2,663 reads

Technical Article

Easy Package Configuration

One of the age old problems in DTS is moving packages between your development, test and production environments. Typically a series of manual edits needs to be done to all the packages to make sure that all the connection objects are pointing to the correct physical servers. This is time consuming and gives rise to the possibility of human error, particularly if the solution incorporates many DTS packages. Many companies have provided their own custom solutions for managing this problem but these are still workarounds for a problem that is inherently DTS's.

2004-12-14

1,761 reads

Technical Article

For Loop Container Samples

One of the new tasks in SQL Server 2005 is the For Loop Container. In this article we will demonstrate a few simple examples of how this works. Firstly it is worth mentioning that the For Loop Container follows the same logic as most other loop mechanism you may have come across, in that it will continue to iterate whilst the loop test (EvalExpression) is true. There is a known issue with the EvalExpression description in the task UI being wrong at present. (SQL Server 2005 Beta 2).

2004-11-18

3,221 reads