SSIS Pipe Delimited File

Question

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

123|123|123|

123|123

 

 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...

Answer 1

1) Import as a single, wide column

2) Split up using a script transformation

Details here: http://www.bimonkey.com/2009/06/flat-file-source-error-the-column-delimiter-for-column-columnname-was-not-found/

Cheers, James

---

James Beresford @ http://www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia ##### BI Monkey SSIS ETL Framework @ http://ssisetlframework.codeplex.com/

Answer 2

You should be able to use the Delimited File Source.

---

Talk to me now on

Answer 3

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.".

Answer 4

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 @ http://www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia ##### BI Monkey SSIS ETL Framework @ http://ssisetlframework.codeplex.com/

Answer 5

Hello Lzlzgh,

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

http://consultingblogs.emc.com/jamiethomson/archive/2006/07/14/4226.aspx

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 ""
  Else
   Return tokenArray(token - 1)
  End If

 End Function

 Public Overrides Sub PreExecute()
  MyBase.PreExecute()
  '
  ' Add your code here for preprocessing or remove if not needed
  '
 End Sub

 Public Overrides Sub PostExecute()
  MyBase.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.

Thanks

Answer 6

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)