Component

Overview

The Query Component is an SSIS Data Flow Component providing the ability to retrieve data from a SQL database by defining data flow input columns as query keys. The Query component supports a variety of connection types as a source.

Demonstration

Demonstration Video

Setup

Use the parameters below to set up the Query component for processing.

General

The General page is used to define the component source database and statement.

Type

Select the connection type. This parameter has the options listed in the following table.

Value	Description
OLEDB	Use the OLEDB Connection Manager for the Connection option.
ADO	Use the ADO Connection Manager for the Connection option.
ADO.NET	Use the ADO.NET Connection Manager for the Connection option.

Connection

Select an existing connection manager.

New

Click New to create a new connection manager.

Related Topics: OLE DB Connection Manager, ADO Connection Manager, ADO.NET Connection Manager

Table / View radio button

Select an existing table or view as the query source.

SQL Query radio button

Specify an SQL statement as the query source.

Insert sequence terminator

Indicates whether an empty line should be inserted at the end of each set of records returned by the query. The default is true.

Options	Description
True	Empty line will be inserted.
False	Empty line will not be inserted.

Columns

The Columns page is used to set up the query key columns and output columns.

Available Input Columns

List of available input columns from the upstream data flow component. Connect only the key column(s) to available lookup columns to use as query key columns.

Available Query Columns

List of available columns in the records being searched. Use the check boxes to include columns in output.

<SQL Command> is a special query column. This allows you to provide a SQL statement from upstream to be used for the query. Map it to the input column containing the statement to populate the SQL statement the Query will use with content from the input row.

Lookup Column

Select lookup column to include in output.

Output Alias

Specify output column name. The default is the name of the lookup column.

Overview

The Sybase Destination Component is an SSIS Data Flow Component, which is used to connect to an Sybase database and to perform bulk loads of data into Sybase tables. The configuration parameters are provided in the standard data flow destination component dialog. This component requires installation of Adaptive Server Enterprise version 15.7.

Demonstration

Demonstration Video

Setup

Use the parameters below to setup the component.

Parameters

IDbConnection

Select an existing ADO.NET Sybase Adaptive Server Enterprise Data Provider connection manager, or click <New connection...> to create a new connection manager.

The Sybase data provider supports multiple bulk-load modes. To set the bulk-load mode you have to include ENABLEBULKLOAD parameter in your connection string and use one of the options below:

Option	Description
0	Bulk-mode is not used (default).
1	Enables bulk-load using array insert.
2	Enables bulk-load using the bulk copy interface.
3	Enables bulk-load using the fast logged bulk copy interface.

Related Topics: ADO.NET Connection Manager

DestinationTable

Specify the destination database table where the data is to be loaded.

BatchSize

Specify the number of rows to be sent as a batch.

Timeout

Specify the number of seconds allowed for the bulk copy operation to complete before it is aborted.

How to modify the destination table dynamically at runtime?

The destination table is available in the data flow properties list. Follow these steps to set up an expression:

• Right-click on the data flow canvas and select the Properties menu.
• Scroll down and find the property named like [Sybase Destination].[DestinationTable]. This is the property containing the destination table.
• Scroll down and find the Expressions property. Set up an expression to modify the statement dynamically.

Samples

• To test the performance of the Sybase Destination component against the standard ODBC Destination, see this package. You must create a TEST_TABLE table with the following columns:
  • COL1 INT
  • COL2 DATETIME
  • COL3 FLOAT
  • COL4 NVARCHAR (255)
  • COL5 NVARCHAR (255)

  The NumberOfRows variable controls the number of rows inserted.

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 dialog. When working with composite data, a separate input must be set up for the main object and each related composite object.

Demonstration

Demonstration Video

Setup

Use the parameters below to set up the component.

Parameters

QUICKBOOKS

Select an existing QuickBooks connection manager.

Related Topics: QuickBooks Connection Manager

Action

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.

BatchSize

Specify the number of rows to be sent as a batch.

DestinationObject

Specify the QuickBooks object on which the action is to be performed.

ErrorRowDisposition (QuickBooks Destination Input)

Specify how to handle rows with errors.

Id (QuickBooks Destination Error Output)

Contains the unique identifier of the added, updated, deleted or voided record.

EditSequence (QuickBooks Destination Error Output)

Contains the QuickBooks EditSequence field of the added or updated record.

Composite object data

The composite object input accepts sets of records, one set for each parent record. Each set of records must be separated with a blank row (all NULLs). The parent object will not finish processing its record until all composite object children have received a blank row to indicate completion of the composite data. See the demonstration video for an example of creating records containing composite data.

How to find which records contain an error

The component's Error Output is used for providing both error information and the record identifier of the processed records. Check the ErrorCode column:

You can set up a standard Conditional Split component to filter non-error records.

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 dialog. A separate output is setup for the main object and each related composite object.

Demonstration

Demonstration Video

Setup

Use the parameters below to set up the component.

Parameters

QUICKBOOKS

Select an existing QuickBooks connection manager.

Related Topics: QuickBooks Connection Manager

BatchSize

Specify the number of rows to be retrieved as a batch. Default is 1000.

Object

Select a QuickBooks object.

QueryXML

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:

<EmployeeQueryRqType>
  <NameFilter>
    <MatchCriterion>StartsWith</MatchCriterion>
    <Name>John</Name>
  </NameFilter>
</EmployeeQueryRqType>

Composite object data

The composite object output contains not only fields from the composite object, but also the parent objects. The output will be populated with sets of records, one set for each parent record. Each set of records is separated with a blank row (all NULLs).

In order to retrieve the composite data for the Quickbooks Desktop version, you must include an element similar to the following in the QueryXML parameter:

<InvoiceQueryRqType>           
  <IncludeLineItems>true</IncludeLineItems>
</InvoiceQueryRqType>

The example above will retrieve item objects associated with the invoice objects.

How to modify source query dynamically at runtime

The source object, query and parent are available in the data flow properties list. Follow these steps to set up an expression:

• Right-click on the data flow canvas and select the Properties menu.
• Scroll down and find the property named like [QuickBooks Source].[QueryXML]. This is the property containing the query.
• Scroll down and find the Expressions property. Set up an expression to modify the query dynamically.

Overview

The Teradata Destination Component is an SSIS Data Flow Component which connects to a Teradata database and bulk loads data into Teradata database tables. It is up to 80x faster than the standard loading using ODBC Destination. The configuration parameters are provided in the standard data flow destination component dialog. The component requires installation of Teradata Tools and Utilities 13.10. Install the following Teradata components:

• Shared ICU libraries for Teradata
• Teradata GSS client
• ODBC Driver for Teradata
• CLIv2
• Teradata Parallel Transporter (Teradata PT)
• Teradata Parallel Transporter API
• Teradata PT Load Operator
• Teradata PT Stream Operator
• Teradata PT Update Operator

Teradata Tools and Utilities post-installation steps

• Define TPT and TPT64 environment variables, referencing the root Teradata Parallel Transporter folder. The default folder is: \Program Files\Teradata\Client\13.10\Teradata Parallel Transporter
• Make sure the following folders are included in your PATH:
  • \Program Files\Teradata\Client\13.10\ODBC Driver for Teradata\Lib\
  • \Program Files\Teradata\Client\13.10\Shared ICU Libraries for Teradata\lib\
  • \Program Files\Teradata\Client\13.10\Teradata Parallel Transporter\bin
  • \Program Files\Teradata\Client\13.10\CLIv2
  • \Program Files\Teradata\Client\13.10\Teradata Parallel Transporter\msg

Demonstration

Demonstration Video

Setup

Use the parameters below to set up the component.

Parameters

IDbConnection

Select an existing ODBC connection manager. The ODBC connection manager is only used for connecting with the Teradata Parallel Transporter. The ODBC API is not used for the bulk-load process.

Related Topics: ODBC Connection Manager

OperatorType

Select the bulk-load driver. This parameter has the options listed in the following table.

Action	Description
Load	Use the Load driver. Use this option when the destination table is empty and there are no secondary indexes defined.
Update	Use the Update driver. Use this option when the destination table already contains data. The destination is locked during processing.
Stream	Use the Stream driver. Use this option when you want to avoid locking of the destination table during processing.

DestinationTable

Specify the destination database table where the data is to be loaded.

ErrorLimit

Specify the maximum number of errors allowed before the component stops processing. The default is 0.

DataEncryption

Indicates whether full security encryption of SQL requests, responses and data is to be used. This parameter has the options listed in the following table.

Value	Description
True	All SQL requests, responses, and data are encrypted.
False	No encryption occurs. This is the default setting.

ErrorTable

Specifies an error table. If not specified, a default error table will be set up with a name based on the selected destination table.

MinSessions

Specifies the minimum number of sessions to be used during processing. The default is 1.

MaxSessions

Specifies the maximum number of sessions to be used during processing. The default is 4.

TraceFile

Specifies the name of the external file to use for tracing messages.

TenacityHours

Specifies the number of hours that the driver continues trying to log on when the maximum number of load and export operations are already running on the Teradata Database. The default value is four hours.

TenacitySleep

Specifies the number of minutes that the driver pauses before retrying to log on when the maximum number of load and export operations are already running on the Teradata Database. The default is six minutes.

AppendErrorTable

Indicates whether or not the Stream Operator will use the existing error table.

Value	Description
True	Stream operator will use the existing error table or create the error table if it does not exist.
False	Stream operator will not use the existing error table (default).

How to modify destination table dynamically at runtime

The destination table is available in the data flow properties list. Follow these steps to set up an expression:

• Right-click on the data flow canvas and select the Properties menu.
• Scroll down and find the property named like [Teradata Destination].[DestinationTable]. This is the property containing the destination table.
• Scroll down and find the Expressions property. Set up an expression to modify the table dynamically.

Samples

• To test the performance of Teradata Destination against the standard ODBC Destination, see this package. In order to use this package, create a TEST_TABLE table with the following columns:
  • COL1 INTEGER
  • COL2 TIMESTAMP (6)
  • COL3 FLOAT
  • COL4 VARCHAR (200)
  • COL5 VARCHAR (200)
  • COL6 DECIMAL (9,5)
  • COL7 VARCHAR (16000)

  The NumberOfRows variable controls the number of inserted rows.

Overview

Sage Destination Component is SSIS Data Flow Component for loading data into Sage SData entity. The configuration options are provided in the standard data flow destination component dialog. A separate input is setup for the main entity and each related composite entity.

Setup

Use the options below to setup the component.

Options

SAGE

Select an existing Sage connection manager.

Related Topics: Sage Connection Manager

Action

Select destination entity action. This property has the options listed in the following table.

Action	Description
Create	Create new record in the destination entity.
Update	Update existing record in the destination entity. You have to specify entity key for the update to work.
Delete	Delete existing record from the destination entity.

Note: After action change, you have to use Refresh command in the 'Column Mappings' tab to reload destination page metadata.

BatchSize

Specify the number of rows to be sent as a batch.

DestinationEntity

Specify the destination Sage SData entity where the data is loaded.

ErrorRowDisposition (Sage Destination Input)

Specify how to handle rows with errors.

Composite entity data

The composite entity input accepts sets of records, one set for each parent record. Each set of records must be separated with blank row (all NULLs). The parent entity will not finish processing its record, until all composite entity children have received blank row to indicate completion.

Overview

Sage Source Component is SSIS Data Flow Component for consuming data from Sage SData Service. The configuration options are provided in the standard data flow source component dialog. A separate output is setup for the main entity and each related composite entity.

Setup

Use the options below to setup the component.

Options

SAGE

Select an existing Sage connection manager.

Related Topics: Sage Connection Manager

BatchSize

Specify the number of rows to be retrieved as a batch.

Entity

Select Sage entity.

QueryParameters

Specify SData query parameters. Multiple parameters are separated with newline. For example to retrieve account entity records where names start with the letter M and include related Addresses information specify the following parameters:

where=AccountName like 'M%'
include=Addresses

Composite entity data

The composite entity output contains not only properties from the composite entity, but also the parent entities. The output will be populated with sets of records, one set for each parent record. Each set of records is separated with blank row (all NULLs).

How to modify query parameters dynamically at runtime?

The source entity and query parameters are available in the data flow properties list. Follow these steps to setup expression:

• Right-click on data flow canvas and select Properties menu.
• Scroll-down and find property named like [Sage].[QueryParameters]. This is the property containing the query parameters.
• Scroll-down and find Expressions property. Setup expression to modify the criteria dynamically.

Overview

Dynamics GP Destination Component is SSIS Data Flow Component for loading data into Dynamics GP object. The configuration options are provided in the standard data flow destination component dialog. A separate input is setup for the main object and each related composite object.

Setup

Use the options below to setup the component.

Options

DYNAMICS-GP

Select an existing Dynamics GP connection manager.

Related Topics: Dynamics GP Connection Manager

Action

Select destination object action. This property has the options listed in the following table.

Action	Description
Create	Create new record in the destination Dynamics GP object.
Update	Update existing record in the destination Dynamics GP object. You have to specify object identifier for the update to work.
Delete	Delete existing record from the destination Dynamics GP object.
Void	Void existing record from the destination Dynamics GP object.

Note: After action change, you have to use Refresh command in the 'Column Mappings' tab to reload destination page metadata.

DestinationObject

Specify the destination Dynamics GP object where the data is loaded.

ErrorRowDisposition (Main object Input)

Specify how to handle rows with errors.

Composite object data

The composite object input accepts sets of records, one set for each parent record. Each set of records must be separated with blank row (all NULLs). The parent object will not finish processing its record, until all composite object children have received blank row to indicate completion.

Overview

OpenAir Destination Component is SSIS Data Flow Component for loading data into OpenAir type. The configuration options are provided in the standard data flow destination component dialog.

Setup

Use the options below to setup the component.

Options

OPENAIR

Select an existing OpenAir connection manager.

Related Topics: OpenAir Connection Manager

Action

Select destination record action. This property has the options listed in the following table.

Action	Description
Create	Create new record in the destination type.
Modify	Modify existing record in the destination type. You have to specify id field for the update to work.
Delete	Delete existing record from the destination type.
Upsert	Update and insert record in the destination type. You have to specify externalid for action to work.

Note: After action change, you have to use Refresh command in the 'Column Mappings' tab to reload destination type metadata.

DestinationType

Specify the destination OpenAir type where the data is loaded.

BatchSize

Specify the number of rows to be sent as a batch.

ErrorRowDisposition (OpenAir Destination Input)

Specify how to handle rows with errors.

Id (OpenAir Destination Error Output)

Contains the unique identifier of the added, updated or deleted record.

How to modify destination type dynamically at runtime?

The destination type is available in the data flow properties list. Follow these steps to setup expression:

• Right-click on data flow canvas and select Properties menu.
• Scroll-down and find property named like [OpenAir Destination].[DestinationType]. This is the property containing the destination type.
• Scroll-down and find Expressions property. Setup expression to modify the destination type dynamically.

Overview

OpenAir Source Component is SSIS Data Flow Component for consuming data from OpenAir Service. The configuration options are provided in the standard data flow source component dialog.

Setup

Use the options below to setup the component.

Options

OPENAIR

Select an existing OpenAir connection manager.

Related Topics: OpenAir Connection Manager

BatchSize

Specify the number of rows to be retrieved as a batch. Default is 1000.

Filter

Specify records filter. Multiple filters are separated with newline. Use filters listed in the following table.

Value	Description
open-envelopes	Returns only records associated with an open envelope.
approved-envelopes	Returns only records associated with an approved envelope.
rejected-envelopes	Returns only records associated with a rejected envelope.
submitted-envelopes	Returns only records associated with a submitted envelope.
nonreimbursed-envelopes	Returns envelopes that have a non-zero balance attribute.
reimbursable-envelope	Returns only records associated with a reimbursable envelope.
open-slips	Returns only records associated with an open slip.
approved-slips	Returns only records associated with an approved slip.
open-timesheets	Returns only records associated with an open timesheet.
approved-timesheets	Returns only records associated with an approved timesheet.
rejected-timesheets	Returns only records associated with a rejected timesheet.
submitted-timesheets	Returns only records associated with a submitted timesheet.
not-exported	Returns only records that have not been marked as exported.
approved-revenue-recognition-transactions	Returns only revenue recognition transactions belonging to approved revenue_container records.
newer-than older-than date-equal-to date-not-equal-to	Date filters. Date filter parameters and multiple date filters are separated with comma. Not supported in importexport type. Example: to retrieve all records where updated field is equal or greater than Jan 1, 2011, set for filter: date-equal-to,newer-than,updated,updated,01-01-2011,01-01-2011 Example: to retrieve all records for the whole day and times use: newer-than,older-than,updated,updated,2011-08-31 23:59:59,2011-09-02

IncludeCustom

Specify to include custom fields in result. This property has the options listed in the following table.

Value	Description
True	The custom fields are included in the result.
False	The custom fields are not included in the result (default).

IncludeDeleted

Specify to include all records in result, including the deleted. This property has the options listed in the following table.

Value	Description
True	All records are included in the result, including the deleted.
False	The deleted records are not included in the result (default).

InputType

Select input OpenAir type.

Note: After input type change, you have to use Refresh command in the 'Column Mappings' tab to reload type metadata.

Method

Select OpenAir retrieve method. This property has the options listed in the following table.

Value	Description
All	Returns all available records (default).
EqualTo	Returns records that have fields that are equal to the field value(s) passed in MethodCriteria. Selecting this value displays the dynamic option MethodCriteria.
EqualTo	Returns records that have fields that are not equal to the field value(s) passed in MethodCriteria. Selecting this value displays the dynamic option MethodCriteria.

Method Criteria

Select variable containing recordset object with method criteria(s) or specify method criteria. To match match multiple values, set field criteria to comma delimited list. To match empty value, set field criteria to empty.

How to modify input type dynamically at runtime?

The input type, filter and method criteria are available in the data flow properties list. Follow these steps to setup expression:

• Right-click on data flow canvas and select Properties menu.
• Scroll-down and find property named like [OpenAir Source].[InputType]. This is the property containing the record.
• Scroll-down and find Expressions property. Setup expression to modify the input type dynamically.