Using SQL Server Integration Services (SSIS) to populate QuickBooks records

The QuickBooks ODBC driver allows you to insert data into QuickBooks by using an SSIS package.

Before you can use the QuickBooks ODBC driver to connect SSIS to QuickBooks, you need to configure an ODBC data source. An ODBC data source stores the connection details for the target database (in this case, QuickBooks) and the ODBC driver that is required to connect to it (in this case, the QuickBooks ODBC driver).

You configure ODBC data sources in ODBC Data Source Administrator, which is included with Windows. To run ODBC Data Source Administrator, in the Windows Run dialog box, enter:

%windir%\syswow64\odbcad32.exe

Use ODBC Data Source Administrator to create a QuickBooks ODBC driver data source:

1. Choose the System DSN tab, and then choose Add.
2. In the Create New Data Source dialog box, choose Easysoft ODBC-QuickBooks Desktop Driver, and then choose Finish.
3. Complete these fields in the Easysoft ODBC-QuickBooks Desktop Driver DSN Setup dialog box:

   Setting	Value
   DSN	QuickBooks Desktop
   Connection Mode	Same As QuickBooks
   Application Name	EasysoftQuickBooksODBC
   Application ID	EasysoftQuickBooksODBC
   QB Edition	Set this to be the same as QuickBooks Desktop. For example, if you have the US version of QuickBooks, set QB Edition to US.

Create the SSIS project:

1. Create a text file named billingrate.txt with these contents:

   Name,FixedBillingRate
   "BR1A",5.00
   "BR1B",10.00
   "BR1C",15.00
   "BR1D",20.00
   "BR1E",25.00

2. Start QuickBooks Desktop if it is not already running.
3. In SSIS, create a new project named QuickBooksBillingRate.
4. Drag a Data Flow Task from the SSIS Toolbox to the Control Flow.
5. Name the Data Flow Task CreateBillingRate.
6. Double-click CreateBillingRate.
7. In the Data Flow tab, double-click a Flat File Source. Name the Flat File Source BillingRate.
8. Double-click BillingRate.

   The Flat File Source Editor dialog box is displayed.

9. Choose New.

   The Flat File Connection Manager Editor dialog box is displayed.

10. Browse for billingrate.txt. In the Text qualifier box, type ".
11. In the left hand pane, choose Columns. Choose OK.
12. In the Data Flow tab, double-click ODBC Destination. Name the ODBC Destination BillingRateTable.
13. Drag the blue arrow from BillingRate to BillingRateTable.
14. Double-click BillingRateTable

    The ODBC Destination dialog box is displayed.

15. Choose New.

    The Configure ODBC Connection Manager dialog box is displayed.

16. Choose New.

    The Connection Manager dialog box is displayed.

17. Choose your QuickBooks ODBC driver data source from the Use user or system data source name list. Choose OK.
18. In the ODBC Destination dialog box, enter BillItemLine in the Name of the table or the view box.
19. QuickBooks prompts you to whether to allow the QuickBooks ODBC driver to access your QuickBooks data. In the QuickBooks - Application with No Certificate dialog box, choose Yes, always allow access even if QuickBooks is not running. Choose Allow this application to access personal data such as Social Security Numbers and customer credit card information. Choose Continue.

    The Confirm to Proceed dialog box is displayed.

20. Choose Yes.

    The Access Confirmation dialog box is displayed. Choose OK.

21. In the Mappings pane, map each input column to a destination column that has the same name. Choose OK.
22. Choose the Control Flow tab, and then choose Start.

    The BillingRate table is populated with the new records.