Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Handling Zero Byte Files in DTS

By James Greaves,

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 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>
	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
		Main = DTSStepScriptResult_ExecuteTask
	End If
	Set oFile = Nothing
	Set oFSO = Nothing
	End Function


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.


Total article views: 6772 | Views in the last 30 days: 1
Related Articles

Using the Dynamic Properties Task

In SQL Server 7.0 Data Transformation Services (DTS), dynamically configuring packages was difficult...


Export option (right click in the database - tasks - export data...)

Export option (right click in the database - tasks - export data...)


IsSort on Derived column transformation

IsSort property on Derived column transformation


Accessing Dynamic Properties task Properties in Activex Script

DTS Package/ Dynamic Properties task


Change Package Properties Using Parameters–SSIS 2012

In DTS, changing package properties was close to impossible (the less we talk about DTS, the better)...

sql server 7