Zero byte files can be a common issue when working with DTS packages in SQL Server. These files, which have a size of 0 bytes, can cause problems during the import process. In this article, we will discuss how to handle zero byte files in SQL Server and provide a solution using VBScript and CDO.

Setting up the Data Transformation

The first step is to set up your source and destination data with a data transformation between them. Right-click on the data transformation and select “Workflow Properties”. In the Workflow Properties dialog box, go to the “Options” tab.

If you want the DTS package to fail on the step in the event of a zero byte file, check the “Fail package on step failure” checkbox. If you only want the package to bypass the file import if a zero byte file is encountered, leave this checkbox blank.

Handling Zero Byte Files with VBScript

To handle zero byte files, we will use VBScript and CDO. Click on the “Use ActiveX script” checkbox and then click the “Properties” button to open a VBScript window. Insert the following code:

'**********************************************************************
' Visual Basic ActiveX Script
'**********************************************************************

Function Main()

Dim oFSO
Dim oFile
Dim FileNm

' File you are verifying. Using UNC path name is acceptable if SQL Agent has domain privileges on share
FileNm = <File Name here> 'A global variable could be used here as well

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.GetFile(FileNm) 

' If-Then statement to check import file. If it is zero bytes, send an e-mail using CDO 
' notifying of the zero byte file and either bypass or fail the DTS package

If oFile.Size = 0 Then
    ' Code for sending e-mail to the desired recipient using CDO
    Dim objMail
    Set objMail = CreateObject("CDO.Message")
    objMail.From = <insert e-mail address here>
    objMail.To = <insert e-mail address here>
    objMail.Subject = <insert subject here>
    objMail.TextBody = <insert body text here>
    objMail.Send

    Set objMail = Nothing
    
    ' Option 1: Choosing this option will stop the data import from taking place, but your DTS task will continue
    ' Main = DTSStepScriptResult_DontExecuteTask

    ' Option 2: Choosing this option will fail the DTS package if the "Fail Package on Step Failure" is turned on in the package properties
    Main = DTSTaskExecResult_Failure
Else
    Main = DTSStepScriptResult_ExecuteTask
End If

Set oFile = Nothing
Set oFSO = Nothing

End Function

Testing the Transformation

Once the VBScript is in place for the transformation, it is recommended to test it by creating a zero byte file and executing the step within the DTS package. Keep in mind that how you wish to handle zero byte files can be customized inside the IF/THEN statement. The example provided uses CDO mail to illustrate how it can be used in a work environment. You can also modify the process by using global variables for the file name and e-mail parameters.

By following these steps, you can effectively handle zero byte files in your SQL Server DTS packages and ensure a smooth data import process.

Enjoy!