Importing Poorly Formatted Text Files using SSIS

Case 3: Variable Columns

In the previous cases we have the same number of columns throughout. What if the number of columns also varied? For example, consider the following text file:

P1,P0001,Product 1
P2,P0002,Product 2,01/10/2007
P3,P0003,Product 3
P4,P0004,Product 4
P5,P0005,Product 5
P6,P0006,Product 6,21/06/2007
P7,P0007,Product 7
P8,P0008,Product 8,15/10/2008
P9,P0009,Product 9
P10,P00010,Product 10

You can see that the date is available for only few columns. You will not be able to use traditional text file handling with SSIS for above case.

Again, the difference with the previous two cases is the script component. To start with you will need to add new output column named Date. Then we need to add some .NET code for the script component:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim strRow As String
Dim strColSeperator As String
Dim rowValues As String()

rowValues = Row.Line.Split(CChar(","))
Row.ShCode = rowValues.GetValue(0).ToString()
Row.Code = rowValues.GetValue(1).ToString()
Row.Description = rowValues.GetValue(2).ToString()

If rowValues.GetUpperBound(0) = 3 Then
Row.Date = rowValues.GetValue(3).ToString()
End If 

End Sub

End Class 

In this case aftr splitting, the script checks the number of elements the array has. If it 3 then you have a date column in the data row. Again we will check the data viewer output:

Sample

For your reference I have added sample SSIS package for you along with the text files I have used. Download here

You may need to change all the connection manager file paths to suit to your file path.

 Conclusion
I am sure you have come across with many situations with text files. What are those situations and what were alternatives you implemented? I would love to hear your feedback at dineshasanka@gmail.com