VB to VB.Net for SSIS Logging

• jglayden

  SSC Journeyman

  Points: 94

  More actions

  February 26, 2014 at 2:30 pm

  #286188

  I am currently converting old DTS packages to SSIS (SQL Server 2005) and would like to use this simple form to log each process that is completed. I understand that I can use a VB script task, but can someone help me convert this to .Net in order for this to work. Or is there a better script that can give me basic logging to a file and send an email message. I use SSIS logging for some of the packages that I have converted over, but as many people write, it isn't the best to just know what packages are currently running and to quickly see the start and finish time. Thanks for everyone's help.

  --Beginning of the DTS

  '**********************************************************************

  ' Visual Basic ActiveX Script

  '************************************************************************

  Function Main()

  '************************************************************************

  ' Local variable definitions

  '************************************************************************

  dim objMail

  Const ForReading = 1, ForWriting = 2, ForAppending = 8

  dim fso,log

  '************************************************************************

  ' initialize local variables

  '************************************************************************

  Set objMail = CreateObject("CDO.Message")

  objMail.Subject = "Nightly Master Job has started"

  objMail.From = "xxx@xxxxx.com"

  objMail.To = DTSGlobalVariables("gEmail").Value

  objMail.TextBody = "The job started running at " & Now()

  'This section provides the configuration information for the remote SMTP

  'server.Normally you will only change the server name or IP.

  objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

  'Name or IP of Remote SMTP Server

  objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "xxxxx"

  'Server port number(typically 25)

  objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

  'Sets up NTLM authentication using the credentials of the running thread

  objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 2

  objMail.Configuration.Fields.Update

  'msgbox "pre-send"

  'On Error Resume Next

  objMail.Send

  ' msgbox Err.Description

  Set objMail = Nothing

  set fso = CreateObject("Scripting.FileSystemObject")

  DTSGlobalVariables("logFile").Value = "D:\Master_DTSightly_master_job_log_" & Year(Now) & Month(Now) & Day(Now) & ".txt"

  'msgbox DTSGlobalVariables("logFile").Value

  if not fso.FileExists(DTSGlobalVariables("logFile").Value) then fso.CreateTextFile DTSGlobalVariables("logFile").Value, true end if

  set log= fso.OpenTextFile(DTSGlobalVariables("logFile").Value, ForAppending)

  log.WriteLine("[" & NOW &"]" & vbTab & "Nightly Master Job Started!")

  returnStatus = DTSTaskExecResult_Failure

  Main = DTSTaskExecResult_Success

  End Function

  --End of the DTS

  '**********************************************************************

  ' Visual Basic ActiveX Script

  '************************************************************************

  Function Main()

  Const ForReading = 1, ForWriting = 2, ForAppending = 8

  dim fso,log

  set fso = CreateObject("Scripting.FileSystemObject")

  if not fso.FileExists(DTSGlobalVariables("logFile").Value) then fso.CreateTextFile DTSGlobalVariables("logFile").Value, true end if

  set log= fso.OpenTextFile(DTSGlobalVariables("logFile").Value, ForAppending)

  log.WriteLine("[" & NOW &"]" & vbTab & "Nightly Master Job Completed Successully!")

  returnStatus = DTSTaskExecResult_Failure

  Main = DTSStepScriptResult_ExecuteTask

  End Function