SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Owen Sound
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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??

  2. #2
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..

    Hopefully others here can offer better suggestions..

    Dan

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Owen Sound
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    Function Main()
    	
    Dim strFile		'String of text file
    Dim objFile		'Scripting Object
    Dim FileInput		'Text File Source
    	
    	Set objFile = CreateObject("Scripting.FileSystemObject")
    	
    	strFile = DTSGlobalVariables("strFile").Value
    
    	'   Open the file
    	Set FileInput = objFile.OpenTextFile(strFile, 1)
    
    	If FileInput.AtEndOfStream Then
    		Main = DTSTaskExecResult_Failure
    	Else
    		Main = DTSTaskExecResult_Success
    	End If
    
    	Set objFile = Nothing
    End Function
    How can i made the ActiveX quit the DTS with a success status when there is an empty file?

    So in an ActiveX file we have..

    No file -> Fail
    File with Data -> Success
    File with no Data -> Success but quit DTS package

  4. #4
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..

    Code:
    File with Data -> Success
    File with no Data -> Success but quit DTS package
    Do you have more steps?

    Dan

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Owen Sound
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The file exists part, is working fine.

    I thought when you put

    Main = DTSTaskExecResult_Failure

    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.


    Quote 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..

    Code:
    File with Data -> Success
    File with no Data -> Success but quit DTS package
    Do you have more steps?

    Dan

  6. #6
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dude, I should clarify something..

    I mean to say, do you have more "tasks"? A task being an icon on your package when you have design mode open..

    Are you attempting to run your "whole package" inside of a single ActiveX script task?

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Owen Sound
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A picture says a million words..



    Ok so I was wrong. When I schedule the job, it reports 'fail' for both Failed - Source and No records, even when I clicked on 'Work Flow' on the Failed - Source and picked - Fail DTS on failure..

    I just want to know how to stop a DTS package from running through Active X. (and report Success)

    Let me know if i still need to clear things up?


    EDIT:

    Ok I think I got it. I went to the workflow properties of Create Temp_Alt... and put in my code there.. Then deleted 'Check Records' and 'No Records'.

    Code:
    Function Main()
    	
    Dim strFile		'String of text file
    Dim objFile		'Scripting Object
    Dim FileInput		'Text File Source
    	
    	Set objFile = CreateObject("Scripting.FileSystemObject")
    	
    	strFile = DTSGlobalVariables("strFile").Value
    
    	'   Open the file
    	Set FileInput = objFile.OpenTextFile(strFile, 1)
    
    	If FileInput.AtEndOfStream Then
    		Main = DTSStepScriptResult_DontExecuteTask
    	Else
    		Main = DTSStepScriptResult_ExecuteTask
    	End If
    
    	Set objFile = Nothing
    End Function
    Last edited by slboytoy; Feb 1, 2005 at 08:16.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •