Issue with SQL Server 2005 Scripting Language

Issue with SQL Server 2005 Scripting Language

• zlly20

  Ok try the following code, the chnaged lines are in bold and basically are using a variable for the fileinfo as NoBugz suggested and putting some additional code around the delete to only attempt to delete them if they exists and put any additional exception handling around the deletes to capture any exceptions which might occur.

  Potentially, the files not be created if they dont have sufficient permissions on the share that is being written, or they may not have the permissions to delete files

  ' Microsoft SQL Server Integration Services Script Task
  ' Write scripts using Microsoft Visual Basic
  ' The ScriptMain class is the entry point of the Script Task.

  Imports System
  Imports System.Data
  Imports System.Math
  Imports System.IO
  Imports System.IO.File
  Imports System.IO.FileSystemInfo
  Imports Microsoft.SqlServer.Dts.Runtime

  Public Class ScriptMain

  ' The execution engine calls this method when the task executes.
  ' To access the object model, use the Dts object. Connections, variables, events,
  ' and logging features are available as static members of the Dts class.
  ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
  '
  ' To open Code and Text Editor Help, press F1.
  ' To open Object Browser, press Ctrl+Alt+J.

  Public Sub Main()

  Dim strCurrentMonth As String
  Dim strCurrentYear As String
  Dim strWriteFileName As String
  Dim strReadHeaderFileName As String
  Dim strReadBodyFileName As String

  'Utilizing a case statement, determine the monthname & year and set the appropriate variables

  Select Case Month(Now())
  Case 1
  strCurrentMonth = "January"
  Case 2
  strCurrentMonth = "February"
  Case 3
  strCurrentMonth = "March"
  Case 4
  strCurrentMonth = "April"
  Case 5
  strCurrentMonth = "May"
  Case 6
  strCurrentMonth = "June"
  Case 7
  strCurrentMonth = "July"
  Case 8
  strCurrentMonth = "August"
  Case 9
  strCurrentMonth = "September"
  Case 10
  strCurrentMonth = "October"
  Case 11
  strCurrentMonth = "November"
  Case 12
  strCurrentMonth = "December"
  End Select

  strCurrentYear = Year(Now()).ToString

  'Set variables with file names (reader files and write file) for ease in readability and to
  'set final (write file) with appropriate nameing convention utilized by Matria HealthCare.

  strWriteFileName = "\\CUPSRV05\SHARED\IS\Public\Data Export\Matria\Files TO Matria\cup_ref_cup_" & strCurrentMonth & strCurrentYear & "_ftp_ReferralFormat.txt"

  strReadHeaderFileName = "\\CUPSRV05\SHARED\IS\Public\Data Export\Matria\Files TO Matria\Matria_Referral_Control.txt"

  strReadBodyFileName = "\\CUPSRV05\SHARED\IS\Public\Data Export\Matria\Files TO Matria\Matria_Referral.txt"

  'create stream reader/writer objects

  Dim sr As New StreamReader(strReadHeaderFileName)
  Dim sr2 As New StreamReader(strReadBodyFileName)
  Dim sw As New StreamWriter(strWriteFileName)

  'feed the header record into the final file

  Do Until sr.Peek = -1
  'write the header record
  sw.WriteLine(sr.ReadLine)
  Loop

  'close the read stream for the header record file
  sr.Close()

  'Feed the body records into the final file
  Do Until sr2.Peek = -1
  'write all base records
  sw.WriteLine(sr2.ReadLine)
  Loop

  'close the read stream for the body records
  sr2.Close()

  'close the write stream for the final distribution file
  sw.Close()

  'dispose of all stream objects
  sr.Dispose()
  sr2.Dispose()
  sw.Dispose()

  Dim EligBaseFile As New FileInfo(strReadBodyFileName)
  Dim EligHeaderFile As New FileInfo(strReadHeaderFileName)

  Try
  IF EligBaseFile.exists then
  EligBaseFile.Delete()
  End If
  Catch Ex as exception
  msgbox("Problem deleting file" & ex.message)
  End Try

  Try
  IF EligHeaderFile .exists then
  EligHeaderFile.Delete()
  End If
  Catch Ex as exception
  msgbox("Problem deleting file" & ex.message)
  End Try

  'final statement for SSIS package to determine script result

  Dts.TaskResult = Dts.Results.Success

  End Sub

  End Class

  
  

• tonyl1956

  Try removing the quotes in the FileInfo constructor. ;-)
  
  
  
  

• sab7a

  In addition to spotty comments you do not have access to the filesystem with the default sql clr settings. Check out the database trustworthy property.
  
  
  
• Issue with SQL Server 2005 Scripting Language