Cause:
SSIS fails to read the file and displays the below warning due to the column delimiter ? ("c" with cedilla) and not due to the line delimiter {LF} (Line Feed).
[Read flat file [1]] Warning: The end of the data file was reached while
reading header rows. Make sure the header row delimiter and the number of
header rows to skip are correct.
Here is a sample SSIS package that shows how to resolve the issue using Script Component and at the end there is another example that simulates your issue.
Resolution:
Below sample package is written in SSIS 2008 R2. It reads a flat file with row delimiter {LF} as a single column value; then splits the data using Script Component to insert the information into a table in SQL Server 2008 R2 database.
Use Notepad++ to create a simple flat file with few rows. The below sample file has Product Id and List Price information on each row separated by ? as column delimiter and each row ends with {LF} delimiter.
On the Notepad++, click Encoding and then click Encoding in UTF-8 to save the flat file in UTF-8 encoding.
The sample will use an SQL Server 2008 R2 database named Sora. Create a new table named dbo.ProductListPrice using the below given script. SSIS will insert the flat file data into this table.
USE Sora;
GO
CREATE TABLE dbo.ProductListPrice
(
ProductId nvarchar(30) NOT NULL
, ListPrice numeric(12,2) NOT NULL
);
GO
Create an SSIS package using Business Intelligence Development Studio (BIDS) 2008 R2. Name the package as SO_6268205.dtsx. Create a data source named Sora.ds to connect to the database Sora in SQL Server 2008 R2.
Right-click anywhere inside the package and then click Variables to view the variables pane. Create a new variable named ColumnDelimiter of data type String in the package scope SO_6268205 and set the variable with the value ?
Right-click on the Connection Managers and click New Flat File Connection... to create a connection to read the flat file.
On the General page of the Flat File Connection Manager Editor, perform the following actions:
- Set Connection manager name to
ProductListPrice
- Set Description to
Flat file connection manager to read product list price information.
- Select the flat file path. I have the file in the path
C:SivaStackOverflowFiles6268205ProductListPrice.txt
- Select
{LF} from Header Row Delimiter
- Check
Column names in the first data row
- Click
Columns page
On the Columns page of the Flat File Connection Manager Editor, verify that the Column delimiter is blank and disabled. Click Advanced page.
On the Advanced page of the Flat File Connection Manager Editor, perform the following actions.
- Set the Name to
LineData
- Verify that the Column delimiter is set to
{LF}
- Set the DataType to
Unicode string [DT_WSTR]
- Set the OutputColumnWidth to
255
- Click the
Preview page.
On the Preview page of the Flat File Connection Manager Editor, verify that the displayed data looks correct and click OK.
You will see the data source Sora and the flat file connection manager ProductListPrice on the Connection Managers tab at the bottom of the package.
Drag and drop Data Flow Task onto the Control Flow tab of the package and name it as File to database - Without Cedilla delimiter
Double-click the Data Flow Task to switch the view to the Data Flow tab on the package. Drag and drop a Flat File Source on the Data Flow tab. Double-click the Flat File Source to open Flat File Source Editor.
On the Connection Manager page of the Flat File Source Editor, select the Flat File Connection Manager ProductListPrice and click Columns page.
On the Columns page of the Flat File Source Editor, check the column LineData and click OK.
Drag and drop a Script Component onto the Data Flow tab below the Flat File Source, select Transformation and click OK. Connect the green arrow from Flat File Source to Script Component. Double-click Script Component to open Script Transformation Editor.
Click Input Columns on Script Transformation Editor and select LineData column. Click Inputs and Outputs page.
On the Inputs and Outputs page of the Script Transformation Editor, perform the following actions.
- Change the inputs name to FlatFileInput
- Change the outputs name to
SplitDataOutput
- Select Output Columns and click
Add Column. Repeat this again to add another column.
- Name the first column
ProductId
- Set the DataType of column ProductId to
Unicode string [DT_WSTR]
- Set the Length to
30
On the Inputs and Outputs page of the Script Transformation Editor, perform the following actions.
- Name the second column
ListPrice
- Set the DataType of column ListPrice to
numeric [DT_NUMERIC]
- Set the Precision to
12
- Set the Scale to
2
- Click Script page to modify the script
On the Script page of the Script Transformation Editor, perform the following actions.
- Click the ellipsis button against ReadOnlyVariables and select the variable
User::ColumnDelimiter
- Click
Edit Script...
Paste the below C# in the Script Editor. The script performs the following tasks.
- Using the column delimiter value
? defined in the variable User::ColumnDelimiter, the method FlatFileInput_ProcessInputRow splits the incoming value and assigns it to the two output columns defined in the Script Component transformation.
Script component code in C#
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
}
public override void PostExecute()
{
base.PostExecute();
}
public override void FlatFileInput_ProcessInputRow(FlatFileInputBuffer Row)
{
const int COL_PRODUCT = 0;
const int COL_PRICE = 1;
char delimiter = Convert.ToChar(this.Variables.ColumnDelimiter);
string[] lineData = Row.LineData.ToString().Split(delimiter);
Row.ProductId = String.IsNullOrEmpty(lineData[COL_PRODUCT])
? String.Empty
: lineData[COL_PRODUCT];
Row.ListPrice = String.IsNullOrEmpty(lineData[COL_PRICE])
? 0
: Convert.ToDecimal(lineData[COL_PRICE]);
}
}
Drag and drop OLE DB Destination onto the Data Flow tab. Connect the green arrow from Script Component to OLE DB Destination. Double-click OLE DB Destination to open OLE DB Destination Editor.
On the Connection Manager page of the OLE DB Destination Editor, perform the following actions.
- Select
Sora from OLE DB Connection Manager
- Select
Table or view - fast load from Data access mode
- Select
[dbo].[ProductListPrice] from Name of the table or the view
- Click Mappings page
Click Mappings page on the OLE DB Destination Editor would automatically map the columns if the input and output column names are same. Click OK.
<img src="https://i.stack.imgur.com/s2wUC.png" alt="OL
