The
Flat File source reads data from a text file. The text file can be in
delimited, fixed width, or mixed format.
·
Delimited format uses column and row delimiters to define
columns and rows.
·
Fixed width format uses width to define columns and rows. This
format also includes a character for padding fields to their maximum width.
·
Ragged right format uses width to define all columns, except for
the last column, which is delimited by the row delimiter.
Flat File Source Editor: It
contains 3 pages
1. Connection
Manager Page
2.
Columns page
3.
Error Output page
1.
Connection Manager page of the Flat
File Source Editor dialog
box to select the connection manager that the Flat File source will use. The
Flat File source reads data from a text file, which can be in a delimited,
fixed width, or mixed format.
A
Flat File source can use one of the following types of connection managers:
·
A Flat File connection manager if the source is a single flat
file.
·
A Multiple Flat Files connection manager if the source is
multiple flat files and the Data Flow task is inside a loop container, such as
the For Loop container. On each loop of the container, the Flat File source
loads data from the next file name that the Multiple Flat Files connection
manager provides.
Options:
Select an existing
connection manager from the list, or create a new connection manager by
clicking New.
New
Create a new connection manager by
using the Flat File Connection Manager Editor dialog box.
Retain
null values from the source as null values in the data flow
Specify whether to keep null
values when data is extracted. The default value of this property is false. When this value is false, the Flat File source replaces null values
from the source data with appropriate default values for each column, such as
empty strings for string columns and zero for numeric columns.
Preview
Preview results by using the Data View dialog box. Preview can display up to 200
rows.
Use the General page of the Flat File Connection Manager Editor dialog box to select a file and data format. A
flat file connection enables a package to connect to a text file.
To learn more about
the Flat File connection manager,
Connection
manager name
Provide a unique name for the flat file
connection in the workflow. The name provided will be displayed within SSIS
Designer.
Description
Describe the connection. As a best practice,
describe the connection in terms of its purpose, to make packages self-documenting
and easier to maintain.
File
name
Type the path and file name to use in the flat
file connection.
Browse
Locate the file name to use in the flat file
connection.
Locale
Specify the locale to provide
language-specific information for ordering and for date and time formats.
Unicode
Indicate whether to use Unicode. If you use
Unicode, you cannot specify a code page.
Code
page
Specify the code page for non-Unicode text.
Format
Indicate whether the file uses delimited,
fixed width, or ragged right formatting.
|
Value
|
Description
|
|
Delimited
|
Columns are
separated by delimiters, specified on the Columns page.
|
|
Fixed width
|
Columns have a fixed
width.
|
|
Ragged right
|
Ragged right files
are files in which every column has a fixed width, except for the last column.
It is delimited by the row delimiter.
|
Text
qualifier
Specify the text qualifier to use. For
example, you can specify that text fields are enclosed in quotation marks.
After
you select a text qualifier, you cannot re-select the None option. Type None to de-select the text qualifier.
Header
row delimiter
Select from the list of delimiters for header
rows, or enter the delimiter text.
|
Value
|
Description
|
|
{CR}{LF}
|
The header row is
delimited by a carriage return-line feed combination.
|
|
{CR}
|
The header row is
delimited by a carriage return.
|
|
{LF}
|
The header row is
delimited by a line feed.
|
|
Semicolon {;}
|
The header row is
delimited by a semicolon.
|
|
Colon {:}
|
The header row is
delimited by a colon.
|
|
Comma {,}
|
The header row is
delimited by a comma.
|
|
Tab {t}
|
The header row is
delimited by a tab.
|
|
Vertical bar {|}
|
The header row is
delimited by a vertical bar.
|
Header
rows to skip
Specify the number of header rows or initial
data rows to skip, if any.
Column
names in the first data row
Indicate whether to expect or provide column
names in the first data row.
Use
the Columns page of the Flat File Connection Manager
Editor dialog
box to specify the row and column information, and to preview the file.
Static Options
Connection manager name
Provide a unique name for
the Flat File connection in the workflow. The name provided will be displayed
within SSIS Designer.
Description
Describe the connection. As
a best practice, describe the connection in terms of its purpose, to make
packages self-documenting and easier to maintain.
Flat File Format Dynamic Options
Format = Delimited
Row delimiter
Select from the list of
available row delimiters, or enter the delimiter text.
|
Value
|
Description
|
|
{CR}{LF}
|
Rows are delimited by a carriage return-line feed
combination.
|
|
{CR}
|
Rows are delimited by a carriage return.
|
|
{LF}
|
Rows are delimited by a line feed.
|
|
Semicolon {;}
|
Rows are delimited by a semicolon.
|
|
Colon {:}
|
Rows are delimited by a colon.
|
|
Comma {,}
|
Rows are delimited by a comma.
|
|
Tab {t}
|
Rows are delimited by a tab.
|
|
Vertical bar {|}
|
Rows are delimited by a vertical bar.
|
Column delimiter
Select from the list of
available column delimiters, or enter the delimiter text.
|
Value
|
Description
|
|
{CR}{LF}
|
Columns are delimited by a carriage return-line feed
combination.
|
|
{CR}
|
Columns are delimited by a carriage return.
|
|
{LF}
|
Columns are delimited by a line feed.
|
|
Semicolon {;}
|
Columns are delimited by a semicolon.
|
|
Colon {:}
|
Columns are delimited by a colon.
|
|
Comma {,}
|
Columns are delimited by a comma.
|
|
Tab {t}
|
Columns are delimited by a tab.
|
|
Vertical bar {|}
|
Columns are delimited by a vertical bar.
|
Refresh
View the effect of changing
the delimiters to skip by clicking Refresh.
This button only becomes visible after you have changed other connection options.
Preview rows
View sample data in the flat
file, divided into columns and rows by using the options selected.
Reset Columns
Remove all but the original
columns by clicking Reset
Columns.
Format = Fixed Width
Font
Select the font in which to
display the preview data.
Source data columns
Adjust the width of the row
by sliding the vertical red row marker, and adjust the width of the columns by
clicking the ruler at the top of the preview window
Row width
Specify the length of the
row before adding delimiters for individual columns. Or, drag the vertical red
line in the preview window to mark the end of the row. The row width value is
automatically updated.
Reset Columns
Remove all but the original
columns by clicking Reset
Columns.
Format = Ragged
Right
 Note |
|
Ragged right files are files in which every column has
a fixed width, except for the last column. It is delimited by the row
delimiter.
|
Font
Select the font in which to
display the preview data.
Source data columns
Adjust the width of the row
by sliding the vertical red row marker, and adjust the width of the columns by
clicking the ruler at the top of the preview window
Row delimiter
Select from the list of
available row delimiters, or enter the delimiter text.
|
Value
|
Description
|
|
{CR}{LF}
|
Rows are delimited by a carriage return-line feed
combination.
|
|
{CR}
|
Rows are delimited by a carriage return.
|
|
{LF}
|
Rows are delimited by a line feed.
|
|
Semicolon {;}
|
Rows are delimited by a semicolon.
|
|
Colon {:}
|
Rows are delimited by a colon.
|
|
Comma {,}
|
Rows are delimited by a comma.
|
|
Tab {t}
|
Rows are delimited by a tab.
|
|
Vertical bar {|}
|
Rows are delimited by a vertical bar.
|
Reset Columns
Remove all but the original columns
by clicking Reset
Columns.
Use the Advanced page of the Flat File
Connection Manager Editor dialog box to set properties that specify how Integration
Services reads and writes data in flat files. You can change the names of
columns in the flat file, and set properties that include data type and
delimiters for each column in the file.
By default, the length
of string columns is 50 characters. You can resize the length of these columns
to prevent truncation of data or excess column width. You can also update other
metadata to enable compatibility with destination columns. For example, you
might change the data type of a column that contains only integer data to a
numeric data type, such as DT_I2. You can make these modifications manually, or
you can click the Select Types button to use the Suggest Column Types dialog box to evaluate sample data and make
some of these changes for you automatically.
Connection
manager name
Provide a unique name for the flat file
connection manager in the workflow. The name provided will be displayed within
SSIS Designer.
Description
Describe the connection manager. As a best
practice, describe the connection manager in terms of its purpose, to make
packages self-documenting and easier to maintain.
Configure
the properties of each column
Select a column in the left pane to view its
properties in the right pane. See the following table for a description of data
type properties. Some of the properties listed are configurable only for some
flat file formats.
|
Property
|
Description
|
|
ColumnType
|
Denotes whether the
column is delimited, fixed width, or ragged right. This property is
read-only. Ragged right files are files in which every column has a fixed
width, except for the last column. It is delimited by the row delimiter.
|
|
OutputColumnWidth
|
Specify a value to
be stored as a count of bytes; for Unicode files, this value corresponds to a
count of characters. In the Data Flow task, this value is used to set the
output column width for the Flat File source. In the object model, the name
of this property is MaximumWidth.
|
|
DataType
|
Select from the list
of available data types. For more information.
|
|
TextQualified
|
Indicate whether
text data is surrounded by text qualifier characters such as quote
characters.
True: Text data in
the flat file is qualified. False: Text data in the flat file is NOT
qualified.
|
|
Name
|
Provide a
descriptive column name. If you do not enter a name, Integration Services
automatically creates a name in the format Column 0, Column 1 and so forth.
|
|
DataScale
|
Specify the scale of
numeric data. Scale refers to the number of decimal places. For more
information,
|
|
ColumnDelimiter
|
Select from the list
of available column delimiters. Choose delimiters that are not likely to
occur in the text. This value is ignored for fixed-width columns.
|
|
DataPrecision
|
Specify the
precision of numeric data. Precision refers to the number of digits. For more
information,
|
|
InputColumnWidth
|
Specify a value to
be stored as a count of bytes; for Unicode files, this will display as a
count of characters. This value is ignored for delimited columns.
Note In the object model, the
name of this property is ColumnWidth.
|
New
Add a new column by clicking New. By default, the New button adds a new column at the end of the
list. The button also has the following options, available in the drop-down
list.
|
Value
|
Description
|
|
Add Column
|
Add a new column at
the end of the list.
|
|
Insert Before
|
Insert a new column
before the selected column.
|
|
Insert After
|
Insert a new column
after the selected column.
|
Delete
Select a column, and then remove it by
clicking Delete.
Suggest
Types
Use the Suggest Column Types dialog box to evaluate sample data in the file
and to obtain suggestions for the data type and length of each column. For more
information.
Use the Preview node of the Flat File
Connection Manager Editor dialog box to view the contents of the source file in a tabular
format.
Connection
manager name
Provide a unique name for the Flat File
connection in the workflow. The name provided will be displayed within SSIS
Designer.
Description
Describe the connection. As a best practice,
describe the connection in terms of its purpose, to make packages
self-documenting and easier to maintain.
Data
rows to skip
Specify how many rows to skip at the beginning
of the flat file.
Refresh
View the effect of changing the number of rows
to skip by clicking Refresh. This button only becomes visible after you have changed other
connection options.
Preview
rows
View sample data in the flat file, divided
into columns and rows according to the options you have selected.
Use the 2.Columns node of the Flat File Source Editor dialog box to map an output column to each
external (source) column
Available
External Columns.
View the list of available external columns in
the data source. You cannot use this table to add or delete columns.
External
Column
View external (source) columns in the order in
which the task will read them. You can change this order by first clearing the
selected columns in the table, and then selecting external columns from the
list in a different order.
Output
Column
Provide a unique name for each output column.
The default is the name of the selected external (source) column; however, you
can choose any unique, descriptive name. The name provided will be displayed
within SSIS Designer.
Use the 4. Error Output page of the Flat File Source Editor dialog box to select error-handling options
and to set properties on error output columns.
Input/output
View the name of the data source.
Column
View the external (source) columns that you
selected on the Connection Manager page of the Flat File Source Editor dialog box.
Error
Specify what should happen when an error
occurs: ignore the failure, redirect the row, or fail the component.
Truncation
Specify what should happen when a truncation
occurs: ignore the failure, redirect the row, or fail the component.
Description
View the description of the error.
Set
this value to selected cells
Specify what should happen to all the selected
cells when an error or truncation occurs: ignore the failure, redirect the row,
or fail the component.
Apply
Apply the error handling option to the
selected cells.
The Advanced Editor dialog box reflects the properties that
can be set programmatically.
Custom
Properties:
FileNameColumnName
(Data Type: String): The name of an output column that
contains the file name. If no name is specified, no output column containing
the file name will be generated.
RetainNulls
(Data Type: Boolien): A value that specifies whether to
retain Null values from the source file as Null values when the data is
processed by the Data Transformation Pipeline engine. The default value of this
property is False.
The following table describes the custom properties of the
output columns of the Flat File source. All properties are read/write.
FastParse (Data Type:
Boolien): A value that indicates
whether the column uses the quicker, but locale-insensitive, fast parsing
routines that DTS provides or the locale-sensitive standard parsing
routines.The default value of this property is False.
Output columns also include the UseBinaryFormat property. You use this property to
implement support for binary data, such as data with the packed decimal format,
in files. By default UseBinaryFormat is set to false.
If you want to use a binary format, set UseBinaryFormat to true and the data type on the output column
to DT_BYTES. When you
do this, the Flat File source skips the data conversion and passes the data to
the output column as is. You can then use a transformation such as the Derived
Column or Data Conversion to cast the DT_BYTES data to a different data type, or you
can write custom script in a Script transformation to interpret the data. You
can also write a custom data flow component to interpret the data.
No comments:
Post a Comment