Help Manual - Premium Data File Destination

Documentation on SSIS Premium Data File Destination Component

Using the Premium Data File Destination Component

The Premium Data File Destination component is an SSIS data flow pipeline component that can be used to write data from the source into an Avro, ORC, or Parquet file.

The component includes the following four pages to configure how you want to read data.

  • General.
  • Document Designer.
  • Input Columns.
  • Error Handling.

General Page

The General page of the Premium Data File Destination Component allows you to specify the general settings of the component.

Premium Data File Destination Editor

Connection Manager

The Premium Data File Destination Component requires a connection in order to connect to a File. The Connection Manager drop-down will show a list of all connection managers that are available to your current SSIS package.

This component will support the following connection managers:

  • Local File
  • FTPS Connection Manager
  • SFTP Connection Manager
  • Amazon S3 Connection Manager
  • Azure Blob Connection Manager
  • Azure Data Lake Storage Connection Manager
  • Azure Files Connection Manager(since v20.1)
  • Box Connection Manager
  • Dropbox Connection Manager
  • Google Cloud Storage Connection Manager
  • Google Drive Connection Manager(since v21.2)
  • Hadoop Connection Manager
  • OneDrive Connection Manager
  • SharePoint Connection Manager (offered with the SSIS Integration Toolkit for Microsoft SharePoint)
  • WebDAV Connection Manager(since v20.1)
File Format

This option lets you choose the file format that the data will be written to. The following are the currently supported file formats:

  • Avro
  • ORC
  • Parquet
Destination File Path

The Destination File Path specifies the location of the file that you are trying to write to.

Compression Format

There are different compression formats that can be chosen, depending on the selected File Format. Available options are:

  • Deflate (Available only for Avro File Format)
  • ZLib (Available only for ORC File Format)
  • GZip (Available only for Parquet File Format)
  • Snappy (Available only for Parquet File Format)
  • None
Batch size

This option allows you to specify the batch size when writing to a file, Default value is 5000.

Expression fx Icon

Click the blue fx icon to launch SSIS Expression Editor to enable dynamic updates of the property at run time.

Generate Documentation Icon

Click the Generate Documentation icon to generate a Word document that describes the component's metadata including relevant mapping, and so on.

Document Designer Page

The Document Designer page allows you to build the design of the document you are trying to write to, or import the design from an existing document.

Premium Data File Destination Editor

The Document Designer includes the following two tabs:

  • Details View
  • Diagram View

In the Details View tab, the top part of the page is used to manually configure the nodes in the design:

  • Add Node: This button will add a new node to your Document design.
  • Remove Nodes: This button will remove a node from your Document design.
  • Direction buttons: These buttons can be used to rearrange the position of the nodes.
  • Rename Nodes: This option allows you to specify how the node name should be represented.
    • Use Qualified Names: When this option is selected, the output/column name will be set to the full qualified node name based on the node location in the document.
    • Use Short Names: When this option is selected, the output/column name will be set to the given Node Name directly.
  • Filter Columns: This option allows you to show or hide certain Columns in the grid.
    • Show Basic Columns: When this option is selected, only basic columns will be shown in the grid.
    • Show All Columns: When this option is selected, all available columns will be shown in the grid.
  • Filter Nodes: This option allows you to filter the list of nodes shown in the grid by typing a keyword in the textbox.

The Details View grid consists of:

  • Node Type: This option allows you to specify the type of the Node in your document design, There are four options available:
    • Array
    • Object
    • Value
    • Raw: This type can be used when trying to write data in the destination file format under a node exactly as it is in the document.
  • Node Name: The Name of the Node in the document.
  • Input/Column Name: The name which will be used for the node when writing to the file.
  • Source type: The type of the input for a node such as a dedicated Input, Variable, or Column depending on the Node Type.
  • Node-Write Settings: This option allows you to specify the settings of each node such as the datatype or the Variable Name based on the selected Source Type.

  • Premium Data File Destination Editor

    • Namespace (Available only for Avro File Format): A string that qualifies the name.
    • Is Map (Available only for Avro and Parquet File Formats): Specify if the child object is a Map type or not. Map nodes must contain exactly two child nodes called 'Key' and 'Value'.
    • Column Name: The name of the column.
    • Data Type: The data type of the field that can be changed accordingly.
    • Length: This option allows you to specify the Length of the fields. If the data type specified is a string, the length specified here would be the maximum size. If the data type is not a string, the length will be ignored.
    • Precision: This option allows you to specify the number of digits in a number.
    • Scale: This option allows you to specify the number of digits to the right of the decimal point in a number.
    • Code Page: This option allows you to specify the Code Page of the field.
    • Documentation (Available only for Avro File Format): This option allows you to provide documentation for the field you are writing to.
    • Nullable (Available only for Avro and Parquet File Formats): This option specifies if this field's value can be set to NULL.
    • Premium Data Type: This option allows you to choose the data type of a field among all the available data types for the file format you are working with.
    • Enum Values(Available only for Avro File Format): This option allows you to specify the Enum values when working with the Enumeration Premium Data Type in Avro file format.

In the Diagram View tab, you would be able to view the document design as a diagram where you can link different inputs with each other in order to create the hierarchy of your document and establish relationships between the different inputs:

Premium Data File Destination Editor

Create New Input

This option allows you to create a new Input and link it to an existing input if required based on the intended document design.

Delete Selected Input

This option allows you to remove existing Inputs from the diagram

Reset Layout

This option allows you to rest the layout of the diagram which would reorganize the location of each input.

Import

This option allows you to import the design of your document from one of the following three sources:

  • Designer Settings: Import the design from an existing .designer.settings file.
  • Connection Manager: Import the design based on the retrieved document from the connection manager.
  • Local File: Import the design based on a file on your local file system.
Export

Designer Settings: This option allows you to export the current document design to a .designer.settings file which can be used later to import the same design in a different component.

Input Columns Page

The Input Columns Page of the Premium Data File Destination Component allows you to map the columns from upstream components to the defined nodes for the designed document.

Premium Data File Destination - Columns Page

  • Input Column: Select an Input Column from an upstream component.
  • Field: This is the field you are writing data to
  • Data Type: This column indicates the type of value for the current field.
  • Unmap: This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.

Error Handling Page

The Error Handling page allows you to specify how errors should be handled when they happen.

Premium Data File Destination - Error Handling Page

There are three options available.

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to be sent will be redirected to the 'Error Output' output of the Destination Component. As indicated in the screenshot below, the blue output connection represents rows that were successfully sent, and the red 'Error Output' connection represents rows that were erroneous. The 'ErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by the server or the component itself.

Premium Data File Destination - Error Output

Note: Use extra caution when selecting Ignore error option, since the component will remain silent for any errors that have occurred.