Documentation forTask Factory

Task Factory File Gateway

Note:  File Gateway is available for SQL Server versions 2012 and higher.

File Gateway Task

Task Icon Task Description
Task Factory File Gateway Task Icon The File Gateway Task gives you the ability to validate delimited files before performing a downstream action on the selected file.

File Format

Task Factory File Gateway Task File Format

Option Description
Column Delimiter Identifies the character used to distinguish between columns. Users can select between comma, vertical bar (pipes), semicolon, colon, or tabs (\t).
Column Names In First Row This option should be selected if the file contains column names in the first row.
Row Delimiter This option identifies the performance that creates a new row. Users can select between carriage return line feed (CrLf), line feed only (Linefeed), and carriage return (CarriageReturn.)
Text Character Identifies whether row data is wrapped in single quotes, double quotes, or no specific character.

Columns

Task Factory File Gateway Task Columns

Option Description
Index Identifies the order (zero-based) of the columns.
Name Users can set or rename columns.
Type Users can set the column data type.

File Validation

Users can build rules for validation of the file before row processing begins. Users make such requirements as the file size should be equal to, greater than, or less than a specified size (in kilobytes), a specified number of column headers or rows, or the file's last modified date occurred on, before, or after a specific date. Users should note that multiple conditions can be created to validate the file.

Task Factory File Gateway Task File Validation

Row Validation

Users can build rules that each row must follow. For example, if a column should not contain null, users can add an Is Not Null condition for that column. If a NULL is found during the scan, the validation would fail causing the File Gateway Task to fail and, ultimately, the package to fail.

Task Factory File Gateway Task Row Validation

File Gateway Source

Source Icon Source Description
Task Factory File Gateway Source Icon The File Gateway Source gives you the ability to validate delimited files or rows before performing a downstream action on the selected file.

File Format

Task Factory File Gateway Source File Format

Option Description
Column Delimiter Identifies the character used to distinguish between columns. Users can select between comma, vertical bar (aka pipes), semicolon, colon, or tabs (\t).
Column Names In First Row This option should be selected if the file contains column names in the first row.
Row Delimiter This option identifies the performance that creates a new row. Users can select between carriage return line feed (CrLf), line feed only (Linefeed), and carriage return (CarriageReturn.)
Text Character Identifies whether row data is wrapped in single quotes, double quotes, or no specific character.

Columns

Task Factory File Gateway Source Columns

Option Description
Index Identifies the order (zero-based) of the columns.
Name Users can set or rename columns.
Type Users can set the column data type.

File Validation 

Users can build rules for validation of the file before row processing begins. Users make such requirements as the file size should be equal to, greater than, or less than a specified size (in kilobytes), a specified number of column headers or rows, or the file's last modified date occurred on, before, or after a specific date. 

Note:  Multiple conditions can be created to validate the file.

Task Factory File Gateway Source File Validation

Row Validation 

Users can build rules that each row must follow. For example, if a column should not contain null, users can add an Is Not Null condition for that column. If a NULL is found during the scan, the validation would fail causing the File Gateway Task to fail and, ultimately, the package to fail.

Task Factory File Gateway Source Row Validation

The component has two successful outputs that can be used for Row Validation (valid and invalid output.)

Additional Information:  See the Task Factory Error Row Handling article for more information about this functionality.