Component

Salesforce Destination


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

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

Looking for older releases? Check here.

Overview

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

Demonstration

Demonstration VideoDemonstration Video

Setup

Use the parameters below to set up the component.

Parameters

SFORCE

Select an existing Salesforce connection manager.

Related Topics: Salesforce Connection Manager

Action (1.4 SR-2)

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

Action Description
Create Create a new record in the destination object.
Update Update an existing record in the destination object. You must specify the appropriate object record identifier for the update to work.
Delete Delete an existing record from the destination object. You must specify the appropriate object record identifier for the delete to work.
Upsert (1.4 SR-3) Update and insert a record in the destination object. Selecting this value displays the dynamic parameter ExternalId.

Note: After changing the action, you must use the Refresh command in the 'Column Mappings' tab to reload the destination object metadata.

BatchSize

Specify the number of rows to be sent as a batch. The maximum for regular mode is 200. The maximum for bulk-load mode is 10,000.

Concurrency (1.6 SR-3)

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

Value Description
Parallel Process batches in parallel mode (default).
Serial Process batches in serial mode. Processing in parallel can cause database contention. When this is severe, the job may fail. If you're experiencing this issue, submit the job with serial concurrency mode. This guarantees that batches are processed one at a time. Note that using this option may significantly increase the processing time for a job.
DestinationObject

Specify the destination Salesforce object where the data is to be loaded.

ExternalId (1.4 SR-3)

Specify the field for the external identifiers used in the upsert action.

Mode (1.6)

Select the destination object processing mode. This parameter has the options listed in the following table.

Value Description
Regular Process the data in regular mode.
BulkData Process the data in bulk-load data mode.
BulkBinary Process the data in bulk-load binary mode.
JobIdVariable (1.6 SR-1)

Select variable to store bulk-load job identifier. Optional.

ErrorRowDisposition (Salesforce Destination Input)

Specify how to handle rows with errors.

Id (Salesforce Destination Error Output) (1.4 SR-4)

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

RelationshipName (Salesforce Destination Input -> External Column) (1.5 SR-2)

Specify the relationship name for updating the foreign key lookup with an external identifier.

ReferenceType (Salesforce Destination Input -> External Column) (1.5 SR-2)

Specify the referenced object for updating the foreign key lookup with an external identifier.

ExternalId (Salesforce Destination Input -> External Column) (1.5 SR-2)

Specify the external identifier field for updating the foreign key lookup with an external identifier.

How to modify a destination object dynamically at runtime?

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

How to find which records contain an error?

The component's Error Output is used for providing both error information and the processed record identifier. 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.

How to upload files to Salesforce?

Uploaded files are located in the Attachment object. Use the standard "Import Column" transformation to import file content into the Body field.

Related scripts

Salesforce Source


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

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

Looking for older releases? Check here.

Overview

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

Demonstration

Demonstration VideoDemonstration Video

Setup

Use the parameters below to set up the component.

Parameters

SFORCE

Select an existing Salesforce connection manager.

Related Topics: Salesforce Connection Manager

BatchSize

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

InputType

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

Value Description
Object Input type is Salesforce object. Selecting this value displays the dynamic parameters Object, ObjectFilter (1.5 SR-2).
Statement Input type is a SOQL 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 object metadata.

Mode (1.6 SR-3)

Select the read mode. This parameter has the options listed in the following table.

Value Description
Regular Read data using regular mode (default).
Bulk Read data using bulk mode.
Object

Select the SalesForce data object.

ObjectFilter (1.5 SR-2)

Specify the object filter statement using SOQL statement expressions.

Statement

Specify a SOQL statement.

IncludeDeleted (1.4 SR-1)

Use this parameter to include all records in the result, including the deleted records. This parameter has the options listed in the following table.

Value Description
True The deleted records are included in the result.
False The deleted records are not included in the result.

How to modify the source dynamically at runtime

The Object, ObjectFilter 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 Properties from the menu.
  • Scroll-down and find a property named like [Salesforce Source].[Statement]. This is the property containing the statement.
  • Scroll-down and find the Expressions property. Set up an expression to modify the statement dynamically.

How to include the missing columns when using an SOQL statement

The Salesforce service only returns fields containing data. If there is no data for a specific field, no data is returned. Therefore, it is impossible to set up the appropriate field and field type. There are 3 options to include the missing columns:

Option 1

This option will work fine for SOQL statements without relationships. For statements with relationships, see the other options below.

  1. Set the InputType to Object. Select the object from which you want to retrieve data.
  2. Go to the 'Column Mappings' tab and press 'Refresh' to reload the object's columns.
  3. Set the InputType to ‘Statement’ and specify your statement. Do not refresh the columns in the 'Column Mappings' tab.
  4. If you want to remove columns you don't need, go to the 'Column Mappings' tab and remove the mappings you don't need.
Option 2
  1. Set up a temporary Salesforce record and the required relationship data.
  2. Open the 'Salesforce Source' component setup dialog and specify the SOQL statement retrieving the temporary record data.
  3. Refresh the 'Column Mappings' tab. All needed column metadata should now be included.
  4. Go back to the Salesforce setup window and remove the temporary record and all other unneeded data.
Option 3

This option requires manual definition of the missing columns.

  1. Go to the 'Input and Output Properties' tab.
  2. Go to 'Salesforce Source Output' -> 'External Columns'.
  3. Press 'Add Column'.
  4. Specify the column name and type. Make sure the name is specified properly with the appropriate structured definition.

How to download files from Salesforce

Uploaded files are located in the Attachment object. Use the standard "Export Column" transformation to export the Body field content into a file.

Related scripts

ODBC Destination


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

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

Looking for older releases? Check here.

Overview

The ODBC Destination Component is an SSIS Data Flow Component for loading data into an ODBC-compatible database table. The component can use either the standard ODBC insert interface or the optimized bulk-load engine. The component has been successfully tested with the following databases:

Database Details
SQL Server Support for regular and bulk-load insert.
Oracle Support for regular and bulk-load insert. For a faster alternative see Oracle Destination.
DB2 Support for regular insert only. There is an issue with DB2 bulk support. You can find more details here. For faster alternative check DB2 Destination.
MySQL Support for regular and bulk-load insert. The bulk-load is 30x faster compared to regular ODBC. To use, download MySQL Connector/ODBC.
Vertica Support for regular and bulk-load insert. The bulk-load is 400x faster compared to regular insert. Use BatchMode=ArrayParameter.
Informix Support for regular and bulk-load insert. The bulk-load is 3x faster compared to OLE DB destination. For a faster alternative, see Informix Destination.
PostgreSQL (EnterpriseDB) Support for regular and bulk-load insert. To use, download the PostgreSQL ODBC driver.
Microsoft Access Support for regular and bulk-load insert. The bulk-load is 5x and more faster compared to OLE DB destination.
Sybase Support for regular insert only. You have to use ODBC version 15.05.00.1000. Decimal or numeric field types are not working. We have sent the following incident cases to Sybase: 11553913, 11553762, 11553757.


The configuration options are provided in the standard data flow destination component dialog.

Demonstration

Setup

Use the parameters below to set up the component.

Parameters

IDbConnection

Select an existing ODBC connection manager.

Related Topics: ODBC Connection Manager

DestinationTable

Specify the destination database table where the data is loaded.

BatchSize

Specify the number of rows to be sent as a batch. If the size is set to 1, the component uses regular insert.

Timeout

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

BatchMode (1.6 SR-2)

Specify batch mode. This parameter has the options listed in the following table.

Option Description
BulkOperations Perform bulk-load based on SQLBulkOperations function.
ArrayParameter Perform bulk-load based on array parameters.
ErrorRowDisposition (ODBC Destination Input)

Specify how to handle rows with errors.

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 [ODBC 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 ODBC Destination against the standard OLEDB Destination, see this package. You must create a TEST_TABLE table with the following columns:
    • COL1 INTEGER
    • COL2 TIMESTAMP
    • COL3 FLOAT
    • COL4 NVARCHAR2 (250)
    • COL5 NVARCHAR2 (250)
    • COL6 DECIMAL (16)

    The NumberOfRows variable controls the number of rows inserted.

Address Parse


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

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

Looking for older releases? Check here.

Overview

The Address Parse Component is an SSIS Data Flow Component for parsing, correcting and standardizing Addresses in the United States. With an optional purchase of the latest USPS reference database containing more than 120 million addresses, you can verify your addresses and avoid costly errors. The following output columns are generated from successfully parsed address:

  • Standardized - The postal address in standard format.
  • Type - Type of address
  • Leading - Information before address.
  • Street - Street name
  • Number - Street / P.O. Box number
  • Suite - Suite
  • City - City
  • State - US State
  • Zip - 5 digit Zip code
  • Barcode (1.4 SR-3) - 12 digit delivery point barcode. A purchase of the USPS Address Reference database is required.
  • Predirection (1.5 SR-4) - Pre-direction.
  • Suffix (1.5 SR-4) - Suffix.
  • Postdirection (1.5 SR-4) - Post-direction.

Setup

Use the options below to set up the Address Parse component for processing. The component dialog contains the following elements:

  • General tab - specify parsed address quality, verification in the reference database and how to handle rows that cannot be parsed.
  • Mapping tab – select the column containing the address to process.
  • Error Output tab - specify error handling options.

Parameters

Include Low Quality

Low quality addresses are permitted.

High Quality

Only high quality addresses are permitted.

Check address in reference database

Indicate that the address should be checked in the reference database. An address which cannot be uniquely identified is returned as non-parseable. This option is enabled when you purchase the optional USPS reference database.

Address and city separated by newline (1.4 SR-4)

Indicate that the address and city elements are explicitly separated by a newline character.

Specify how to handle non-parseable addresses

Select an option for handling rows with non-parseable addresses.

When you select Redirect rows to no parse output, the rows are redirected to a “no parse output” and are not handled as errors. The Error option on the Error Output page of the Address Parse Editor dialog box is not available.

When you select any other option in the Specify how to handle non-parseable addresses list box, the rows that are non-parseable are handled as errors. The Error option on the Error Output page is available.

Diagnostic Information (1.4 SR-3)

When this component is used with the USPS reference database, it can provide additional diagnostic information when the address is not found. The information is provided in the standard error output's error code column. See the codes and descriptions listed in the following table.

Value Description
100 No information available.
101 Address is invalid.
102 Address is incomplete.
103 Street not found.
104 Number is invalid.
105 Unable to load streets list.
106 Multiple address matches.

Related scripts

  • Geocode Transformation script - Load latitude and longitude coordinates of input address, using Google's Geocoding service.

Script Component


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

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

Looking for older releases? Check here.

Overview

Note: Please install at least SQL Server 2005 SP2 before using this component.

The Script Component Plus is an SSIS Data Flow component and it is an extension of the standard Microsoft Script Component. It provides benefits similar to Script Task Plus, but for the Microsoft Data Flow Task. You can reuse your data flow script code and build a user interface to simplify your script configuration. These features reduce the need to implement custom SSIS script components. See the public script repository for useful components and samples.

The component dialog contains the following elements:

  • Setup tab - Select script initialization parameters. The Setup tab configuration is made with attributes in the script.
  • Advanced tab - Setup of the standard Microsoft Script Component configuration parameters. You can modify the script code in this tab.
  • Export ScriptExport Script - export the SSIS script to an external file (.scx).
  • Link ScriptLink Script - link to the SSIS script from an external file (.scx). The link can be removed by pressing the button again.

Demonstration

Setup

The initialization and user interface for a script is defined by implementing properties on the ScriptMain class. The user interface is contained in the property grid view and it is available by selecting the Setup tab in the COZYROC Script Component dialog. We urge you to familiarize yourself with the PropertyGrid class. This is the class used for presentation; specified attributes below are related directly to it.

See the samples below for how to implement script component parameters. These samples are specific to the script component. The attributes demonstrated in the Script Task Plus samples can also be used.

How to upgrade a script from 2005 to 2008

Most of the scripts published in the public script repository are for SQL 2005. However they can be easily upgraded to SQL 2008 by following these 10 steps:

Note: In order for this process to work, you must have both SQL 2005 and 2008 installed side-by-side on the same machine.

  • 1. Create an SSIS 2005 package and insert the Data Flow Task. Go to data flow design and insert the COZYROC Script Component Plus.
  • 2. Load a script in the component and break the link to the script. Save the package.
  • 3. Go to the SSIS 2008 solution. Right-click on the solution and select 'Add Existing Package'.
  • 4. Select the package you have created in step 1. SSIS 2008 will do an automatic upgrade of the package and show some warnings.
  • 5. Open the imported package.
  • 6. Double-click on the COZYROC Script Component Plus and enter the script IDE.
  • 7. Right-click on the script project and select Properties.
  • 8. Go to the References tab and add a reference to CozyRoc.SSISPlus.2008 from the "Microsoft SQL Server\100\SDK\Assemblies" folder.
  • 9. Save and close the project settings. Make sure the script compiles.
  • 10. Close the script IDE and export the script.

Component connection parameter

The Connection attribute described in Script Task Plus has an additional constructor with the isConnectionID parameter. When the parameter is set to true, the component connection property will contain the connection ID, instead of the connection name. Set this parameter to true in your script component to be able to use the package connection manager.

Extended script component base class

The extended script component base class contains the following additional features:

  • HostComponent - property to access the hosting component object.
  • DesignConnections - property containing package connections list. To be used only in design mode.

Extended buffer base class

The extended buffer base class contains the following additional features:

  • Buffer - property to access the internal buffer object. This object is usually needed when working with dynamically processed columns.
  • StaticInputColumns - access to the list of specified static input columns.
  • StaticOutputColumns - access to the list of specified static output columns.

Component input parameter

  • Define the property associated with the Input attribute.
    <Input()> _
    Property Input() As String
        Get
            Input = m_input
        End Get
        Set(ByVal value As String)
            m_input = value
        End Set
    End Property

Component input column parameter

  • Define property attributed with the InputColumn attribute, setting for parameter the property containing input name. Defining InputColumn without parameter will retrieve the first component input columns. You can ask the user for single or multiple columns selection.
    <InputColumn("Input")> _
    Property InputColumn() As String
        Get
            InputColumn = m_inputColumn
        End Get
        Set(ByVal value As String)
            m_inputColumn = value
        End Set
    End Property

Component input external column parameter

  • Define property attributed with the InputExternalColumn attribute, setting for parameter the property containing input name. Defining InputExternalColumn without parameter will retrieve the first component input external columns. You can ask the user for single or multiple columns selection.
    <InputExternalColumn("Input")> _
    Property InputExternalColumn() As String
        Get
            InputExternalColumn = m_inputExternalColumn
        End Get
        Set(ByVal value As String)
            m_inputExternalColumn = value
        End Set
    End Property

Component input virtual column parameter

  • Define property attributed with the InputVirtualColumn attribute, setting for parameter the property containing input name. Defining InputVirtualColumn without parameter will retrieve the first component input virtual columns. You can ask the user for single or multiple columns selection.
    <InputVirtualColumn("Input")> _
    Property InputVirtualColumn() As String
        Get
            InputVirtualColumn = m_inputVirtualColumn
        End Get
        Set(ByVal value As String)
            m_inputVirtualColumn = value
        End Set
    End Property

Component output parameter

  • Define the property associated with the Output attribute.
    <Output()> _
    Property Output() As String
        Get
            Output = m_output
        End Get
        Set(ByVal value As String)
            m_output = value
        End Set
    End Property

Component output column parameter

  • Define property attributed with the OutputColumn attribute, setting for parameter the property containing output name. Defining OutputColumn without parameter will retrieve the first component output columns. You can ask the user for single or multiple columns selection.
    <OutputColumn("Output")> _
    Property OutputColumn() As String
        Get
            OutputColumn = m_outputColumn
        End Get
        Set(ByVal value As String)
            m_outputColumn = value
        End Set
    End Property

Component output external column parameter

  • Define property attributed with the OutputExternalColumn attribute, setting for parameter the property containing output name. Defining OutputExternalColumn without parameter will retrieve the first component output external columns. You can ask the user for single or multiple columns selection.
    <OutputExternalColumn("Output")> _
    Property OutputExternalColumn() As String
        Get
            OutputExternalColumn = m_outputExternalColumn
        End Get
        Set(ByVal value As String)
            m_outputExternalColumn = value
        End Set
    End Property

Component Column Mappings tab (1.4 SR-3)

  • To enable the script Mappings tab, associate your script main class with the ColumnMappings attribute.
    <ColumnMappings()> _
    Public Class ScriptMain
        Inherits UserComponent
    ...

EDI Source


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

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

Looking for older releases? Check here.

Overview

The EDI Source Component is an SSIS Data Flow Component for parsing EDI format files. The configuration parameters are provided in the standard data flow source component dialog. The component has already been successfully used to process 210, 271, 277, 810, 812, 820, 822, 832, 834, 835, 837, 850, 852, 855, D95B formats.

COZYROC offers consulting services, in case you don't have the resources internally to build the configuration file. For details, contact us.

Note: In SSIS+ 1.3 SR-2, the EDI Source component has been considerably enhanced. We recommend that you install SSIS+ 1.3 SR-2 or a later release before using it.

Demonstration

Setup

Use the parameters below to set up the EDI component.

Parameters

Configuration

Specify the EDI configuration file. More information is provided below about the configuration format.

IsInputFileVariable

Indicate whether or not the input file name is stored in a variable. This parameter has the options listed in the following table.

Value Description
True The input file name is stored in a variable. Selecting this value displays the dynamic parameter InputFileVariable.
False The input file name is specified. Selecting this value displays the dynamic parameter InputFile.
InputFile

Select the input file name.

InputFileVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

SegmentDelimiter (1.4)

Override the segment delimiter loaded from the configuration file. This parameter is not visible in the setup dialog, but can be set in the data flow task properties pane.

FieldDelimiter (1.4)

Override the field delimiter loaded from the configuration file. This parameter is not visible in the setup dialog, but can be set in the data flow task properties pane.

ComponentDelimiter ((1.4)

Override the component delimiter loaded from the configuration file. This parameter is not visible in the setup dialog, but can be set in the data flow task properties pane.

SubcomponentDelimiter (1.4)

Override the sub-component delimiter loaded from the configuration file. This parameter is not visible in the setup dialog, but can be set in the data flow task properties pane.

ColumnLength (1.4 SR-1)

Specify the column length for EDI elements. The default is 250.

Configuration file

The EDI source component configuration is an XML file based on the Milyn configuration schema. The latest schema is available here. These are the configuration elements:

edimap

This is the top level root element of the XML document.


Element relationships

Relationship Element
Parent elements <none>
Child elements description, delimiters, segments
description

This element contains a user-friendly description of the configuration file.

Attributes

Attribute Description
name Description of the configuration file.
version Version of the configuration file.


Element relationships

Relationship Element
Parent elements edimap
Child elements <none>
delimiters

This element contains the EDI file delimiters for the segment, field, component and sub-component parts. Special characters like line feed character are specified using XML Character References.

Attributes

Attribute Description
segment Segments delimiter character.
field Fields delimiter character.
component Components delimiter character.
sub-component Sub-components delimiter character.


Element relationships

Relationship Element
Parent elements edimap
Child elements <none>
options (1.3 SR-4)

This element contains parameters for controlling the EDI parser.

Attributes

Attribute Description
skipNewline (1.3 SR-4) Indicate that carriage return / line feed characters should be ignored. Starting from 1.4 SR-3 this option is replaced by the skipCharacters option below. To get the equivalent of specifying this option to true, set skipCharacters to "&#13;&#10;"
undefinedFail (1.4 SR-2) Specify how to handle the appearance of new field, component and sub-component elements. If set to false the parser will ignore the new elements.
skipCharacters (1.4 SR-3) Specify a list of characters to be ignored.


Element relationships

Relationship Element
Parent elements edimap
Child elements <none>
segments

This element puts segments read from the EDI file into groups.

Attributes

Attribute Description
xmltag Name of the segments group.


Element relationships

Relationship Element
Parent elements edimap
Child elements segment
segment

This element specifies an EDI segment. The segment is represented as a separate Output in the SSIS data flow, if it repeats. If the segment repeats once, the segment elements are included as part of the parent segment to serve as context for other segments. You can specify multiple segments.

Attributes

Attribute Description
segcode Code found in the EDI file, which establishes the mapping relationship to this segment. Starting from 1.3 SR-2, this attribute accepts a regular expression for matching complex segments.
xmltag Name of the segment.
truncatable When set to true, the parser will not generate errors when the segment does not contain trailing fields, components, and sub-components, which are not required.
minOccurs Minimum amount of times this segment occurs. If not specified the default is 1.
maxOccurs Maximum amount of times this segment occurs. Specify -1 if this segment may occur an unlimited number of times. If not specified, the default is 1.
segid (1.4 SR-3) Optional attribute which, if specified, will create an additional field for the segment's data flow output. The field will contain a unique segment identifier.


Element relationships

Relationship Element
Parent elements segments
Child elements field
field

This element specifies an EDI field. Each field is represented as a column in the SSIS data flow. You may specify multiple fields.

Attributes

Attribute Description
xmltag Name of the field.
truncatable When set to true, the parser will not generate errors when the field does not contain trailing components or sub-components which are not required.
required Specifies whether or not the field is required. By default it is set to false indicating the field is not required.


Element relationships

Relationship Element
Parent elements segment
Child elements component
component

This element specifies an EDI component. Each component is represented as a column in the SSIS data flow. You may specify multiple components.

Attributes

Attribute Description
xmltag Name of the component.
truncatable When set to true, the parser will not generate errors when the component does not contain trailing sub-components which are not required.
required Specifies whether the component is required. By default it is set to false indicating the component is not required.


Element relationships

Relationship Element
Parent elements field
Child elements sub-component
sub-component

This element specifies an EDI subcomponent. Each subcomponent is represented as a column in the SSIS data flow. You may specify multiple subcomponents.

Attributes

Attribute Description
xmltag Name of the subcomponent.
required Specifies whether the subcomponent is required. By default it is set to false indicating the subcomponent is not required.


Element relationships

Relationship Element
Parent elements component
Child elements <none>

How to exclude EDI elements you don't need from data flow? (1.5 SR-4)

To exclude an element from the data flow, prefix the xmltag attribute value with the IGNORE keyword like this:

<medi:field xmltag="IGNORE ISA01 I01 Authorization Information Qualifier" />

Related scripts

Case Study

Case study by EPIC Pharmacies, Inc.

Samples

  • The sample package used in the demonstration video can be download from here.

Informix Destination


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

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

Looking for older releases? Check here.

Overview

The Informix Destination Component is an SSIS Data Flow Component, which connects to an Informix database and bulk loads data into Informix database tables. It is much faster than the standard loading using the OLE DB Destination. The configuration parameters are provided in the standard data flow destination component dialog. The component requires installation of the IBM Data Server Runtime Client.

Demonstration

Setup

Use the parameters below to set up the component.

Parameters

IDbConnection

Select an existing ADO.NET IBM Informix .NET Data Provider connection manager, or click <New connection...> to create a new connection manager.

Related Topics: ADO.NET Connection Manager

DestinationTable

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

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 [Informix Destination].[DestinationTable]. This is the property containing the destination table.
  • Scroll down and find the Expressions property. Set up your expression to modify the statement dynamically.

Samples

  • To test the performance of the Informix Destination component against the standard OLEDB Destination, see this package. You must create a TEST_TABLE table with the following columns:
    • col1 INTEGER
    • col2 DATE
    • col3 FLOAT
    • col4 NVARCHAR (250)
    • col5 NVARCHAR (250)
    • col6 DECIMAL (16)

    The NumberOfRows variable controls the number of rows inserted.

DB2 destination


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

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

Looking for older releases? Check here.

Overview

The DB2 Destination Component is an SSIS Data Flow Component, which is used to connect to a DB2 database and performs a bulk load of data into DB2 database tables. It is much faster than standard loading using OLE DB Destination. The configuration parameters are provided in the standard data flow destination component dialog. The component requires installation of the IBM Data Server Runtime Client.

Demonstration

Setup

Use the parameters below to set up the component.

Options

IDbConnection

Select an existing ADO.NET IBM DB2 .NET Data Provider connection manager, or click <New connection...> to create a new connection manager.

Related Topics: ADO.NET Connection Manager

DestinationTable

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

How to modify a 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 data flow canvas and select the Properties menu.
  • Scroll down and find the property named like [DB2 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 DB2 Destination component against the standard OLEDB Destination, see this package. You must create a TEST_TABLE table with the following columns:
    • COL1 INTEGER
    • COL2 TIMESTAMP
    • COL3 DOUBLE
    • COL4 VARCHAR2 (250)
    • COL5 VARCHAR2 (250)
    • COL6 DECIMAL (5, 2)

    The NumberOfRows variable controls the number of rows inserted.

Oracle Destination


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

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

Looking for older releases? Check here.

Overview

The Oracle Destination Component is an SSIS Data Flow Component, which is used to connect to an Oracle database and to perform bulk loads of data into Oracle tables. It uses a direct path load approach. Using a direct path load is faster than standard loading (using the OLE DB Destination) as it formats Oracle data blocks and writes the data blocks directly to the data files. This eliminates considerable processing overhead. The configuration parameters are provided in the standard data flow destination component dialog. This component requires installation of Oracle 11g ODAC 11.1.0.6.20.

Demonstration

Setup

Use the parameters below to setup the component.

Parameters

IDbConnection

Select an existing ADO.NET Oracle Data Provider for .NET connection manager, or click <New connection...> to create a new connection manager.

Related Topics: ADO.NET Connection Manager

DestinationTable

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

DestinationPartition

Specify the destination table partition where the data is to be loaded. This property is not required if the destination is a single partition table.

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 [Oracle 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 Oracle Destination component against the standard OLEDB Destination, see this package. You must create a TEST_TABLE table with the following columns:
    • COL1 NUMBER
    • COL2 TIMESTAMP
    • COL3 FLOAT
    • COL4 NVARCHAR2 (250)
    • COL5 NVARCHAR2 (250)

    The NumberOfRows variable controls the number of rows inserted.

Data Flow Source


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

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

Looking for older releases? Check here.

Overview

The Data Flow Source Component is an SSIS Data Flow Component for consuming data in a data flow from the Data Flow Destination component. The configuration parameters are provided in the standard data flow source component dialog.

Setup

Use the parameters below to connect to the Data Flow Destination component.

Parameters

PackageConnection

Specify the package connection.

DataFlowDestination

Type the data flow destination, or click the browse button () to locate and select it.

WaitStart (1.2 SR-3)

Use this variable to contain the thread synchronization event object. If specified, the component will wait for a signal before starting to process data. Check the following script for an example showing how to handle this variable:

    ' Signals wait start variable. If not initialized yet, initialize to ManualResetEvent.
    Private Sub SignalStart_()
        Dim vars As Variables
        Dim eventSignal As EventWaitHandle

        Try
            Call Dts.VariableDispenser.LockOneForWrite(WaitStart, vars)
            If vars(WaitStart).DataType = TypeCode.Object Then
                eventSignal = TryCast(vars(WaitStart).Value, EventWaitHandle)
                If eventSignal Is Nothing Then
                    ' Signal variable is not yet initialized.
                    eventSignal = New ManualResetEvent(True)
                    vars(SignalEnd).Value = eventSignal
                Else
                    ' Set signal.
                    Call eventSignal.Set()
                End If
            Else
                Throw New ApplicationException("Invalid variable type")
            End If
        Finally
            If Not vars Is Nothing Then
                Call vars.Unlock()
            End If
        End Try
    End Sub    ' SignalStart_

 

Syndicate content