Component

Premium component

Overview

SAS Data Destination Component is SSIS Data Flow Component for loading data into SAS's sas7bdat file. The configuration options are provided in the standard data flow destination component dialog.

Setup

Use the options below to setup the component.

Options

FILE

Select an existing File connection manager.

Related Topics: File Connection Manager

Input Column Format

Specify result SAS variable format. This property is optional.

Related Topics: Formats Syntax

Input Column Informat

Specify result SAS variable informat. This property is optional.

Related Topics: Informats Syntax

Premium component

Overview

SAS Data Source Component is SSIS Data Flow Component for consuming data from SAS's sas7bdat files. The configuration options are provided in the standard data flow source component dialog.

Setup

Use the options below to setup the component.

Options

FILE

Select an existing File connection manager.

Related Topics: File Connection Manager

Overview

Dynamics AX Destination Component is SSIS Data Flow Component for loading data into Microsoft Dynamics AX table. The configuration options are provided in the standard data flow destination component dialog.

Setup

Use the options below to setup the component.

Options

DYNAMICS-AX

Select an existing Dynamics AX connection manager.

Related Topics: Dynamics AX Connection Manager

Action

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

Action	Description
Create	Create new record in the destination table.
Update	Update existing record in the destination table. You have to specify the appropriate table record identifier for the update to work.
Delete	Delete existing record from the destination table. You have to specify the appropriate table record identifier for the delete to work.

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

DestinationTable

Specify the destination Dynamics AX table where the data is loaded.

ErrorRowDisposition (Dynamics AX Destination Input)

Specify how to handle rows with errors.

How to modify destination table dynamically at runtime?

The destination table 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 [Dynamics AX Destination].[DestinationTable]. This is the property containing the destination table.
• Scroll-down and find Expressions property. Setup expression to modify the destination table dynamically.

Overview

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

Setup

Use the options below to setup the component.

Options

DYNAMICS-AX

Select an existing Dynamics AX connection manager.

Related Topics: Dynamics AX Connection Manager

InputTables

Select one or more Dynamics AX tables to retrieve.

Statement

Specify select statement, based on selected input tables. Use %1 for the first table, %2 for the second table and so on.

How to modify input tables and statement dynamically at runtime?

The input tables or query 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 [Dynamics AX Source].[InputTables]. This is the property containing the input tables. Every table is separated with comma and line feed (,\n)
• Scroll-down and find Expressions property. Setup expression to modify input tables dynamically.

Overview

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

Setup

Use the options below to setup the component.

Options

SHAREPOINT

Select an existing SharePoint connection manager.

Related Topics: SharePoint Connection Manager

Action

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

Action	Description
Create	Create new item in the destination list.
Update	Update existing item in the destination list. You have to specify the appropriate list item identifier for the update to work.
Delete	Delete existing item from the destination list. You have to specify the appropriate list item identifier for the delete to work.

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

DestinationList

Specify the destination SharePoint list where the data is loaded.

ErrorRowDisposition (SharePoint Destination Input)

Specify how to handle rows with errors.

How to modify destination list dynamically at runtime?

The SharePoint destination list 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 [SharePoint Destination].[DestinationList]. This is the property containing the destination list.
• Scroll-down and find Expressions property. Setup expression to modify the statement dynamically.

Overview

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

Setup

Use the options below to setup the component.

Options

SHAREPOINT

Select an existing SharePoint connection manager.

Related Topics: SharePoint Connection Manager

BatchSize

Specify the number of rows to be retrieved as a batch. The maximum is 5000.

InputList

Select SharePoint list.

InputQuery

Specify CAML query statement. This property is optional.

InputView

Select SharePoint list view to use for input. This property is optional

IncludeFolders

Specify to include the folders in the result list. The default is false.

IsRecursive

Specify to include sub-folders items in the result list. The default is false.

IncludeHidden (1.5 SR-2)

Specify to include hidden list fields. The default is false.

How to modify source dynamically at runtime?

The source list or query 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 [SharePoint Source].[InputList]. This is the property containing the input SharePoint list.
• Scroll-down and find property named like [SharePoint Source].[InputQuery]. This is the property containing the CAML query.
• Scroll-down and find Expressions property. Setup expression to modify the above properties dynamically.

Overview

Excel Destination Component is SSIS Data Flow Component for inserting data into Microsoft Excel worksheets.

The component dialog contains the following elements:

• General tab - specify destination Excel worksheet and options.
• Columns tab - select columns to insert.
• Error Output tab - specify error handling options.

Setup

Use the options below to setup the component.

General

Connection

Select CozyRoc Excel connection manager.

Related Topics: Excel Connection Manager

Worksheet

Select destination Excel worksheet.

Start Column

Specify destination start column.

Start Row

Specify destination start row.

First row has column names

Specify first row contains column names.

Clear

Specify to clear worksheet before inserting data.

Save At End

Specify to save workbook at the end.

Columns

Use the Columns page to select output columns.

Input Column

View input (source) columns in the order in which the task will write them. You can change this order by first clearing the selected columns in the table discussed above, and then selecting input columns from the list in a different order.

Output Alias

Provide a unique name for each output column. The default is the name of the selected input (source) column.

Error Output

Use the Error Output page to select error handling options.

Input or Output

View the name of the data source.

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 the description of the error.

How to modify destination dynamically at runtime?

The destination worksheet and start columns and rows 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 [Excel Destination Plus].[Worksheet]. This is the property containing the destination worksheet.
• Scroll-down and find property named like [Excel Destination Plus].[StartColumn] or [Excel Destination Plus].[StartRow]. These are the properties containing the destination column and row.
• Scroll-down and find Expressions property. Setup expression to modify the worksheet or region dynamically.

Overview

Excel Source Component is SSIS Data Flow Component for consuming data from Excel worksheets.

The component dialog contains the following elements:

• General tab - specify source Excel worksheet and region.
• Columns tab - select columns to load.
• Error Output tab - specify error handling options.

Setup

Use the options below to setup the component.

General

Connection

Select CozyRoc Excel connection manager.

Related Topics: Excel Connection Manager

Worksheet

Select source Excel worksheet.

First row has column names

Specify first row contains column names.

Named Range (1.5 SR-2)

Select source named range.

Region Column

Specify source region column information.

• Start - start column. The column is 1-based.
• Width - number of columns.

Region Row

Specify source region row information.

• Start - start row. The row is 1-based.
• Height - number of rows. If set to -1, the component will read all used rows from worksheet.

Output

Specify output type. This property has the options listed in the following table.

Value	Description
Formatted Value	Output formatted cell value.
Raw Value	Output raw cell value.
Formula	Output cell formula.

Reset

Click Reset button to reinitialize component metadata.

Columns

Use the Columns page to map an output column to external (source) column.

Available External Columns

View the list of available external columns in the data source. You cannot use this table to add or delete columns.

External Column

View external (source) columns in the order in which the task will read them. You can change this order by first clearing the selected columns in the table discussed above, and then selecting external columns from the list in a different order.

Output Column

Provide a unique name for each output column. The default is the name of the selected external (source) column.

Error Output

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

Input or Output

View the name of the data source.

Column

View 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 the description of the error.

How to modify source dynamically at runtime?

The source worksheet and region 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 [Excel Source Plus].[Worksheet]. This is the property containing the worksheet.
• Scroll-down and find property named like [Excel Source Plus].[Region]. This is the property containing the source region. The property is a string combination containing values for start column, start row, width and height. Each value is separated with comma (,).
• Scroll-down and find Expressions property. Setup expression to modify the worksheet or region dynamically.

Overview

Dynamics CRM Destination Component is SSIS Data Flow Component for loading data into Microsoft Dynamics CRM entity. The configuration options are provided in the standard data flow destination component dialog.

Setup

Use the options below to setup the component.

Options

DYNAMICS-CRM

Select an existing Dynamics CRM connection manager.

Related Topics: Dynamics CRM 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 the appropriate entity record identifier for the update to work.
Delete	Delete existing record from the destination entity. You have to specify the appropriate entity record identifier for the delete to work.

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

DestinationEntity

Specify the destination Dynamics CRM entity where the data is loaded.

IgnoreMissingIdentifier

Specify how unrecognized identifiers are handled. If set to True, the fields containing unrecognized identifiers will be filtered. The default value is False.

DetectDuplicate (1.5 SR-3)

Specify to enable duplicate detection rules during create or update. The default value is False.

ErrorRowDisposition (Dynamics CRM Destination Input)

Specify how to handle rows with errors.

Id (Dynamics CRM Destination Error Output) (1.5 SR-1)

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

How to modify destination entity dynamically at runtime?

The destination entity 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 [Dynamics CRM Destination].[DestinationEntity]. This is the property containing the destination entity.
• Scroll-down and find Expressions property. Setup expression to modify the destination table dynamically.

How to get identifier of newly created entity record?

The identifier of the created entity record is provided in the component's error output, in the Id column. Connect the error output to a destination where you want to store the identifiers.

How to setup activity party list attribute?

The activity party list can be setup with either party's email or using party unique identifier. Multiple party elements are separated with semicolon (;):

• To specify with email use: email1;email2;...
• To specify with unique identifier use: <entity name>:{<guid1>};<entity name>:{<guid2>};...

How to assign roles to users?

Use Dynamics CRM Association Destination script here.

How to relate records between two entities?

Use Dynamics CRM Relationship Destination script here.

How to find which records contain error?

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

You can setup standard Conditional Split component to filter non-error records.

Overview

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

Setup

Use the options below to setup the component.

Options

DYNAMICS-CRM

Select an existing Dynamics CRM connection manager.

Related Topics: Dynamics CRM Connection Manager

BatchSize

Specify the number of rows to be retrieved as a batch. The maximum is 500.

InputType

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

Value	Description
Entity	Input type is Dynamics CRM entity. Selecting this value displays the dynamic option Entity.
FetchXML	Input type is FetchXML query. Selecting this value display the dynamic option FetchXML.

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

Entity

Select Dynamics CRM data entity.

FetchXML

Specify FetchXML statement. Select to design your query in the standard Advanced Find dialog.
Note: Advanced Find is not available in CRM 3.0

How to modify source dynamically at runtime?

The source entity or query 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 [Dynamics CRM Source].[FetchXML]. This is the property containing the query.
• Scroll-down and find Expressions property. Setup expression to modify the query dynamically.