SSIS Pipe Delimited File


  • I have a flat file that is pipe delmited with varying lengths:




     with cr/lf at end of each row. This file is not loading as expected because the metadata changes. With that i would appreciate input on a fix...

    Wednesday, May 19, 2010 12:01 AM


All replies

  • 1) Import as a single, wide column

    2) Split up using a script transformation

    Details here:

    Cheers, James

    James Beresford @ SSIS / MSBI Consultant in Sydney, Australia ##### BI Monkey SSIS ETL Framework @
    • Marked as answer by lzlzgh Thursday, May 20, 2010 8:46 PM
    Wednesday, May 19, 2010 1:10 AM
  • You should be able to use the Delimited File Source.

    Todd McDermid's Blog Talk to me now on
    Wednesday, May 19, 2010 2:34 AM
  • I went with this example but when I try to load the file I receive the following error:


    [FF_SRC_MyMicros [1]] Error: Data conversion failed. The data conversion for column "Column 23" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    Wednesday, May 19, 2010 11:51 PM
  • Which in all probablilty means your text is being truncated - i.e. you are trying to put your strings into too small a data type.
    James Beresford @ SSIS / MSBI Consultant in Sydney, Australia ##### BI Monkey SSIS ETL Framework @
    Thursday, May 20, 2010 1:48 AM
  • Hello Lzlzgh,

    Here are the steps you can , I have modified Jamie's code

    1--Create flate file source connection to your file , in Format select Ragged Right, so will have each row in one colunm.

    2--Drage Script component. in input select the cloumn 0 that will be coming from your flate file source, in input and output column, Add cloumns to output( column1,column2,column3) by clicking add new column. Set the data type of these columns to DT_STR.

    go to script and paste this script.. you can see the comments what i did, just added a function and then used that function.


    ' 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
     'This funcation is added that we used at the end
     Private Function Tokenise(ByVal input As String, ByVal delimiter As String, ByVal token As Integer) As String
      Dim tokenArray As String()
      tokenArray = input.Split("|".ToCharArray) 'Split the string by the delimiter
      If tokenArray.Length < token Then 'Protect against a request for a token that doesn't exist
       Return ""
       Return tokenArray(token - 1)
      End If
     End Function
     Public Overrides Sub PreExecute()
      ' Add your code here for preprocessing or remove if not needed
     End Sub
     Public Overrides Sub PostExecute()
      ' Add your code here for postprocessing or remove if not needed
      ' You can set read/write variables here, for example:
      ' Me.Variables.MyIntVar = 100
     End Sub
     Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
      ' Add your code here-- These three lines are addess rest of that comes automatically
      Row.Column1 = Tokenise(Row.Column0, "|", 1).ToString
      Row.Column2 = Tokenise(Row.Column0, "|", 2).ToString
      Row.Column3 = Tokenise(Row.Column0, "|", 3).ToString
     End Sub
    End Class

    After that you can connection these three columns to your destination. If you want to do some formatting you can use derived column between script component and your destination.


    Thursday, May 20, 2010 3:11 AM
  • I am getting the following error to the code above.  I debugged and found the error is on the following line

    tokenArray = input.Split("|".ToCharArray)

    Object reference not set to an instance of an object.

    at SC_492050cf326142b0b8d540b864bc103e.vbproj.ScriptMain.Tokenise(String input, Int32 token)
    at SC_492050cf326142b0b8d540b864bc103e.vbproj.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
    at SC_492050cf326142b0b8d540b864bc103e.vbproj.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
    at SC_492050cf326142b0b8d540b864bc103e.vbproj.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
    at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

    • Edited by nshirode Tuesday, July 03, 2012 6:37 AM
    Tuesday, July 03, 2012 6:35 AM