File Gateway Source
The "File Gateway Source" gives users the ability to validate delimited files or rows before performing a downstream action on the selected file.
File Gateway is available for SQL versions 2012 and higher.
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 or double quotes.
Index - Identifies the order (zero-based) of the columns.
Name - Users can set or rename columns.
Type - Users can set the column data type.
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.
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.
The component has two "successful" outputs that can be used for Row Validation (valid and invalid output.)
Please see the Error Row Handling page for more information about this functionality.