Run Stored DTS Package in VBScript & FTP Output

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
Unfortunately, I haven't been able to do much for indepth articles lately.  Taking care of my 5 month old son Caeden is more than a handful.  With that in mind, I've only been including some recent scripts I've done.  This one is no different.  I received a requirement to export all or a portion of our SQL Server database to Microsoft Access and then publish the Access .mdb on a remote FTP server.
Assuming once I had successfully done this one, they'd undoubtedly ask for it on other projects.  So, I tried to write it in such a way that it would be easy to implement with any database.  The other requirement was that I couldn't install any new software on the database servers.  Thus, I opted for good old VBScript.
 


 
This example uses a parent folder called dtsjobs.  It holds an empty access database shell because the DTS package doesn't generate its own.  It also holds the first half of an FTP script.  This gives you a centralized place to change your FTP connection settings across all projects.  Prior to running the sample, you'll need to open this with NotePad and adjust the settings.
The folders underneath dtsjobs are specific to projects.  In this sample, we'll call it project1.  Since we could eventually have multiple jobs for the same project, we'll name this job job1.vbs.  Of course, you'll want to use more descriptive names.  In the job1.vbs file, you'll see that there are module level variables to define all of the settings we'll need.  This makes it easy for you to copy a job file and modify it accordingly.  All output files are written to the files folder under our project folder.  The output files are named with a date/time stamp which allows us the flexibility of exporting our database as often as we need to throughout the day.
Now if the database structure changes, just recreate the DTS package and store it in SQL Server with the same package name.  Prior to running this sample, remember that a DTS package named Project1Export would need to exist.  Also, Microsoft Office or Microsoft Access is not required to be installed on your database server for this to work.  As long as you provide the empty Microsoft Access database file (.mdb), it will work just fine.
To download the code and folder structure in it's entirety: sample code.  Just unzip the file in the c:\ folder and a dtsjobs folder will be created.
Sample Code
  
  Dim msFolder
  Dim msBaseFolder
  Dim msAccessEmpty
  Dim msAccessFull
  Dim msProject
  Dim msFTPFile
  Dim msFTPFileLog
  Dim msToday
  Dim msDTSServer
  Dim msDTSUser
  Dim msDTSPwd
  Dim msDTSLog
  Dim msDTSPkg

  nMonth = month(date)
  if Clng(nMonth) < 10 then nMonth = "0" & nMonth

  msToday = year(date) & nMonth & day(date) & replace(FormatDateTime(now,4),":","")

  msProject = "project1"
  ' Get root folder of our app (ex. c:\dtsjobs )
  msBaseFolder = replace(ucase(WScript.ScriptFullName),ucase("\" & msProject & "\" & WScript.ScriptName),"")
  msFolder = msBaseFolder & "\"  & msProject 
  msAccessEmpty = "empty.mdb"
  msAccessFull = msProject & msToday &  ".mdb"
  msFTPFile = msFolder & "\"  & "Job1FTP.scr"
  msFTPFileLog = msFolder & "\"  & "Job1FTP.log"
  msDTSLog = msFolder & "\"  & "Job1DTS.htm"
 

  ' SQL Server connection settings and DTS Package name

  msDTSServer = "(local)"
  msDTSUser = "sa"
  msDTSPwd = "mypwd"
  msDTSPkg = msProject & "export"
 


 Sub ProcessJob()

  on error resume next

    Const DTSSQLStgFlag_Default = 0
    Const DTSStepExecResult_Failure = 1
    Const ForWriting = 2 

    Dim oPkg
    Dim oStep
    Dim bStatus
    Dim oFS
    Dim oFile

  ' Remove files from the previous instances of this job

    RemoveFile msFolder & "\files\"  & msAccessEmpty
    RemoveFile msFolder & "\files\"  & msAccessFull
    RemoveFile msFTPFile
    RemoveFile msFTPFileLog
    RemoveFile msDTSLog

 ' Copy the empty.mdb shell to our working folder

    CopyFile msBaseFolder, msAccessEmpty,msFolder & "\files",msAccessEmpty
 
   ' Execute our DTS Package stored in SQL Server

    Set oPkg = CreateObject("DTS.Package")
	
    oPkg.LoadFromSQLServer msDTSServer,msDTSUser,msDTSPwd,DTSSQLStgFlag_Default,"","","",msDTSPkg

    oPkg.Execute()
	
    bStatus = True
	
   ' Write a log file displaying success or failure for each package step.

    Set oFS = CreateObject("Scripting.FileSystemObject")

    Set oFile = oFS.OpenTextFile(msDTSLog,ForWriting,True) 

    oFile.WriteLine "<html><body>" 
    oFile.WriteLine "Execution time: " & Now() 

    For Each oStep In oPkg.Steps

        oFile.WriteLine "   Pkg Step " & oStep.Name & " "

        If oStep.ExecutionResult = DTSStepExecResult_Failure Then
           oFile.WriteLine " failed<br>"
           bStatus = False
        Else
           oFile.WriteLine " succeeded<br>"
        End If
 
        oFile.WriteLine "Task """ & oPkg.Tasks(oStep.TaskName).Description & """<br>"

   Next
	
   If bStatus Then
      oFile.WriteLine "Package " & oPkg.Name & " succeeded<br>"
   Else
      oFile.WriteLine "Package " & oPkg.Name & " failed<br>"
   End If
	
   Set oPkg = nothing	 

   oFile.WriteLine  "Done: " & Now() & " " &  "<br></body></html>"

   ' Close the package log file

   oFile.Close

   Set oFile = nothing
   Set oFS = nothing

   ' Rename our working empty.mdb to our desired filename

     RenameFile msFolder & "\files",msAccessEmpty,msAccessFull

   ' FTP the file
 
     FTPPutFile 

 End Sub
 

 Sub FTPPutFile()

   Dim oFS 

   Dim oFile 
   Dim oFileScript
   Dim oStream
   Dim oShell

   Const ForReading = 1 
   Const ForWriting = 2 
   Const ForAppending = 8 
   Const TristateUseDefault = -2 
   Const TristateTrue = -1 
   Const TristateFalse = 0 
 
 on error resume next

  ' Grab the general FTP login info

   Set oFS = CreateObject("Scripting.FileSystemObject") 

   Set oFileScript = oFS.GetFile(msBaseFolder & "\"  & "FTP.scr") 

  ' Open the new empty FTP script for writing

   Set oFile = oFS.OpenTextFile(msFTPFile,ForWriting,True) 

  ' Read the general FTP login info line by line

   Set oStream = oFileScript.OpenAsTextStream(ForReading, TristateUseDefault) 

   Do While Not oStream.AtEndOfStream 
     
     ' Write general login info to our new FTP script
      oFile.WriteLine oStream.ReadLine 
 
   Loop 

  ' close the general login info file

   oStream.Close 

   Set oStream = nothing
   Set oFileScript = nothing

   ' write job specific commands to the FTP script

    oFile.WriteLine "binary"
    oFile.WriteLine "lcd " &  msFolder & "\files"
    oFile.WriteLine "cd " & msProject
    oFile.WriteLine "put " & msAccessFull 
    oFile.Write "quit"
 
    oFile.Close 

     Set oFile = Nothing 
     Set oFS = Nothing

    ' Execute the FTP script and write the results to the log file

      Set oShell = CreateObject("WScript.Shell") 

      oShell.Run "%comspec% /c ftp.exe -i -s:" & msFTPFile & " >"  & msFTPFileLog, 0, True 
 
      Set oShell = nothing
  
      sErrFolder = msFolder & "\error.txt"
  
      if err.number <> 0 then WriteFile sErrFolder, err.description 
      
 End Sub
	  

 Sub WriteFile(sFilePathAndName,sFileContents)   

  on error resume next

  Const ForWriting=2 

  
  Set oFS = CreateObject("Scripting.FileSystemObject") 
   
  Set oFSFile = oFS.OpenTextFile(sFilePathAndName,ForWriting,True) 
   
   oFSFile.Write(sFileContents) 
   
   oFSFile.Close 
   
   Set oFSFile = Nothing 
   Set oFS = Nothing 

 End Sub 
 

 Sub RemoveFile(sFilePathAndName) 

  Set oFS = CreateObject("Scripting.FileSystemObject") 
   
  If oFS.FileExists(sFilePathAndName) = True Then 
     oFS.DeleteFile sFilePathAndName, True 
  end if 

  Set oFS = Nothing 
   
 End Sub 

 Sub CopyFile(sFileFromFolder,sFileFrom,sFileToFolder,sFileTo) 

  Set oFS = CreateObject("Scripting.FileSystemObject") 
 
     oFS.CopyFile sFileFromFolder & "\"  & sFileFrom,sFileToFolder & "\" 
  
  Set oFS = Nothing 
   
 End Sub 

 Sub RenameFile(sFolder,sFileFrom,sFileTo) 

  Set oFS = CreateObject("Scripting.FileSystemObject") 
 
     oFS.MoveFile sFolder & "\"  & sFileFrom,sFolder & "\"  & sFileTo 
  
  Set oFS = Nothing 
   
 End Sub 

 ProcessJob


Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.