The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

DTS trying to import empty file

I have a DTS package that updates a bunch of tables from a txt file. The odd time, there will be no updates, so the txt file will be blank. Making the DTS package fail. Is there a way to check if the file is blank, then just ignor the next step??

In package design mode, you can insert an ActiveX (VBScript) step that uses FileSystemObject to detect the size of the file. Assuming column headers are not present, the file would be zero bytes. Likewise, you could count the number of lines in the file. Yeah, this is all sorta hokey, but it's a quick fix.

If you give me more information about the TXT files, I could do a few quickie experiments..

Again Danfran right now that is how I am doing it. (Just like when you helped me out in the subject 'Sums of Sums')
I have an ActiveX to check to see if there is a file to begain with. It gets a 'Success or Fail' status.

Next ActiveX checks to see if there are any records in there. I could read the file size, or read the first line of data. Either way works fine that I've tested. So its all good up to now.

if objFile.fileexists(filename) then
[your block here]
else
main = dts_fail
end if

The way to make a step fail the entire package (what you want in case of missing file) is to right click on the step in designer mode, then select workflow, workflow properties. On the OPTIONS tab, you'll see a checkbox that says "Fail Package on Step Failure".

If the file is present in the first step, then success or failure of the package will be determined ONLY by existence of the file (success or failure of the step). (and maybe some parsing error that you define otherwise).

--> The problem I have is not knowing whether or not you have other steps. If you don't, then that's the end. If both emtpy-file or full-file are "success", then say so in your [EndOfStream] code block! You can use "EXIT SUB" in Sub Main().

The difference between the following items is whether or not you have more steps to do in your package..

that it automactily fails the DTS package.. I didn't actually run it in a schedule to find out if it does or not, because when ever you run the script it says 'The task reported a failure on execution'.

I'm going to run it in a schedule right now and play around with it..
Except our T3 line is down... I'll post back tomorrow..

When there are records, there are more steps to take in the DTS. The steps will fail, if there are no records, thats why I have to check first, to see if it's blank or not. Thanks again for your help.

Originally Posted by danfran

Just wrap the " 'Open the file" block with this..

if objFile.fileexists(filename) then
[your block here]
else
main = dts_fail
end if

The way to make a step fail the entire package (what you want in case of missing file) is to right click on the step in designer mode, then select workflow, workflow properties. On the OPTIONS tab, you'll see a checkbox that says "Fail Package on Step Failure".

If the file is present in the first step, then success or failure of the package will be determined ONLY by existence of the file (success or failure of the step). (and maybe some parsing error that you define otherwise).

--> The problem I have is not knowing whether or not you have other steps. If you don't, then that's the end. If both emtpy-file or full-file are "success", then say so in your [EndOfStream] code block! You can use "EXIT SUB" in Sub Main().

The difference between the following items is whether or not you have more steps to do in your package..