Task Factory File Gateway

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

File Gateway Task

File Gateway Task

Task IconTask Description
Task Factory File Gateway Task IconThe File Gateway Task gives users the ability to validate delimited files before performing a downstream action on the selected file.

File Format

Task Factory File Gateway Task File Format

OptionDescription
Column DelimiterIdentifies the character used to distinguish between columns. Users can select between comma, vertical bar (pipes), semicolon, colon, or tabs (\t).
Column Names In First RowThis option should be selected if the file contains column names in the first row.
Row DelimiterThis 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 CharacterIdentifies whether row data is wrapped in single or double quotes.

Columns

Task Factory File Gateway Task Columns

OptionDescription
IndexIdentifies the order (zero-based) of the columns.
NameUsers can set or rename columns.
TypeUsers 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

File Gateway Source

Source IconSource Description
Task Factory File Gateway Source IconThe File Gateway Source gives users 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

OptionDescription
Column DelimiterIdentifies 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 RowThis option should be selected if the file contains column names in the first row.
Row DelimiterThis 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 CharacterIdentifies whether row data is wrapped in single or double quotes.

Columns

Task Factory File Gateway Source Columns

OptionDescription
IndexIdentifies the order (zero-based) of the columns.
NameUsers can set or rename columns.
TypeUsers 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.