Flat File Source in SSIS 2008R2 with Example

Friends,

A Flat File source is a data flow component that uses metadata defined by a Flat File connection manager to specify the format and structure of the data to be extracted from the flat file by a transform process. The Flat File source can be configured to extract data from a single flat file by using the file format definition provided by the Flat File connection manager. PFB the steps in configuring Flat file source:

For configuring Flat file source, you should have a Data Flow Task. You can Take Data Flow task in Control Flow tab and then double click on it will take you to the Data flow tab .

Here you can drag Data Flow from Left hand Side OR you can directly go to the Data Flow Tab and on that page click on the message shown. It will automatically add a Data Flow Task in the Control Flow Tab and Enable the all components of Data Flow Tab.

Now Drag a Flat File source From Left hand side  Data Flow Source into the Data flow Tab.

Double click on the Flat file Source or Right-Click on it and select Edit. It will Open the Flat File Source Editor.

Now Select New to add a new flat file source Connection.

It will again open a new Wizard Called Flat File Connection Manager. Here you can write connection manager name as in my case I have written Flt File Connection and you can also write some description if you want.

As you see the entire component all in disable state

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.
Text qualifier: Specify the text qualifier to use. For example, you can specify that text fields are enclosed in quotation marks.
Header row delimiter: Select from the list of delimiters for header rows, or enter the delimiter text.
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

So first you have to browse your flat file source by clicking on the Browse, In my case my flat file source is a text file and it is on Desktop. After Browsing you will see everything got enabled.

My source is Beauty.txt (Names might look funny but remember this is just an example)

Here my file name is Beauty.txt and Accordingly I set all the properties. Like I don’t need any Text qualifier and don’t want to skip any rows and want first row as  a Column Names. Now go the next section called Columns in Left hand Side.

As you can see my first row becomes column name here and the data get stored in respective column. Now go to the Advanced Section On the Left Hand Side.

In this you can Configure the existing column or you can Add Column ,Insert Before and Insert After. By selecting Suggest Types… you can suggest data type for the selected Column. You can find these option in suggest types…

Number of rows: Type or select the number of rows in the sample that the algorithm uses.
Suggest the smallest integer data type: Clear this check box to skip the assessment. If selected, determines the smallest possible integer data type for columns that contain integral numeric data.
Suggest the smallest real data type: Clear this check box to skip the assessment. If selected, determines whether columns that contain real numeric data can use the smaller real data type, DT_R4.
Identify Boolean columns using the following values: Type the two values that you want to use as the Boolean values true and false. The values must be separated by a comma, and the first value represents True.
Pad string columns: Select this check box to enable string padding.
Percent padding: Type or select the percentage of the column lengths by which to increase the length of columns for character data types. The percentage must be an integer.

Now select Preview Tab on the left hand side where you can see the preview of the file that getting upload.

Now select ok and you will be again on the File system source Editor.

You can check the the box Retain null values from the source ,if you want to retain the null values. Now Go to the Column Tab in the Left hand side where you can see the column mapping. Where you can change the output column name if you want in Output Column.

Now select Error Output tab here you can handle your Errors result. As you see have Column name , Error, Truncation and Description.

You can change it into three options. Ignore Failure- If you want to ignore failure and  want to move forward, Redirect Rows – if you want to redirect he rows to another output. Fail Component- If you want to fail the component on failure of error and Truncation.

And press ok you are good to go to the next section either transformation or direct to the destination.

Thanks Swati Srivatsava for the clean and complete document.

Regards,
Roopesh Babu V