Using VBScript to Automate Tasks
By Haidong Ji
A key component of proactive monitoring is automation. If we can automate a lot of routine tasks, we will have time to focus on more important issues. In addition, automation also frees up time for you as a busy DBA, so you can learn new things and continuously improve yourself.
Automation tools available for SQL Server are SQL Server Agent, SQL (Stored Procedures), DTS, ActiveX scripts (VBScript), SQL Server command line tools (OSQL/ISQL), DOS command batch files, WMI, SQL Mail, to name just a few. They all have their pluses and minuses. Depending on the situation, one method might be better suited than others.
In this article, I will give you a very brief introduction of VBScript. I will then provide you with a couple of examples of using VBScript to automate file deletion and FTPing files. Hopefully they can give you some ideas on where and how to use VBScript. Both examples can be modified and put into use easily.
In the next few weeks, I will provide some introductions and examples on other automation tools, such as WMI, shell command and batch files, Stored Procedures, etc
What is VBScript
Visual Basic Scripting Edition brings active scripting to a wide variety of environments, like web programming, Windows management, and SQL Server management. Unlike VB6 or VB7 (Thank God Microsoft will drop the .NET lingo, I absolutely hate it. It is a mouthful.), it is not a full-blown, feature rich programming language. VBScript resembles more VB6 than VB7. If you are already familiar with Visual Basic or VBA (Visual Basic for Applications), you should have no trouble learning VBScript. Even if you are a beginner, VBScript is not that hard and there are plenty of resources available on the web to help you out. Andy Warren had an article a few months ago on SQL-DMO. Many ideas and techniques presented in that article can also be applied in general VBScript programming.
VBScript has only one data type called Variant. A Variant is a special kind of data type that can contain different kinds of information, depending on how it is used. One subtype of variant is Object. When assigning or instantiating an object (FileSystemObject, File object, etc.), remember to use the SET keyword. This is very important, especially for beginners.
In SQL Server, you can use VBScript to create an ActiveX task in DTS. You can also add your script as a step to SQL Server Agent job, just remember to select ActiveX Script as the step type. The following 2 examples can be used in both ways.
Example 1: VBScript to delete files in a folder that are certain days old
As part of a disaster recovery plan, you may need to transfer backup files from one server to another. However, you probably don't want to keep accumulating backup files that you run out of space on your backup server. In this case, you want to delete files that are certain days (or weeks) old. The following VBScript can handle this task nicely. You can customize this code, such as changing the value of iDaysOld, to fit your needs. Most of the code should be self-explanatory. For more on disk space management, see one of my articles published a couple of weeks ago.
Option Explicit on error resume next Dim oFSO Dim sDirectoryPath Dim oFolder Dim oFileCollection Dim oFile Dim iDaysOld 'Customize values here to fit your needs iDaysOld = 21 Set oFSO = CreateObject("Scripting.FileSystemObject") sDirectoryPath = "FolderName here. Can be UNC path like \\MyServer\MyFolder" set oFolder = oFSO.GetFolder(sDirectoryPath) set oFileCollection = oFolder.Files 'Walk through each file in this folder collection. 'If it is older than 3 weeks (21) days, then delete it. For each oFile in oFileCollection If oFile.DateLastModified < (Date() - iDaysOld) Then oFile.Delete(True) End If Next 'Clean up Set oFSO = Nothing Set oFolder = Nothing Set oFileCollection = Nothing Set oFile = Nothing
Example 2: VBScript to automate FTP files
Many of us work in a heterogeneous technical environment. Our environment may require us to get files from *nix servers or mainframe. The following example demonstrate a technique to get a file via FTP, assuming the file you want to get daily is named as YYYYMMDDData.csv. Because the file name changes daily, so we generate FTP script files on the fly. The rest of the code should be self-explanatory.
Option Explicit Dim objFSO, objMyFile, objShell, strFTPScriptFileName, strFile2Get Dim strLocalFolderName, strFTPServerName, strLoginID Dim strPassword, strFTPServerFolder 'Customize code here to fit your needs strLocalFolderName = "My Folder Name where we put the file to be FTPed" strFTPServerName = "FTP Server Name" strLoginID = "FTP Server Login ID" strPassword = "FTP Login ID Password" strFTPServerFolder = "Folder Name on FTP server where the file resides" 'The following code converts date to the right format, YYYYMMDD strFile2Get = DatePart("yyyy",Date) If DatePart("m",Date) < 10 Then strFile2Get = strFile2Get & "0" End If strFile2Get = strFile2Get & DatePart("m",Date) If DatePart("d",Date) < 10 Then strFile2Get = strFile2Get & "0" End If strFile2Get = strFile2Get & DatePart("d",Date) 'The following code generates the file name on the FTP server you want to get strFile2Get = "Data" & strFile2Get & ".csv" 'The follow lines of code generate the FTP script file on the fly, 'because the get file name changes every day strFTPScriptFileName = strLocalFolderName & "\FTPScript.txt" Set objFSO = CreateObject("Scripting.FileSystemObject") If (objFSO.FileExists(strFTPScriptFileName)) Then objFSO.DeleteFile (strFTPScriptFileName) End If Set objMyFile = objFSO.CreateTextFile(strFTPScriptFileName, True) objMyFile.WriteLine ("open " & strFTPServerName) objMyFile.WriteLine (strLoginID) objMyFile.WriteLine (strPassword) objMyFile.WriteLine ("cd " & strFTPServerFolder) objMyFile.WriteLine ("ascii") objMyFile.WriteLine ("lcd " & strLocalFolderName) objMyFile.WriteLine ("get " & strFile2Get) objMyFile.WriteLine ("bye") objMyFile.Close Set objFSO = Nothing Set objMyFile = Nothing 'The following code executes the FTP script. It creates a Shell 'object and run FTP program on top of it. Set objShell = WScript.CreateObject( "WScript.Shell" ) objShell.Run ("ftp -s:" & chr(34) & strFTPScriptFileName & chr(34)) Set objShell = Nothing
In this article, I gave a very brief introduction to VBScript and provided 2 working examples. Hopefully they demonstrated enough techniques to get you started. Stay tuned for more articles from me on other ways to automate SQL Server management.