2

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
  • 36,233
  • 13
  • 65
  • 124
Amanvir Mundra
  • 420
  • 1
  • 6
  • 20

1 Answers1

0

You have to follow these steps:

  1. In The DataFlow Taskadd s script component between the OLEDB Source and Flat File Destination

  2. In the Script Component Mark Description Column as Input, Add OutDescription Column as Output Column of type DT_WSTR and length 200

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

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • I am a bit new to this, but this code looks like it is going to only fetch a substring of length 200, which is the same as truncation. – Amanvir Mundra Apr 11 '17 at 10:44
  • oooh. i missunderstood the question. i will fixed it – Hadi Apr 11 '17 at 10:55
  • Hadi, I tried your code. It adds line breaks to the string but the new line for starts from 0 index and not 51. I want the description column to be wrapped in it's own space (e.g. 51-250). But your answer gave me a direction and a way to approach this problem. Updated the question with a sample layout. – Amanvir Mundra Apr 12 '17 at 08:19
  • @AmanvirSinghMundra Just use `String.Join(vbNewLine & "".PadLeft(50,CChar(" ")), longlist)` i updated my answer – Hadi Apr 12 '17 at 10:02
  • Padding doesn't seem to work. Maybe I am missing something in the configuration. I'll try to get it working and then mark it as an answer. Thanks a lot for the help :) – Amanvir Mundra Apr 12 '17 at 10:12