Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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>
	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

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

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

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

ARTICLE

Using the Dynamic Properties Task

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

FORUM

IsSort on Derived column transformation

IsSort property on Derived column transformation

FORUM

Accessing Dynamic Properties task Properties in Activex Script

DTS Package/ Dynamic Properties task

BLOG

Change Package Properties Using Parameters–SSIS 2012

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

Tags
dts    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones