Zero byte file handling in DTS

This article outlines how to handle the possibility of a zero byte file of any

format that your DTS package most likely relies upon for import for a data pump

task. Furthermore, when a zero byte file is encountered, an option to bypass or

stop the DTS step/package is possible as well as sending an e-mail via CDO as I

will illustrate in my example.

The first step is to setup your source and destination data with a data

transformation between them. Right click on the data transformation and you

will see the menu in Figure 1.

Figure 1: Workflow Properties transformation dialog

Choose the "Workflow Properties" option and the dialog box in Figure 2

will appear on the precedence tab by default. Click on the "options" tab and

bring up the dialog box as seen in Figure 2 below.

Figure 2: Workflow Properties Options tab

If you wish the package to fail on the step in the event of a zero byte file,

click the checkbox "Fail package on step failure". If you only desire

for the DTS package to bypass the file import if a zero byte file is

encountered, leave this checkbox blank and enable "Option 1" in the VB

script below that will be used to handle the detailed transformation property.

Check the "Use ActiveX script" checkbox and then click the "Properties"

button to open a VBScript window to insert the code that will handle the zero

byte file:

'**********************************************************************
' 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 and send e-mail using CDO 
' notifying of zero byte file and either bypass or fail theDTS 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
‘Option1: (Disabled below with comment tick.) Choosing this option will stop the data import 
' from taking place but your DTS task will continue
‘Main = DTSStepScriptResult_DontExecuteTask

‘Option 2: (Enabled below) 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

Conclusions

Once the VB script is in place for the transformation, I suggest making a zero byte file with and testing the transformation by right clicking and choosing "Execute step" within the DTS package. Keep in mind that how you wish to handle zero byte files can be done inside the IF/THEN statement as you wish. I used the CDO mail as an example so I could illustrate how I use it in my work environment. Further modifications of this process could include the use of global variables for the file name and e-mail parameters.

Enjoy