Recently I worked with another consultant on trying to import data from a flat file into a SQL Server 2005 database. There were issues loading the file using the SSIS Import and Export Wizard, however, because not all of the rows in the file had the same number of columns. This wouldn't have been an issue using the SQL Server 2000 DTS Import but this functionality has been modified in the new version of SQL Server. Below is the best answer I could find as to why:
The simple answer to the question "Why has this been changed in SSIS?" can be summed up in one word. Performance. In order that DTS could cope with files like this it would parse each row twice; once to find the row delimiter and a second time to find all the columns in the row. In SSIS that's no longer necessary; because the file is expected to be in a certain format SSIS doesn't worry that the row delimiter might be in the wrong place hence each row is only parsed once. Obviously then doing it the SSIS way results in a lot less work and thus the file can be loaded a lot quicker.
So let's assume we have a flat file that contains the following rows:
0,1,2
1,2,3,4,5,6,7
1,2,3,4,5
a,b,c,d,e,f,g
Attempting to load the file through SSIS Import Wizard and previewing the columns:
Not quite what we were expecting. If we were to try the same in SQL 2000, we'd see:
So to get this file loaded properly in SSIS, we need to develop our own package with a Script Component. Below is the layout for the Data Flow:
Here's the flat file connection manager. Note that I'm not using a columnn delimiter - the approach here is to load all the data for each row into its own column and break it apart in the Script Component.
Within the properties for the Script Component, we have one input column (Column0) and create one output column for each possible column in the file.
The code within the Scripting Component (which I shamelessly ripped from one of the blogs mentioned at the end of this post) is where the input column is broken up into the separate values and assign the output column values.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.Col1 = Tokenise(Row.Column0, ",", 1)
Row.Col2 = Tokenise(Row.Column0, ",", 2)
Row.Col3 = Tokenise(Row.Column0, ",", 3)
Row.Col4 = Tokenise(Row.Column0, ",", 4)
Row.Col5 = Tokenise(Row.Column0, ",", 5)
Row.Col6 = Tokenise(Row.Column0, ",", 6)
Row.Col7 = Tokenise(Row.Column0, ",", 7)
End Sub
'Private function that parses out the columns of the whole row
Private Function Tokenise(ByVal input As String, ByVal delimiter As String, ByVal token As Integer) As String
Dim tokenArray As String()
tokenArray = input.Split(delimiter.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
End Class
I've created the table beforehand that will contain the data within SQL and set that as my destination.
So all that remains is to execute the data flow task. After it has successfully run, we can check the destination table:
Source code for this example can be found here.