Script task to write to fixed width file

• apache626

  SSChasing Mays

  Points: 618

  More actions

  May 24, 2010 at 11:33 am

  #1171689

  Ok I wanted to post this so anyone else with this problem can be helped. What I did was take a bit of logic from Phil and Lowell. I took Lowell idea of using the caption and set the caption of every column in the table equal to a string that is the width of the column:

  ds5.Tables(0).Columns(0).Caption = "20"

  ds5.Tables(0).Columns(1).Caption = "10"

  ds5.Tables(0).Columns(2).Caption = "8"

  ds5.Tables(0).Columns(3).Caption = "2"

  ds5.Tables(0).Columns(4).Caption = "1"

  ds5.Tables(0).Columns(5).Caption = "8"

  ds5.Tables(0).Columns(6).Caption = "20"

  ds5.Tables(0).Columns(7).Caption = "20"

  ds5.Tables(0).Columns(8).Caption = "8"

  ds5.Tables(0).Columns(9).Caption = "7"

  ds5.Tables(0).Columns(10).Caption = "8"

  ds5.Tables(0).Columns(11).Caption = "7"

  ds5.Tables(0).Columns(12).Caption = "1"

  ds5.Tables(0).Columns(13).Caption = "1"

  ds5.Tables(0).Columns(14).Caption = "1"

  ds5.Tables(0).Columns(15).Caption = "12"

  I then used Phils code to check if the value was Null:

  For Each dr As DataRow In table.Rows

  Dim array() As Object = dr.ItemArray

  For i As Integer = 0 To (array.Length - 1)

  If Not array(i) Is DBNull.Value Then

  sw.Write(array(i).ToString())

  ElseIf array(i) Is DBNull.Value Then

  array.SetValue(CObj(New String("", GetInteger(table.Columns(i).Caption))), i)

  sw.Write(array(i).ToString())

  End If

  Next

  sw.Write(Environment.NewLine)

  As you can see if the value is NULL i then call Lowell GetInteger() method to convert the caption of the column to an integer. I then create a new string of the right length by passing the caption value.

  array.SetValue(CObj(New String("", GetInteger(table.Columns(i).Caption))), i)

  Thanks everyone for your help!