Stop DTS Package Execution if 0 Byte File

Question

Stop DTS Package Execution if 0 Byte File

JN-161845

SSCrazy

Points: 2052
More actions
March 24, 2005 at 3:03 pm

#61668

Hello,
I have a DTS package that imports data from CSV, but it fails with error when the source feed is 0 byte. How can I stop the pacakge from execution if the source CSV is 0 byte? Or is there a way to process a 0 byte file without getting execution error? I imagine this can be done in ActiveX, but wasn't sure how this can be written.
Thanks for your help.
jn

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Using FileSystemObjects, sorry no sample on hand, but look for that in the seach of the forum should turn up a few. Use FileSystemObjects to check the file size and throw a fail or other to change the execution.

mike gercevich SSCrazy Points: 2961 More actions · Answer 2

Use an Active-X task script to open the file as a TextStream

use a loop like such:

set objFile= objFS.OpenTextFile("c:\somefile2.csv", 1, false)

WHILE NOT objFile.AtEndOfStream

sLine = objFile.ReadLine

'** Process the Line

WEND

Mike Gercevich

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 3

jn,

You could also try something like this:

Dim oFS

Dim oFile

Dim oFolders

Dim oFiles

Set oFS = CreateObject("Scripting.FileSystemObject")

Set oFolders = oFS.GetFolder("Folderspec")

Set oFiles = oFolders.Files

For Each oFile In oFiles

MsgBox ("file size is " & oFile.Size)

Set oFiles = Nothing

Set oFile = Nothing

Set oFS = Nothing

Good Luck,

Darrell

JN-161845 SSCrazy Points: 2052 More actions · Answer 4

Thanks for all your suggestions. What I still would like to know is how can I stop my DTS from executing if let's say I found using FSO that the file is 0 byte?

I came across a script to stop transformation so will give that a try.

JN

Rajesh Hegde Right there with Babe Points: 761 More actions · Answer 5

To stop your DTS package , you need to flag the step to say that the DTS package should fail if the particular step fails.

In case of Activex Script task, if the file size is 0, then the script should return "DTSTaskExecResult_Failure". Right click the Activex task and seleck workflow properties. Under the execution section, check "Fail package on step failure" option. This will cause your DTS to stop execution.

Hope this helps.

Rajesh

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 6

You could also disable the following steps (avoiding having the package return a bogus failed status), much cleaner this way.

take a look at the samples on http://www.sqldts.com/