Home » SQL Server

How capture bad records from "Flat File Source" in SSIS

The flat file is having 10 columns so used 9 delimitor. Few records in the Flat file is having more than 10 columns. I need to redirect in to some error table and rest valid records needs to be loaded in target. Pls help.


4 Answers Found


Answer 1

This is a difficult scenario. You may be able to do it by dragging the red arrow from the Source Adapter on your data flow and sending to some task(s) that will handle the errors. You must also set the "Error Configuration" to Re-direct rows.

However, the hard part is that this MAY not work. You may need to go with a Script Transform set as a Source. In there, you would read every row via the script, and decide what to do with it. The advantage here is that it give you much finer control.


Answer 2

This could go a few ways depending on the flat  file itself and the format.  Agree that a script source  may be the best choice and then bring the rows in and either RegEx them or validate as you go.  That and most processing for these types of things will be slow (row-by-row processing)

Example: If the extra column in the data rows is something simply another catch of a comma, you could bring in the flat file  source as normal.  using a script tranformation you could then process the input row with a RegEx match on the alst column.  Mostly, the last column will contain the added rows as it will go off the meta that is generated and only see your number of header columns  it knows of.  Anything after that will be placed into that last column.  So in that last column you could do a .Match and count the instances of the commas.  If you find more than one, you can act upon that.  Sending the output to an output buffer so it can be later processed or reviewed.

Really varies on how you would do this depending on the flat file format.  Processing the file before you get into the DF is also common if the flat file is truly unstable or you cannot trust the contents while keeping the package from doing the job.


Answer 3

Thanks for your quick response. It will be a tough for me for writing script as I am new to SSIS.

Can you share you thought like If I use conditional split to check the deliminator in each record(if any) i.e. Bad record and redirect  to a ERROR table.

Many Thanks,



Answer 4

Well here's the thing, your Header rows probably have VERY different metadata than your detail rows, so you would need to have just ONE column in the flat  file connection manager, then AFTER you split the data into Header/Detail, you would then parse out all the columns  using conditional splits.

Why not jump in with two feet and learn how to use the Script task? We'll help  (there's plenty of it in this forum).

Try this:

Add a file  Connection Manager to your package (NOT a FLAT file, just a regular file) Add a Data Flow, and in that add a Script Transform, set it to Source Open it and on the Inputs and Outputs tab, add three outputs, one each for Header, Detail, and Footer paths, and add all the columns associated with each. On the Connection Managers tab, add the File Connection Manager from Step 1. On the Script Tab, click Edit Script Under the Public Overrides Sub CreateNewOutputRows() function: take a look at the following code snippets for examples of what to do:


Dim cnInvoiceFile As IDTSConnectionManager100 = Me.Connections.InvoiceFile


Dim fn AsString = CType(cnInvoiceFile.AcquireConnection(Nothing), String)


Dim readS AsNew StreamReader(fn)


Dim dataLine AsString





      If readS IsNotNothingThen


         WhileNot readS.EndOfStream

            dataLine = readS.ReadLine()

With OutputHeaderBuffer


.MyField1 = <something>

.MyField1 = <something else>







Catch ex As Exception



You will need to put in your own logic to test if the row is a Header, Detail, or Footer and write to the appropriate OutputBuffer accordingly.



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure