How To Load Flat File In SSIS? RRS feed

  • Question

  • Hi All,


    I want load the flat file with trailer records first one is program name ,second record count and final one is date value.In ssis how to load this data?









    • Edited by cmk1 Saturday, February 5, 2011 1:40 AM
    Thursday, January 27, 2011 4:11 PM


All replies

  • Header row is skippable in the flatfile connection manager. Footer isn't.

    If you can distinguishing the footer rows from the others, you can use a conditional spit the get rid of it.

    If not... You could use a row counter component to count the rows and then use a script component to skip the last rows.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Thursday, January 27, 2011 4:23 PM
  • I don't want skip the last row i want load those values in table..header i will skip it.





    Thursday, January 27, 2011 4:25 PM
  • Hi CMK,

    I think the most elegant way is to use this component: http://www.sqlis.com/post/Row-Number-Transformation.aspx.

    It lets you add a pretend column which is a row number, by knowing the row number you can determine if this is the footer or header and act accordingly. I mean you can then use the conditional split.

    I have not tried this approach myself, but if you would, you could share the solution here I am thinking.

    Arthur My Blog
    By: TwitterButtons.com
    Thursday, January 27, 2011 4:27 PM
  • Hi All,


    Please provide me any refernece of this kind of task.Its very urgent for me.





    Friday, January 28, 2011 2:50 PM
  • Hi CMK,

    also found this http://support.microsoft.com/kb/908460 it shows how to use a Script Component to archive something similar, I think it is even easier this way in your scenario.

    Arthur My Blog
    By: TwitterButtons.com
    Friday, January 28, 2011 2:59 PM
  • Do you want to import Trailer values also?

    Asynchronous Script Component is very powerful, but I think you can do it without it.

    you can use CONFIGURE ERROR OUTPUT on flat file source , and trailer rows will redirect to error output .

    if you want to skip trailer , just set error output as Ignore.

    Friday, January 28, 2011 4:20 PM
  • Yes I want import the trailer records also which one i bold teh flat file format values.I don't want skip the trailer records.






    Friday, January 28, 2011 5:29 PM
  • is the Trailer record has a default structure ?

    I mean how you separate program name and record count and date from it? is it character based?

    Friday, January 28, 2011 5:33 PM
  • Thanks Reza Raad yes they have default structure.i didn't get you what does mean it character based?




    Friday, January 28, 2011 5:41 PM
  • OK,
    As I said before, you should do this steps:
    in flat file source editor, in general tab, set skip 1 row ( for header row )
    then in second tab, set column delimiter as vertical line ( | ) and click on refresh button, igonre any error which you recieved .
    in ERROR OUTPUT tab, set redirect error ,

    then add a derived column transform to data flow task
    and connect red arrow ( error output ) from flat file source to this derived column transform
    in the Derived column transform add these columns with expressions:

    column name              expression
    ProgramName               SUBSTRING([FirstColumn],2,7)
    RecordCount               SUBSTRING([FirstColumn],10,10)
    Date                      SUBSTRING([FirstColumn],20,8)

    Note that you should use your first column name instead of FirstColumn in above expressions

    then connect green arrow from derived column transformation to any destination you need
    in this data path you have these three column values.

    Friday, January 28, 2011 6:05 PM
  • Thanks Reza Raad..

    Can you elaborate below line.

     connect green arrow ( error output ) from flat file source to this derived column transform




    Friday, January 28, 2011 7:24 PM
  • Thanks Reza Raad..

    Can you elaborate below line.

     connect green arrow ( error output ) from flat file source to this derived column transform




    Sorry, I meant Red arrow , I edited my previous post .

    when you click on flat file source, a red arrow will appear drag and drop it on derived column.

    Friday, January 28, 2011 7:29 PM
  • insted of SUBSTRING([FirstColumn],2,7) this one i need to use below one correct or not

    SUBSTRING([Flat File Source Error Output Column],2,7)





    Friday, January 28, 2011 7:53 PM
  • yes, but note that in error output configuration you should set first column as redirect row

    Friday, January 28, 2011 8:10 PM
  • If i use like below statement the expression tab shows error.In error output first column redirect row option.but still i am getting the error.In dervied transformation which data type i need to select.

    SUBSTRING([Flat File Source Error Output Column],2,7)





    Friday, January 28, 2011 8:50 PM
  • where you got error? what is error message?

    Friday, January 28, 2011 8:52 PM
  • The function SUBSTRING doesn't support the dt_text datatype....like that


    Friday, January 28, 2011 8:54 PM
  • The function SUBSTRING doesn't support the dt_text datatype....like that



    double click on flat file connection manager, in the ADVANCED tab,

    find every column which is DT_TEXT data type and change it to DT_STR

    Friday, January 28, 2011 8:58 PM
  • All columns have only DT_STR.for output column size is 50 for all columns

    Friday, January 28, 2011 9:12 PM
  • Oh, I did a mistake on this case.

    you can't use error output for trailer row . I was wrong.

    the trailer row hasn't same structure as other rows so will be removed by flat file connection manager and you can't redirect it anywhere.

    So there are other options:

    1- Asynchronous script component as others said earlier

    2- with help of an ordinal script task.


    please let me know where you want to import data which comes from trailer? probably you don't want to import it to same table destination which you import base rows. am I correct?

    Friday, January 28, 2011 9:35 PM
  • Thanks Reza Raad...we need to load the diffent table those trailer records please suggest me how to start that process.





    Friday, January 28, 2011 10:14 PM
  • OK, I finaly go with Script Component as Source .

    This is whole solution:

    create new data flow task,
    add a script component transform and set it as Source
    then in second tab ( inputs and outputs ), click on output0, and rename it as OutputMainRows
    then add 8 columns of type DT_STR under OutputMainRows, name them Column 0, Column 1, Column 2,Column 3,...,Column 7

    then add another output, name it as OutputTrailerRow
    under OutputTrailerRow, add 3 columns:
    column name        datatype
    ProgramName        DT_STR
    RecordCount        DT_I4
    Date               DT_DATE

    then go to script tab,
    set language as visual basic
    then click on edit script,

    this is whole code in the script ( you can copy and paste it instead existing code there if you use SSIS 2008 ):

    ' Microsoft SQL Server Integration Services Script Component
    ' Write scripts using Microsoft Visual Basic 2008.
    ' ScriptMain is the entry point class of the script.
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
    <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
    <CLSCompliant(False)> _
    Public Class ScriptMain
      Inherits UserComponent
      Dim sr As System.IO.StreamReader
      Public Overrides Sub PreExecute()
        sr = New System.IO.StreamReader("E:\test\flatfilewithHandT.txt")
      End Sub
      Public Overrides Sub PostExecute()
      End Sub
      Public Overrides Sub CreateNewOutputRows()
        Dim lineIndex As Integer = 0
        While (Not sr.EndOfStream)
          Dim line As String = sr.ReadLine()
          'MessageBox.Show("inside while")
          If (lineIndex <> 0) Then 'remove header row
            'MessageBox.Show("inside if")
            Dim columnArray As String() = line.Split(Convert.ToChar("|"))
            If (columnArray.Length > 1) Then
              'main rows
              OutputMainRowsBuffer.Column0 = columnArray(0)
              OutputMainRowsBuffer.Column1 = columnArray(1)
              OutputMainRowsBuffer.Column2 = columnArray(2)
              OutputMainRowsBuffer.Column3 = columnArray(3)
              OutputMainRowsBuffer.Column4 = columnArray(4)
              OutputMainRowsBuffer.Column5 = columnArray(5)
              OutputMainRowsBuffer.Column6 = columnArray(6)
              OutputMainRowsBuffer.Column7 = columnArray(7)
              'trailer row
              OutputTrailerRowBuffer.ProgramName = line.Substring(1, 7)
              OutputTrailerRowBuffer.RecordCount = line.Substring(9, 10)
              OutputTrailerRowBuffer.Date = DateTime.ParseExact(line.Substring(19, 8), "yyyyMMdd", New System.Globalization.CultureInfo("en-US"))
            End If
          End If
          lineIndex = lineIndex + 1
        End While
      End Sub
    End Class

    save and close it.

    now the script component has two output,
    you can connect OutputMainRows to your destination table for main rows
    and connectio OuputTrailerRow to the destination where you want to insert trailer values.

    That's all.

    Saturday, January 29, 2011 5:39 AM
    • Proposed as answer by Reza RaadMVP Tuesday, February 1, 2011 12:14 PM
    • Marked as answer by cmk1 Thursday, February 3, 2011 10:29 PM
    Saturday, January 29, 2011 6:27 PM
  • Thank you so much Reza Raad...I will try now.





    Saturday, January 29, 2011 9:34 PM
  • Thanks Reza...Raad...Thank you so much...





    Thursday, February 3, 2011 10:30 PM