COZYROC QuickBooks components, part of COZYROC SSIS+ suite since 2012, are third-party plug-ins for Microsoft SSIS that make it easy to integrate or migrate data to and from QuickBooks Desktop. You can use any application, service or database supported by SQL Server Integration Services (SSIS) or COZYROC’s toolkit as the source or destination for QuickBooks data. The SSIS+ Library is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components. In addition, COZYROC also provides short and informative demonstration videos, providing step-by-step instructions on how to get started.
The QuickBooks integration package consists of four parts:
- QuickBooks Connection Manager
- Stores the credentials and functionality for connecting to QuickBooks Desktop.
- Well documented API for use by user-defined scripts.
- QuickBooks Source component
- Retrieves data from QuickBooks objects.
- QueryXML may be used to filter records.
- Handles composite Objects by setting up one output for the main objects and a separate output for the related composite objects.
- The Object and the QueryXML criteria can be modified at runtime using variables and an expression.
- QuickBooks Destination component
- Writes data to QuickBooks objects.
- Supports four actions on Objects: Create, Update, Delete or Void.
- Provides information about records with errors as well as the Record identifier and Edit Sequence for updated records, deleted records, voided records, and new records.
- The component is able to load composite Objects into QuickBooks.
- Reusable scripts
- Unique technology which allows user-defined reusable scripts for additional QuickBooks integration scenarios.
- Support QuickBooks Desktop.
Technical Specifications
Case Studies
QuickBooks Source
In this section we will show you how to set up a QuickBooks Source component.
- Before you begin, configure a QuickBooks connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the QuickBooks Source component and drag it onto the Data Flow canvas.
- Double-click on the QuickBooks Source component on the canvas to open the editor.
- Once the component editor opens, select the connection manager you configured earlier from the Connection drop-down list.
- Click on the Component Properties tab.
- For Object, select the QuickBooks object you want to work with.
- Click on the Input and Output Properties tab.
- Remove any extra Outputs that you do not want from the list of outputs. Certain objects offer multiple outputs and they will have many columns duplicated between them. To remove the unwanted Outputs, select the output(s) you do not want, one-by-one, and click on Remove Output for each one.
- Expand the Output that you want to work with. If you do not want to work with all of the columns from this Output, expand the Output Columns and remove the columns you do not want from the list by selecting them, one-by-one, and clicking on Remove Column for each one.
- Click OK to close the component editor.
Congratulations! You have successfully configured the QuickBooks Source component.
QuickBooks Online Connection
In this task, we will show you step-by-step how to create a connection to QuickBooks Online REST API using COZYROC's REST Connection Manager.
Step 1. Run "Visual Studio" under "Administrator account". Right click on Connection managers Tab and Select New Connection. In Add SSIS Connection Manager scroll down and select Connection managers for REST Connection and click Add.
Step 2. From drop-down menu Configuration select QuickBooks Online. Then click New in the Token File parameter to create a new token file.
Step 3. Specify token scope. If your QuickBooks Online instance doesn't include the Payment feature, remove com.intuit.quickbooks.payment from scope. Press Next.
Step 4: Provide Email and Password. Then press 'Sign In' button.
Step 5: Save generated access token to file. Specify token protection password. Click OK.
Step 6. Click "Test Connection" to test your connection.
Congratulations! You have now established a connection to your QuickBooks Online instance.
In this guide, we will show how to read data from the QuickBooks Online service resource using the COZYROC REST Source component.
Step 1. Select an existing "Data Flow task" or create a new "Data Flow Task".
Step 2. Open "SSIS Toolbox" and drag and drop COZYROC "REST Source" component.
Step 3. Double click on the "REST Source" component and select QuickBooks Online connection from the combo. Select from available QuickBooks Online resources listed in the second combo below.
Optionally you can include extra parameters in the grid below if needed.
Step 4. Click on the "Columns tab" and select QuickBooks Online resource columns you want to read.
Step 5. Click Preview to make sure all necessary resource columns are included.
Bravo! You have now learned how to read data from QuickBooks Online service.
In this guide, we will show how to write data to the QuickBooks Online service resource using the COZYROC REST Destination component.
Step 1. Select an existing "Data Flow task" or create a new "Data Flow Task".
Step 2. Open the "SSIS Toolbox" and drag and drop COZYROC "REST Destination" component.
Step 3. Double click on the "REST Destination" component and select QuickBooks Online connection from the combo. Also, select action and resource you want to work on.
Step 4. Click on the "Mapping" tab and map the input columns for the process you are configuring.
If you want to provide input for other resource composite objects, change the input above and map the columns.
Done. You have now learned how to write data into the QuickBooks Online resource.
QuickBooks Destination
In this section we will show you how to set up a QuickBooks Destination component.
- Before you begin, configure a QuickBooks connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- Configure a source component that will provide the data to be written to QuickBooks.
- In the SSIS Toolbox, locate the QuickBooks Destination component and drag it onto the Data Flow canvas.
- Double-click on the QuickBooks Destination component on the canvas to open the component dialog.
- Once the component dialog opens, select the connection manager you configured earlier from the Connection drop-down list.
- Choose the desired Action (Create, Update, Delete, or Upsert).
- Choose the Object you will be working with.
- Click OK to close the component dialog.
- Connect the blue arrow from the source component or the data flow transformation that you want to immediately precede the QuickBooks Destination component.
Congratulations! You have successfully configured the QuickBooks Destination component.
QuickBooks Desktop Connection
In this section we will show you how to set up a QuickBooks Connection Manager to access QuickBooks Desktop Edition.
Configure QuickBooks to allow an external application to connect
- In QuickBooks, click on Edit and then select Preferences from the menu options.
- Select Integrated Applications from the list.
- Click on the Company Preferences tab.
- Ensure that the checkbox for “Don’t allow any applications to access this company file” is NOT checked.
- Click OK to exit the Preferences.
Set up a User account and password in QuickBooks for the Connection Manager to use.
- In QuickBooks, click on Company.
- Select Set Up Users and Passwords from the dropdown list and then select Set Up Users…
- Click on the Add User… button.
- Enter User Name, Password and Confirm Password for the new account.
- Click on Next.
- Select the areas of QuickBooks to which you want this user account to have access. We selected All areas of QuickBooks in this example. If you select Selected areas of QuickBooks, you will go through several more screens to choose the access to allow in each area.
- Click on the Next button.
- If there’s a warning pop-up to confirm your selection, click Yes to confirm.
- A pop-up will display all the areas to which you have given this user access in QuickBooks.
- Click on the Finish button.
- The User List will now display the new user you just set up.
- Click on Close to close the User List.
Configure and start the COZYROC QuickBooks Gateway.
This application must be run on the same server as the server where QuickBooks Desktop Edition is running. QuickBooks Desktop Edition does not provide any support for access over a network. Therefore, the COZYROC QuickBooks Gateway is used to provide that connectivity to your SSIS packages.
- Using Windows Explorer, find QbConnector.exe by adjusting this path for your server: C:\Program Files (x86)\CozyRoc\SSIS\Tools.
- Double-click on QbConnector.exe to open the application dialog.
- In the Connection tab, ensure that the Port is set to 3081.
- Choose the Mode you would like to use. In this example, we are using Default.
- You may choose to Accept a connection from Any address or you may specify a specific Address. In this example, we will accept a connection from any address.
- Click on the Users tab.
- Type in the Name and Password of the account you just set up in QuickBooks for the COZYROC QuickBooks Gateway to use.
- Click on the ellipses next to Company and then select the QuickBooks company file for the QuickBooks company you want to access.
- Click on the Add button to add the new user.
- Click on the Security tab.
- Choose the appropriate security option for your environment. In this example we’ll choose Disabled. Note that if you select Disabled here, you will use http:// in front of the server’s address in step 35. If you select one of the security options that uses a certificate here, you will use https:// in front of the server’s address in step 35.
- Click Start.
- If you get a pop-up with a Windows Security Alert, check the appropriate checkbox and then click on the Allow access button.
Set up the QuickBooks Connection Manager in Visual Studio.
- Right-click on the Connection Manager canvas.
- Select New Connection in the pop-up list.
- Scroll down to find the QuickBooks connection manager in the list and select it.
- Click on the Add button.
- When the configuration dialog opens, enter the IP address of the server where QuickBooks resides with 3081 as the port. If you selected Disabled under the Security tab of the QB Connector, this should start with http://. If you selected one of the certificate choices, this should start with https://.
- Enter the User name and key for the user you set up in QuickBooks for this application to use. Enter the password in the key field.
- Ensure that you have the QuickBooks company file open in QuickBooks that you want to access.
- Click on Test Connection.
- When the QuickBooks – Application Certificate pops up in QuickBooks, select one of the yes choices under the question Do you want to allow this application to read and modify this company file?
- You may be asked to confirm your selection in the CozyRoc QuickBooks Gateway – Authorization pop-up if you chose to allow this application to access the company file even if QuickBooks is not running.
- If you chose Yes, always; allow access even if QuickBooks is not running, then enter the User name of the account to be used to login from your SSIS package.
- You may or may not want to check the checkbox for Allow this application to access personal data such as Social Security Numbers and customer credit card information.
- Click Continue.
- You will see an Access Confirmation pop-up. Click Done.
- Now you should be back in Visual Studio and should see Test Connection succeeded. Click OK.
- Click OK again to close the connection manager dialog.
- If you go back into QuickBooks and look at the Integrated Applications settings in the Preferences, you will see that the User account used by your SSIS package now shows up as an application that has requested access to the company file.
Congratulations! You have successfully configured a QuickBooks connection manager.
Overview
QuickBooks Connection Manager is SSIS Connection Manager for establishing QuickBooks connections. Use the API to build components based on QuickBooks Connection Manager.
QuickBooks Desktop Edition Gateway
The installation includes application for remote web access of QuickBooks Desktop Edition database. The application is found in COZYROC installation sub-folder Tools. The name of the application is QbConnector.exe. Before you are able to work with the data for the first time, you have to enable QbConnector application to access the data in your QuickBooks Desktop application. Please review your QuickBooks documentation how to enable third-party access to your data. Use the options below to setup the gateway.
Specify network port. This port will be used by the remote clients to communicate with the server.
Specify log file to trace communication. Enable only during test because it will slowdown the communication.
Specify QuickBooks database connection mode. This property has the options listed in the following table.
| Value | Description |
|---|---|
| Default | Connect in the mode QuickBooks is already open (default). |
| Single | Connect in single user mode. |
| Multi | Connect in multi user mode. |
Specify what remote client addresses are allowed to connect to gateway. This property has the options listed in the following table.
| Value | Description |
|---|---|
| Any | Any remote client can connect to gateway (default). |
| Address | Only specified remote client can connect to gateway. |
Use Users tab to setup credentials of users, which can access the gateway. Optionally select user-associated company database file. If database file is not specified, the user will connect to the database currently open in QuickBooks.
Specify to use secure (HTTPS) protocol for communication. This property has the options listed in the following table.
| Value | Description |
|---|---|
| Disabled | The secure communication is disabled (default). |
| Embedded Certificate | The secure communication is enabled using the embedded X.509 certificate. |
| User-defined Certificate | The secure communication is enabled using the user-defined X.509 certificate. |
Specify after how many minutes of inactivity the QuickBooks database session is closed.
Specify to start the gateway when the application is opened.
Quick Start
In this section we will show you how to set up a QuickBooks Connection Manager to access QuickBooks Desktop Edition.
Configure QuickBooks to allow an external application to connect
- In QuickBooks, click on Edit and then select Preferences from the menu options.
- Select Integrated Applications from the list.
- Click on the Company Preferences tab.
- Ensure that the checkbox for “Don’t allow any applications to access this company file” is NOT checked.
- Click OK to exit the Preferences.
Set up a User account and password in QuickBooks for the Connection Manager to use.
- In QuickBooks, click on Company.
- Select Set Up Users and Passwords from the dropdown list and then select Set Up Users…
- Click on the Add User… button.
- Enter User Name, Password and Confirm Password for the new account.
- Click on Next.
- Select the areas of QuickBooks to which you want this user account to have access. We selected All areas of QuickBooks in this example. If you select Selected areas of QuickBooks, you will go through several more screens to choose the access to allow in each area.
- Click on the Next button.
- If there’s a warning pop-up to confirm your selection, click Yes to confirm.
- A pop-up will display all the areas to which you have given this user access in QuickBooks.
- Click on the Finish button.
- The User List will now display the new user you just set up.
- Click on Close to close the User List.
Configure and start the COZYROC QuickBooks Gateway.
This application must be run on the same server as the server where QuickBooks Desktop Edition is running. QuickBooks Desktop Edition does not provide any support for access over a network. Therefore, the COZYROC QuickBooks Gateway is used to provide that connectivity to your SSIS packages.
- Using Windows Explorer, find QbConnector.exe by adjusting this path for your server: C:\Program Files (x86)\CozyRoc\SSIS\Tools.
- Double-click on QbConnector.exe to open the application dialog.
- In the Connection tab, ensure that the Port is set to 3081.
- Choose the Mode you would like to use. In this example, we are using Default.
- You may choose to Accept a connection from Any address or you may specify a specific Address. In this example, we will accept a connection from any address.
- Click on the Users tab.
- Type in the Name and Password of the account you just set up in QuickBooks for the COZYROC QuickBooks Gateway to use.
- Click on the ellipses next to Company and then select the QuickBooks company file for the QuickBooks company you want to access.
- Click on the Add button to add the new user.
- Click on the Security tab.
- Choose the appropriate security option for your environment. In this example we’ll choose Disabled. Note that if you select Disabled here, you will use http:// in front of the server’s address in step 35. If you select one of the security options that uses a certificate here, you will use https:// in front of the server’s address in step 35.
- Click Start.
- If you get a pop-up with a Windows Security Alert, check the appropriate checkbox and then click on the Allow access button.
Set up the QuickBooks Connection Manager in Visual Studio.
- Right-click on the Connection Manager canvas.
- Select New Connection in the pop-up list.
- Scroll down to find the QuickBooks connection manager in the list and select it.
- Click on the Add button.
- When the configuration dialog opens, enter the IP address of the server where QuickBooks resides with 3081 as the port. If you selected Disabled under the Security tab of the QB Connector, this should start with http://. If you selected one of the certificate choices, this should start with https://.
- Enter the User name and key for the user you set up in QuickBooks for this application to use. Enter the password in the key field.
- Ensure that you have the QuickBooks company file open in QuickBooks that you want to access.
- Click on Test Connection.
- When the QuickBooks – Application Certificate pops up in QuickBooks, select one of the yes choices under the question Do you want to allow this application to read and modify this company file?
- You may be asked to confirm your selection in the CozyRoc QuickBooks Gateway – Authorization pop-up if you chose to allow this application to access the company file even if QuickBooks is not running.
- If you chose Yes, always; allow access even if QuickBooks is not running, then enter the User name of the account to be used to login from your SSIS package.
- You may or may not want to check the checkbox for Allow this application to access personal data such as Social Security Numbers and customer credit card information.
- Click Continue.
- You will see an Access Confirmation pop-up. Click Done.
- Now you should be back in Visual Studio and should see Test Connection succeeded. Click OK.
- Click OK again to close the connection manager dialog.
- If you go back into QuickBooks and look at the Integrated Applications settings in the Preferences, you will see that the User account used by your SSIS package now shows up as an application that has requested access to the company file.
Congratulations! You have successfully configured a QuickBooks connection manager.
Parameters
Server
Use the Server page of the QuickBooks Connection Manager dialog to specify properties for connecting to QuickBooks service.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Select QuickBooks deployment type. This property has the options listed in the following table.
Value Description Online QuickBooks Online deployment. Desktop QuickBooks deployed on premise (default). Select QuickBooks Online connection token file. If token file doesn't exist, you have to create New... token.
Specify token file decryption password.
Specify the name or IP address of the QuickBooks service.
Specify login to access QuickBooks. The default for QuickBooks Online Edition: ssis.cozyroc.com
Specify connection key to access QuickBooks. For QuickBooks Online Edition (QBOE), review "How to get QuickBooks Online Edition connection key?" below.
Specify application identifier to access QuickBooks. For QuickBooks Online Edition this is mandatory parameter. The default identifier: 374225725.
Specify the number of seconds before timing out session connect. The default value of this property is 100 seconds.
Specify if all clients should use the same connection. This property has the options listed in the following table.
Value Description True All clients use same connection (default). False Every client use different connection. This property is not visible in the connection manager dialog.Specify QBXML version to use with QuickBooks Desktop.
This property is not visible in the connection manager dialog.
Proxy
Use the Proxy page of the QuickBooks Connection Manager dialog to specify properties if your server is behind firewall.
Specify the name or IP address of the proxy server. If not specified, proxy server is not used.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
Knowledge Base
- Where can I find the documentation for the QuickBooks Connection?
- How to get QuickBooks Online Token
- DEPRECATED: How to get QuickBooks Online Edition connection key?
- Does COZYROC support TLS 1.1 and 1.2 protocols?
- How to trace HTTP(S) and REST API using Fiddler
What's New
- New: Support for TLS 1.1 and 1.2 protocols in QuickBooks Desktop Edition Gateway.
- New: Updated QuickBooks Online web service proxy to version 43.
- New: A new parameter 'Start on Open' in QuickBooks Desktop Edition Gateway to automatically start the gateway when the application is opened.
- New: Support for QuickBooks Online sandbox environment.
- New: Support for QuickBooks Online OAuth 2.0 authentication.
- New: Updated QuickBooks Desktop web service proxy to version 13.
- New: A new parameter QBXMLVersion for use with QuickBooks Desktop connections.
- Fixed: Failed with error when using 'ItemSites' object (Thank you, Joel).
- New: Enabled support for TLS 1.1 and 1.2 protocols.
- Fixed: Failed to generate new token for the latest QuickBooks Online service.
- New: Support for QuickBooks Online Data Services.
- New: A new parameter 'Inactive sessions close' in QuickBooks Desktop Edition Gateway to close sessions after specified time of inactivity.
- New: Introduced connection.
Overview
The QuickBooks Source Component is an SSIS Data Flow Component for consuming data from the QuickBooks service. The configuration parameters are provided in the standard Data Flow Component dialogs. A separate output is setup for the main object and each related composite object.
Demonstration
Quick Start
In this section we will show you how to set up a QuickBooks Source component.
- Before you begin, configure a QuickBooks connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the QuickBooks Source component and drag it onto the Data Flow canvas.
- Double-click on the QuickBooks Source component on the canvas to open the editor.
- Once the component editor opens, select the connection manager you configured earlier from the Connection drop-down list.
- Click on the Component Properties tab.
- For Object, select the QuickBooks object you want to work with.
- Click on the Input and Output Properties tab.
- Remove any extra Outputs that you do not want from the list of outputs. Certain objects offer multiple outputs and they will have many columns duplicated between them. To remove the unwanted Outputs, select the output(s) you do not want, one-by-one, and click on Remove Output for each one.
- Expand the Output that you want to work with. If you do not want to work with all of the columns from this Output, expand the Output Columns and remove the columns you do not want from the list by selecting them, one-by-one, and clicking on Remove Column for each one.
- Click OK to close the component editor.
Congratulations! You have successfully configured the QuickBooks Source component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing QuickBooks connection manager.
Related Topics: QuickBooks Connection Manager
Specify the number of rows to be retrieved as a batch. Default is 1000.
Select a QuickBooks object.
Edition Description Desktop Specify query XML. The query should be the same XML format used to serialize QuickBooks query. For example to retrieve all employees with a name starting with "John", use the following query:
Online Specify the WHERE clause of the QuickBooks Online SELECT statement used to retrieve data. (Please only include the conditions after the WHERECLAUSE. Example: TxnDate = '2020-09-30') For further information please check here.
Knowledge Base
- Where can I find the documentation for the QuickBooks Source?
- How to specify ObjectFilter on QuickBooks Source Component for QuickBooks Online?
- How to modify source query dynamically at runtime
- How to work with composite records handling in a QuickBooks source component.
- After modifying the default Data Types in an ETL source, my modifications are lost the next time I open the Source component
What's New
- New: Support for DataExt object.
- Fixed: Component failed with error "Object reference not set to an instance of an object" when trying to use TxnDeleted object (Thank you, Richard).
- New: Redesigned for better support of composite data.
- New: Introduced component.
Overview
The QuickBooks Destination Component is an SSIS Data Flow Component for loading data into QuickBooks objects. The configuration parameters are provided in the standard Data Flow Component dialogs. When working with composite data, a separate input must be set up for the main object and each related composite object.
Demonstration
Quick Start
In this section we will show you how to set up a QuickBooks Destination component.
- Before you begin, configure a QuickBooks connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- Configure a source component that will provide the data to be written to QuickBooks.
- In the SSIS Toolbox, locate the QuickBooks Destination component and drag it onto the Data Flow canvas.
- Double-click on the QuickBooks Destination component on the canvas to open the component dialog.
- Once the component dialog opens, select the connection manager you configured earlier from the Connection drop-down list.
- Choose the desired Action (Create, Update, Delete, or Upsert).
- Choose the Object you will be working with.
- Click OK to close the component dialog.
- Connect the blue arrow from the source component or the data flow transformation that you want to immediately precede the QuickBooks Destination component.
Congratulations! You have successfully configured the QuickBooks Destination component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing QuickBooks connection manager.
Related Topics: QuickBooks Connection Manager
Select the destination object action. This parameter has the options listed in the following table.
Action Description Create Create new records in the destination object. Update Update existing records in the destination object. The ListID and EditSequence columns must be used to specify the records to be updated. Delete Delete existing records from the destination object. The ListID column must be used to specify the records to be deleted. Void Void existing records from the destination object. The ListID column must be used to specify the records to be voided. Specify the number of rows to be sent as a batch.
Specify the QuickBooks object on which the action is to be performed.
Specify how to handle rows with errors.
Contains the unique identifier of the added, updated, deleted or voided record.
Contains the QuickBooks EditSequence field of the added or updated record.
Knowledge Base
- Where can I find the documentation for the QuickBooks Destination?
- How to specify ObjectFilter on QuickBooks Source Component for QuickBooks Online?
- How to work with composite records handling in a QuickBooks destination component.
- How to find which records contain errors in QuickBook destination component.
What's New
- Fixed: Failed to create metadata for 'JournalEntry' object (Thank you, Chris).
- Fixed: Updating composite objects deeper in the structure failed with error "System.MissingMethodException: Method 'CozyRoc.Intuit.QuickBooks.Rest.Line.Line' not found" (Thank you, Brian).
- New: Support for DataExt object.
- Fixed: Component failed to process input data containing diacritic characters. The issue is related to QuickBooks Desktop, which can only process ASCII characters. (Thank you, Richard).
- New: Redesigned for better support of composite data.
- Fixed: Numerous fixes and enhancements (Thank you, Richard).
- New: Introduced component.
Overview
These public interfaces are used in QuickBooks components. They are provided for implementing advanced functionality in your scripts. In order for you to use these interfaces in your project, you have to reference CozyRoc.SSISPlus.dll and CozyRoc.Intuit.dll libraries. Also include CozyRoc.SqlServer.SSIS and CozyRoc.Intuit.QuickBooks namespaces.
IConnection / IQbConnection
When you call QuickBooks Connection Manager AcquireConnection method, it returns object implementing IConnection interface. This is the interface used for interaction with QuickBooks service.
Methods
Connects to QuickBooks service. The method result is IService object.
Closes the connection to QuickBooks service. Use always after you finish working with the service.
Returns true if connected to the service.
Get IService object.
Gets objects list for specific operation. These are the method parameters:
- operation (uint) - It can be one of the options listed in the following table.
Value Description 0 Return objects, which can be read. 1 Return objects, which support record creation. 2 Return objects, which can be updated. 3 Return objects, which permit record removal. 4 Return objects, which permit record void.
Gets specified object composite objects list. These are the method parameters:
- root (string) - object name.
- operation (uint) - It can be one of the options listed in the following table.
Value Description 0 Return objects, which can be read. 1 Return objects, which support record creation. 2 Return objects, which can be updated.
Get object used to manage QuickBooks object. The method returns IObject object. These are the method parameters:
- name (string) - object name.
Properties
Select QuickBooks deployment type. This property has the options listed in the following table.
| Value | Description |
|---|---|
| 0 | QuickBooks Online deployment. |
| 1 | QuickBooks deployed on premise (default). |
Specify the name or IP address of the QuickBooks service.
Specify login to access the QuickBooks service.
Specify key to access the QuickBooks service.
Specify application identifier to access the QuickBooks service.
Specify the number of seconds before timing out session connect. The default value of this property is 100 seconds.
Specify if all clients should use the same connection. This property has the options listed in the following table.
| Value | Description |
|---|---|
| True | All clients use same connection (default). |
| False | Every client uses different connection. |
Specify QBXML version to use with QuickBooks Desktop.
Specify the name or IP address of the proxy server.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
IField / IQbField
This interface is used to retrieve QuickBooks object field information.
Properties
Returns field name.
Returns true if field is object key.
Returns field type.
Returns field length.
Returns field precision.
Returns field scale.
IObject / IQbObject
This interface is used for QuickBooks object management.
Methods
Get object fields. The method returns array of IField objects.
Constructs new in-memory QuickBooks object instance. This property has the options listed in the following table.
| Value | Description |
|---|---|
| 0 | Return instance of result object. |
| 1 | Return instance of create object. |
| 2 | Return instance of update object. |
Sets field value in object instance. These are the method parameters:
- instance (object) - object instance.
- field (string) - field name.
- value (object) - field value.
Returns field value from object instance. These are the method parameters:
- instance (object) - object instance.
- field (string) - field name.
Retrieves objects from QuickBooks service. The method returns array of QuickBooks objects. These are the method parameters:
- query (string) - If data is retrieved from regular QuickBooks object, the query parameter should contain XML string of QuickBooks query. If data is retrieved from composite object, the query parameter should contain object key. Multiple keys are separated with semicolon (;).
- fields (string array) - list of fields to retrieve.
- iterator (ref string) - contains current start iteration position. After data is retrieved, the parameter will contain next iteration position.
- batchSize (int) - number of records to retrieve in one batch.
Creates records in object. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (object array) - array of QuickBooks objects to create.
Updates existing records in object. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (object array) - array of QuickBooks objects with update data.
Updates composite object. These are the method parameters:
- instance (object) - QuickBooks object instance.
- key (string) - composite object key. Multiple keys are separated with semicolon (;).
- list (object array) - array of QuickBooks objects with update data.
Deletes records in object. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (string array) - array of QuickBooks object identifiers to delete.
Voids records in object. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (string array) - array of QuickBooks object identifiers to void.
Properties
Returns the QuickBooks object name.
IStatus / IQbStatus
This interface is used for reporting back status.
Properties
Returns the QuickBooks object identifier.
Returns the QuickBooks edit sequence identifier.
Returns the QuickBooks error code. If zero, there is no error.
Returns the QuickBooks status message.
IService / IQbService
This interface is used for low-level processing with the QuickBooks service.
Methods
Executes QBXML message. The method returns QBXMLMsgsRs object. These are the method parameters:
- qbxml (QBXML) - QBXML object.
Methods
Returns the QuickBooks application type. It can be one of the options listed in the following table.
| Value | Description |
|---|---|
| Online | QuickBooks Online Edition. |
| US | QuickBooks US Edition. |
| CA | QuickBooks Canada Edition. |
| UK | QuickBooks UK Edition. |
| AU | QuickBooks Australia Edition. |
Returns the QuickBooks application version.
Knowledge Base
QuickBooks Desktop Connection
- New: Support for TLS 1.1 and 1.2 protocols in QuickBooks Desktop Edition Gateway.
- New: Updated QuickBooks Online web service proxy to version 43.
- New: A new parameter 'Start on Open' in QuickBooks Desktop Edition Gateway to automatically start the gateway when the application is opened.
- New: Support for QuickBooks Online sandbox environment.
- New: Support for QuickBooks Online OAuth 2.0 authentication.
- New: Updated QuickBooks Desktop web service proxy to version 13.
- New: A new parameter QBXMLVersion for use with QuickBooks Desktop connections.
- Fixed: Failed with error when using 'ItemSites' object (Thank you, Joel).
- New: Enabled support for TLS 1.1 and 1.2 protocols.
- Fixed: Failed to generate new token for the latest QuickBooks Online service.
- New: Support for QuickBooks Online Data Services.
- New: A new parameter 'Inactive sessions close' in QuickBooks Desktop Edition Gateway to close sessions after specified time of inactivity.
- New: Introduced connection.
QuickBooks Destination
- Fixed: Failed to create metadata for 'JournalEntry' object (Thank you, Chris).
- Fixed: Updating composite objects deeper in the structure failed with error "System.MissingMethodException: Method 'CozyRoc.Intuit.QuickBooks.Rest.Line.Line' not found" (Thank you, Brian).
- New: Support for DataExt object.
- Fixed: Component failed to process input data containing diacritic characters. The issue is related to QuickBooks Desktop, which can only process ASCII characters. (Thank you, Richard).
- New: Redesigned for better support of composite data.
- Fixed: Numerous fixes and enhancements (Thank you, Richard).
- New: Introduced component.
QuickBooks Source
- New: Support for DataExt object.
- Fixed: Component failed with error "Object reference not set to an instance of an object" when trying to use TxnDeleted object (Thank you, Richard).
- New: Redesigned for better support of composite data.
- New: Introduced component.
Knowledge Base
- Where can I find the documentation for the QuickBooks Connection?
- Where can I find the documentation for the QuickBooks Destination?
- Where can I find the documentation for the QuickBooks Source?
- How to specify ObjectFilter on QuickBooks Source Component for QuickBooks Online?
- How to get QuickBooks Online Token
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.