SSIS Flat file destination export multi-line column

Question

I have a SQL table as Source and I want to export it's contents to a Flat file using SSIS.

Simplified example:

Source: Notes table (CreatedBy, Description, CreatedOn)
The Description field is nText.

Destination: Flat file - Fixed length 
CreatedBy(0-50)
Description (51-250)
CreatedOn (251-270)

The problem is that the description can be really long and we don't want it to be truncated after 200 chars. It should wrap to the next line.

I cannot find a way to do this using SSIS.

Really appreciate your help.

Update

I am looking to achieve a layout like below:

CreatedBy | Description                          | CreatedOn|
John        Really long description..............  2/2/2017
            more text..
John2       This is the second line                2/3/2017

Hadi answer allows to break a long string into parts but still doesn't solve the layout problem.

Hadi Hadi · Accepted Answer · 2017-04-11T10:27:41

You have to follow these steps:

In The DataFlow Taskadd s script component between the OLEDB Source and Flat File Destination
In the Script Component Mark Description Column as Input, Add OutDescription Column as Output Column of type DT_WSTR and length 200

In the Script window write the following code (Inside Input0_RowProcessing Method:

If Not Row.Description_IsNull AndAlso 
   Not String.IsNullOrEmpty(Row.Description.Trim) Then

       If Row.Description.Trim.Length > 200 Then

            Dim LongString As String = Row.Description.Trim

            Dim longlist As New System.Collections.Generic.List(Of String)

            Dim idx As Integer = 0

            While idx <= LongString.Length

                If LongString.Length < idx + 200 Then
                    longlist.Add(LongString.Substring(idx))
                Else
                    longlist.Add(LongString.Substring(idx, 200))
                End If



                idx += 200

            End While

            Row.OutDescription = String.Join(vbNewLine & "".PadLeft(50,CChar(" ")), longlist)

       Else

           Row.OutDescription = Row.Description

        End If

Else


    Row.OutDescription_IsNull = True


End IF

In Flat File Destination map the OutDesciption column instead of Description column

SSIS Flat file destination export multi-line column

1 Answers