how to encrypt excel file into flat file using vb.net in pgp format in ssis

• jagadeeshb

  Right there with Babe

  Points: 782

  More actions

  December 28, 2009 at 3:43 am

  #136965

  Hi,

  I want to encrypt excel file data into .txt file in pgp format using vb.net script. Here .txt file is encrypted into .txt format whereas .xls file is not encrypting into .txt format and no error is coming but not encrypting the data.

  This is my vb.net code

  Public Sub Main()

  '

  ' Add your code here

  '

  Dim vGPGExecutable As Variables

  Dts.VariableDispenser.LockOneForRead("GPGExecutable", vGPGExecutable)

  Dim gpgExecutable As String

  gpgExecutable = vGPGExecutable("GPGExecutable").Value.ToString

  vGPGExecutable.Unlock()

  Dim vHomeDir As Variables

  Dts.VariableDispenser.LockOneForRead("HomeDirectory", vHomeDir)

  Dim HomeDir As String

  HomeDir = vHomeDir("HomeDirectory").Value.ToString

  vHomeDir.Unlock()

  Dim vrecipient As Variables

  Dts.VariableDispenser.LockOneForRead("recipient", vrecipient)

  Dim recipient As String = vrecipient("recipient").Value.ToString

  vrecipient.Unlock()

  'Dts.Connections("ArchivingFilePath").ConnectionString = Dts.Connections("ArchivingPath").ConnectionString + "utc_" + DateTime.Now.ToString("yyyyMMdd") + ".xls"

  'Dim outputFile As String = Dts.Connections("ArchivingPath").ConnectionString + "utc_" + DateTime.Now.ToString("yyyyMMdd") + ".txt"

  Dim inputfile As String = Dts.Connections("ArchivingFilePath").ConnectionString

  Dim outputFile As String = "C:\Encrypt.txt"

  'Dim inputfile As String = "C:\h1.xls"

  If System.IO.File.Exists(inputfile) = True Then

  Dim gpgOptions As String

  Dim optionsBuilder As StringBuilder = New StringBuilder()

  optionsBuilder.Append("--homedir ")

  optionsBuilder.Append(HomeDir)

  optionsBuilder.Append(" --yes --batch --armor --recipient ")

  optionsBuilder.Append(recipient)

  optionsBuilder.Append(" --no-verbose --output ")

  optionsBuilder.Append(outputFile)

  optionsBuilder.Append(" --always-trust")

  optionsBuilder.Append(" --encrypt ")

  optionsBuilder.Append(inputfile)

  gpgOptions = optionsBuilder.ToString()

  Dim process As Process = New Process()

  Dim streamWriter As StreamWriter

  Dim streamReader As StreamReader

  Dim err As StreamReader

  Dim prStartInfo As ProcessStartInfo = New ProcessStartInfo(gpgExecutable, gpgOptions)

  prStartInfo.UseShellExecute = False

  prStartInfo.RedirectStandardInput = True

  prStartInfo.RedirectStandardOutput = True

  prStartInfo.RedirectStandardError = True

  prStartInfo.CreateNoWindow = True

  process.StartInfo = prStartInfo

  process.Start()

  streamWriter = process.StandardInput

  streamReader = process.StandardOutput

  err = process.StandardError

  streamWriter.Close()

  'System.Windows.Forms.MessageBox.Show(result)

  Else

  Dim logConstr As String

  logConstr = Dts.Connections("Log File Connection String").ConnectionString

  Dim logfile As StreamWriter = New StreamWriter(logConstr, False)

  logfile.WriteLine("Fail to Find Records File to be Encrypted.")

  Dts.TaskResult = Dts.Results.Failure

  logfile.Close()

  End If

  End Sub