would this be simpler with powershell?

• drew.georgopulos

  SSCarpal Tunnel

  Points: 4931

  More actions

  November 9, 2011 at 12:17 pm

  #262524

  I have many packages that duplicate the following file system code, and wonder whether the same could be more economically expressed with powershell one liner instead of a tome's worth of vba and if so how? iow, can this routine be replaced and if it can, what are the magic words?

  thanks a lot

  drew

  Imports System

  Imports System.Data

  Imports System.Data.OleDb

  Imports System.IO

  Imports System.Text

  Imports System.Math

  Imports Microsoft.SqlServer.Dts.Runtime

  Public Class ScriptMain

  Public Sub Main()

  ' Input File

  Dim strFile As String = Dts.Variables("strSrcFileName").Value.ToString

  ' Output File

  Dim objWriter As New StreamWriter(Dts.Variables("strSrcFile").Value.ToString, False)

  ' Input Record Length

  Dim strRecLength As String = Dts.Variables("intRecLen").Value.ToString

  Dim intRecLength As Integer = CInt(strRecLength)

  Dim objFile As New FileInfo(strFile)

  ' Add logic here to read strFile ("strSrcFileName") and

  ' append spaces to the record and write output to objWriter (Dts.Variables("strSrcFile")

  Dim srFileReader As System.IO.StreamReader

  Dim sInputLine As String

  srFileReader = System.IO.File.OpenText(strFile)

  sInputLine = srFileReader.ReadLine()

  Do Until sInputLine Is Nothing

  objWriter.WriteLine(sInputLine.PadRight(intRecLength, " "c).ToString)

  sInputLine = srFileReader.ReadLine()

  Loop

  srFileReader.Close()

  objWriter.Close()

  Dts.TaskResult = Dts.Results.Success

  End Sub

  End Class

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  November 15, 2011 at 1:21 pm

  #1408148

  ##################################################################################

  $recLength = 100 # Dts.Variables("intRecLen")

  $inFile = "C:\inputfile.txt" # Dts.Variables("strSrcFileName")

  $outFile = "C:\outputfile.txt" # Dts.Variables("strSrcFile")

  ##################################################################################

  # delete existing file if it exists

  Remove-Item -Path $outFile -ErrorAction SilentlyContinue

  # iterate over input file and write padded lines to output file

  foreach ($line in Get-Content $inFile)

  {

  "{0,-$recLength}" -f $line | Out-File -FilePath $outFile -Encoding Unicode -Append

  }

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• drew.georgopulos

  SSCarpal Tunnel

  Points: 4931

  More actions

  November 15, 2011 at 2:41 pm

  #1408187

  breath taking!

  thanks a ton

  drew