I have a flat file with row delimiter <cr> & <lf> and column delimiter <tab>.
This file has certain rows less columns than other rows. The rows which have
less columns have early termination with <cr> & <lf>. When I parsed this
file, I found SQL is not inserting missing column delmiters and show the
data, instead the data is messed up because of less column delimiters in
certain rows. Whereas this works perfectly fine with sql server 2000 dts. Is
this a bug in SSIS or this is the new behavior? Does SSIS provide a way to
resolve this scenario?

Thanks

I experienced the same problem...
DTS imports NULL values for the missing columns, whereas SSIS sees the CRLF
chars just as the beginning of the next (missing) field and totally screws
the columns.

I think there is no solution is SSIS.

My workaround involves "fixing" the flatfile before importing it, giving
each row all the needed columns. I implemented this in a script task. See
code:

Public Sub Main()
'
' Add your code here
'
Dim varFieldCount As Integer =
Integer.Parse(Dts.Variables("constInputFieldCount").Value.ToString)
Dim varFileName As String =
Dts.Variables("varFileName").Value.ToString
Dim varFileNameFIXED As String =
Dts.Variables("varFileNameFIXED").Value.ToString

Dim inputStream As New System.IO.StreamReader(varFileName)
Dim outputStream As New System.IO.StreamWriter(varFileNameFIXED)
Dim origLine As String
' separator
Dim spl(0) As String
spl(0) = """;"""

Dim tempFieldCount As Integer

Do
origLine = inputStream.ReadLine
If Not origLine Is Nothing Then
tempFieldCount = origLine.Split(spl,
StringSplitOptions.None).Length
For iCounter As Integer = 1 To (varFieldCount -
tempFieldCount)
origLine &= ";"""""
Next
outputStream.WriteLine(origLine)
End If
Loop Until origLine Is Nothing
inputStream.Close()
outputStream.Close()

' varFileTimestamp
Dts.Variables("varFileTimestamp").Value =
System.IO.File.GetLastWriteTime(varFileName)

Dts.TaskResult = Dts.Results.Success
End Sub

Notice that my flatfile is ;-separated, and is textqualified (") on all
fields. Having no textqualifiers at all is also simple. When it's not the
case on all fields, the logic could get a little trickier... :-)
In this case, I just count occurences of ";"
For every column that is missing, I added ;""

Hope this helps
Nico Verheire




[quoted text, click to view]