Component

REST Destination

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

All Downloads ----- Join VIP ----- Questions?

Overview

The REST Destination Component is an SSIS Data Flow Component for inserting data into a REST resource.

The component dialog contains the following elements:

  • General tab - specify the destination REST resource and options.
  • Mapping tab – setup the columns to insert.
  • Error Output tab - specify error handling options.


Setup

Use the parameters below to set up the component.

General

Connection

Select the COZYROC REST connection manager.

Related Topics: REST Connection Manager

Action

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

Action Description
Create Create new instances in the destination REST resource.
Update Update existing instances in the destination REST resource.
Delete Delete existing instances from the destination REST resource.
Upsert Update and insert records in the destination REST resource.
Resource

Select the destination REST resource.

Batch size

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

Extended Metadata

Specify additional custom metadata.

Parameters

Specify additional parameters for data insertion.

Mapping

Use the Mapping tab to map input columns to destination REST resource columns.

Input Column

Select the input columns.

Destination Column

Select the destination REST columns.

Error Output

Use the Error Output tab to select error handling options for the component.

Input or Output

View the name of the destination.

Error

Specify what should happen when an error occurs: ignore the failure, redirect the row, or fail the component.

Related Topics: Handling Errors in Data

Description

View a description of the error.

How to modify a destination resource dynamically at runtime

The destination resource 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 [REST Destination].[DestinationResource]. This is the property containing the destination resource.
  • Scroll down and find the Expressions property. Set up an expression to modify the destination resource dynamically.

How to find which records contain errors

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

ErrorCode Description
-1 Not an error record.
>0 Error record. Check the ErrorDescription column for more details.

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

REST Source

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

All Downloads ----- Join VIP ----- Questions?

Overview

The REST Source Component is an SSIS Data Flow Component for consuming data from REST services.

The component dialog contains the following elements:

  • General tab - specify the source REST resource.
  • Columns tab - select the columns to load.
  • Error Output tab - specify error handling options.


Setup

Use the parameters below to set up the component.

General

Connection

Select the COZYROC REST connection manager.

Related Topics: REST Connection Manager

Resource

Select a REST resource as the source.

Batch size

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

Extended Metadata

Specify additional custom metadata.

Parameters

Specify one or more filters for the data retrieval. A grid with filters help is displayed under the filter's entry grid.

Columns

Use the Columns tab to specify which columns are to be processed from the source REST resource and to specify new names for the columns being processed, if desired.

Available External Columns

This table lists the columns, by name, that are available to be processed from the source REST resource. If there are columns that you do not want to process, uncheck them from the list.

External Column

Shows the columns that will be processed from the source REST resource based on the columns selected in the Available External Columns list.

Output Column

This shows the column name that will be used during processing. The name of any column may be changed by typing over the text in this list. Each name must be unique.

Error Output

Use the Error Output page to select error handling options for the columns.

Input or Output

View the name of the data source.

Column

View the selected source column.

Error

Specify what should happen when an error occurs: ignore the failure, redirect the row, or fail the component.

Related Topics: Handling Errors in Data

Truncation

Specify what should happen when a truncation occurs: ignore the failure, redirect the row, or fail the component.

Description

View a description of the error.

How to modify the source dynamically at runtime

The source resource and filters 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 [REST Source].[Resource]. This is the property containing the resource.
  • Scroll down and find the property named like [REST Source].[ResourceParameter]. This is the property containing the resource parameters. The property is a string combination with format [name]=[value]. Each value is separated with a newline (\n).
  • Scroll down and find the Expressions property. Set up an expression to modify the resource or parameters dynamically.

QlikView Source

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

All Downloads ----- Join VIP ----- Questions?

Overview

The QlikView Source Component is an SSIS Data Flow Component for consuming data from QlikView's QVX files. The configuration parameters are provided in the standard data flow source component dialog.

Setup

Use the parameters below to set up the component.

Options

FILE

Select an existing File connection manager.

Related Topics: File Connection Manager

QlikView Destination

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

All Downloads ----- Join VIP ----- Questions?

Overview

The QlikView Destination Component is an SSIS Data Flow Component for loading data into a QlikView QVX file. The configuration parameters are provided in the standard data flow destination component dialog.


Setup

Use the parameters below to set up the component.

Parameters

FILE

Select an existing File connection manager.

Related Topics: File Connection Manager

Teradata Source

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

All Downloads ----- Join VIP ----- Questions?

Overview

The Teradata Source Component is an SSIS Data Flow Component which connects to a Teradata database and bulk reads data from Teradata database tables. The configuration parameters are provided in the standard data flow source component dialog. The component requires installation of Teradata Tools and Utilities 13.10 and above. 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


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

InputType

Specify the input type. This parameter has the options listed in the following table.

Value Description
Table Input type is Teradata table. Selecting this value displays the dynamic parameters Table.
Statement Input type is a SQL statement. Selecting this value displays the dynamic parameter Statement.

Note: After making this change, use the Refresh command in the 'Column Mappings' tab to reload the source metadata.

Table

Select the Teradata table.

Statement

Specify the SQL statement.

BlockSize

Specifies the block size in bytes used when returning data to the client. The minimum is 256 bytes. The default and maximum are 64330 bytes for Teradata Database V2R6.0 and later.

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.
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 the Export driver attempts to log on when the maximum number of load and export
operations are already running on the Teradata Database. The default value is four (4) hours.

TenacitySleep

Specifies the number of minutes the Export driver pauses before attempting to log on when the maximum number of load and export operations are already running on the Teradata Database. The default is six (6) minutes.

How to modify the source dynamically at runtime

The Table or Statement parameters are available for setup 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 Source].[Table]. This is the property containing the source table.
  • Scroll down and find the Expressions property. Set up an expression to modify the table dynamically.

Tableau Destination

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

All Downloads ----- Join VIP ----- Questions?

Overview

The Tableau Destination Component is an SSIS Data Flow Component for loading data into Tableau Data Extract (TDE) file. The configuration parameters are provided in the standard data flow destination component dialog.


Data Extract API

To use the component you have to download Data Extract API and follow these steps to deploy (64 bit machine):

  1. Extract TDE-API-C-Java-32Bit.zip package into a folder.
  2. Create a sub folder named TDE in the COZYROC 32bit installation folder. The default installation folder is: C:\Program Files (x86)\COZYROC\SSIS
  3. Copy step 1 extracted sub folder bin content into TDE folder.
  4. Extract TDE-API-C-Java-64Bit.zip package into a folder.
  5. Create a sub folder named TDE in the COZYROC 64bit installation folder. The default installation folder is: C:\Program Files\COZYROC\SSIS
  6. Copy step 4 extracted sub folder bin content into TDE folder.

Setup

Use the parameters below to set up the component.

Parameters

FILE

Select an existing File connection manager.

Related Topics: File Connection Manager

Collation

Select table collation.

Query

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

All Downloads ----- Join VIP ----- Questions?

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 VideoDemonstration 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.

Sybase Destination

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

All Downloads ----- Join VIP ----- Questions?

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

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.

QuickBooks Destination

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

All Downloads ----- Join VIP ----- Questions?

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 VideoDemonstration 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:

ErrorCode Description
-1 Not an error record.
>0 Error record. Check the ErrorDescription column for more details.

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

QuickBooks Source

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

All Downloads ----- Join VIP ----- Questions?

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 VideoDemonstration 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.

ObjectFilter
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:

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

Specify the WHERE clause of the QuickBooks Online SELECT statement used to retrieve data.

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 ObjectFilter 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].[ObjectFilter]. This is the property containing the query.
  • Scroll down and find the Expressions property. Set up an expression to modify the query dynamically.
Syndicate content