none
Fail a job if no file exists RRS feed

  • Question

  • I have a SQL Job and want to add a step at the start which looks in a directory and tries to find a file that looks like this:

    UAT43536451345.dat    

    The criteria is that it starts with the letters 'UAT', has an extension of .dat and has a date modified of today.

    If there is no such file I want the job to fail.

    Anyone have any pointers how I would go about this?

     

    Thanks

    Zoe

    • Changed type KJian_ Wednesday, May 18, 2011 12:49 AM
    Tuesday, May 17, 2011 8:03 AM

Answers

  • 1. create a variable say, file_name, give it a blank value (no value)

    2. create a 'For each Loop Container'  - use foreach file enumerator

    3. set folder to your folder path

    4. use UAT*.dat as files and tick names only

    5, on varialbe mappings in side the foreach loop container, add, file_name with index 0

    6. add an execute sql task

    7. add an oledb (doesn't mater which database, just need a right connection)

    8. drag the constrain from for each container to the execute sql task

    9. use constrain and expressions

    10, add this as expression: @file_name == ""

    11. in sql add: select 1 / 0 (this is demao purpose to force the task to fail you can use the better way by yourself.)

     

    you are done, if it find a file this job pass, otherwise it fails.

     

    NOte, this is a simplified example to demostrate the function, I believe this is not the best way to do it.

     



    Tuesday, May 17, 2011 9:46 AM

All replies

  • I believe that the easiest way is to create an SSIS job. Use for each file emuerator for a folder and check if there is a file starts with UAT and end with .dat. if you find one file meets the condition you finish the package successfully, otherwise force it to fail.
    Tuesday, May 17, 2011 9:19 AM
  • Ive created a 'For each Loop Container'  - When I double click on it I get a dialog box, I click on 'Collection' and under 'enumerator' it says 'Foreach File Enumerator' 

    But how do I specify the folder?

     

    Thanks for your help

    zoe

    Tuesday, May 17, 2011 9:28 AM
  • 1. create a variable say, file_name, give it a blank value (no value)

    2. create a 'For each Loop Container'  - use foreach file enumerator

    3. set folder to your folder path

    4. use UAT*.dat as files and tick names only

    5, on varialbe mappings in side the foreach loop container, add, file_name with index 0

    6. add an execute sql task

    7. add an oledb (doesn't mater which database, just need a right connection)

    8. drag the constrain from for each container to the execute sql task

    9. use constrain and expressions

    10, add this as expression: @file_name == ""

    11. in sql add: select 1 / 0 (this is demao purpose to force the task to fail you can use the better way by yourself.)

     

    you are done, if it find a file this job pass, otherwise it fails.

     

    NOte, this is a simplified example to demostrate the function, I believe this is not the best way to do it.

     



    Tuesday, May 17, 2011 9:46 AM
  • Thanks for the instructions, but I cant seem to find where to do step 3 - set folder to your folder path..

     

    Sorry, Im not very familiar with SSIS..

     

    Cheers

    Tuesday, May 17, 2011 10:05 AM
  • in the for wach loop container, under collection,

    in the emunerator configuration section, Folder. (you can blowse to your folder by clicking the browse button at the right of the text box.)

     

    (by the way, what version you use?)

    Tuesday, May 17, 2011 10:16 AM
  • I was using BIDS 2008, so I couldnt see the folder path.... Ive loaded up 2005 though and I can now see it.

     

    Im not sure what you mean by step 9 though...  'use constrain and expressions' 

     

    Thank you again for your help with this!

     


    Tuesday, May 17, 2011 10:33 AM
  • double click the green line to bring up the precedence constraint editor and you can choose evaluation operation as expression and constraint. (sorry I was typing by my memory and didn't check the BIDS, now I'm looking and found it is expression and constraint.

    sorry for some confusion, but hopefully you can make this work. (I have just tested in my machine and it indeed is able to produce the result you want.) cheers!

    Tuesday, May 17, 2011 11:06 AM
  • I must be doing something wrong..

     

     

    Ive done what you say, but when I execute it succeeds (it should fail as there are not matching files in my directory..)

     

    Should the execute sql task be outside the for each loop container?

     

     

    Tuesday, May 17, 2011 11:22 AM
  •  

    Should the execute sql task be outside the for each loop container?


    Yes, it should outside the container for this test. otherwise you are not able to drag the precedence constraint line.
    Tuesday, May 17, 2011 11:33 AM
  • Ok thanks - seems to work now - only problem is I need to deploy it to a 2008 instance of SSIS, but when I load this package into BIDS 2008 it reports errors on execution

    Also - the execute sql task now appears to have a red x inside the box...

     

    Error 1 Validation error. Foreach Loop Container : The property "LoopEventName" is empty. The property cannot be empty.   Package.dtsx 0 0

    Error 2 Validation error. Foreach Loop Container : The property "ExitEventName" is empty. The property cannot be empty.   Package.dtsx 0 0

    Warning 3 BIDS Helper had problems highlighting expressions and configurations: Object reference not set to an instance of an object. Validate.dtsx 0 0

    Error 4 Validation error. Execute SQL Task : There were errors during task validation.   Validate.dtsx 0 0


    Tuesday, May 17, 2011 12:08 PM
  • I'm using 2008 bids, I don't have the problem. (i'm using the sql server 2008 R2). SSIS sometime just needs patient. can you directly do it in 2008? I believe you can make it work. as I don;t know your environment, i'm sorry I am not able to give you more advices.

    Tuesday, May 17, 2011 12:28 PM
  • Well thanks very much for your help stephen, very much appreciated

     

    Zoe

    x

    Tuesday, May 17, 2011 12:30 PM
  • Hi Zoe,

    Maybe you can try with powershell. You could create an script to check if teh file exists using Powershell Step Type in SQL Server Agent. I wrote a post talking about that, you cancheck it here: http://blogs.solidq.com/EnriquePuigNouselles/Post.aspx?ID=5&title=Checking+existing+files+with+Powershell+and+SQL+Server+Agent

    i hope you will find it useful :)

    Cheers!

     

    Tuesday, May 17, 2011 2:38 PM
  • Hi Enrique,

     

    Your powershell looks like its what I need!

     

    Have you any idea how I would alter it to look for a file with UAT at the start and .dat at the end?

     

    ie.   UAT5654367.dat

           UAT5464536.dat

    etc.

     

    Thanks,

    Zoe

     

    if ( (Test-Path -Path "O:\Interface\UAT*.dat") -eq $false)

    Wednesday, May 18, 2011 7:52 AM
  • Maybe this approach will help you:

    #Get the most recent file
    $LastFile=Get-ChildItem -Path "C:\temp\UAT*.dat" | Sort-Object -Property LastWriteTime -Descending | select Name -First 1
    
    #building the fullpath ie:C:\temp\UAT5464536.dat
    $fullPath="C:\temp\"+$lastFile.Name
    
    ##checking condition
    if ( (Test-Path -Path $fullPath) -eq $false)
    {
    	$err="Error: $($lastFile.Name) does not exist."
    	throw $err
    }
    
    

    First of all i get the most recent file with the format UAT*.dat and then i check the same that we were checking before.

     

    Sincerely,

    Kike

    Wednesday, May 18, 2011 8:55 AM
  • 1. create a variable say, file_name, give it a blank value (no value)

    2. create a 'For each Loop Container'  - use foreach file enumerator

    3. set folder to your folder path

    4. use UAT*.dat as files and tick names only

    5, on varialbe mappings in side the foreach loop container, add, file_name with index 0

    6. add an execute sql task

    7. add an oledb (doesn't mater which database, just need a right connection)

    8. drag the constrain from for each container to the execute sql task

    9. use constrain and expressions

    10, add this as expression: @file_name == ""

    11. in sql add: select 1 / 0 (this is demao purpose to force the task to fail you can use the better way by yourself.)

     

    you are done, if it find a file this job pass, otherwise it fails.

     

    NOte, this is a simplified example to demostrate the function, I believe this is not the best way to do it.

     




    Dear Steven,

           Thanks for the steps. I am new to SSIS. Could ypu please tell me how to do step 11? I did not get it.

     

    Thanks in advance,

    Sandesh

    Wednesday, September 14, 2011 11:14 AM
  • Found Steven's answer very useful indeed.

    Many thanks.


    Mpumelelo

    Thursday, March 22, 2012 2:59 PM
  • I know that this thread is old. However, for the benefit of those who may want to use this thread for reference just like I did, I have decided to add a few comments about some things which I found beneficial.

    This solution as explained by Steven works well. However, further to Steven’s points I have modified the solution into the following setup. Items 1 to 5 remain the same as Steven suggested. I have italicised those items below. The modifications that I have added are from item 6 onwards – which then is as follows:

    1. create a variable say, file_name, give it a blank value (no value)
    2. create a 'For each Loop Container'  - use foreach file enumerator
    3. set folder to your folder path
    4. use UAT*.dat as files and tick names only
    5. on variable mappings inside the foreach loop container, add, file_name with index 0
    6. add two Execute SQL Tasks

    1. Execute SQL Task No. 1.
      • This task will be responsible for determining Success, i.e. if the file of interest in the Source Folder is PRESENT
      • Drag the precedence constraint from the Foreach Loop Container to the Execute SQL Task No. 1 and use the following Constraint options:

    Evaluation Operation:  Expression And Constraint

    Value:  Failure (Failure because the condition specified in the expression below won’t be fulfilled).

    Expression: @file_name != ""

    Open Execute SQL Task No. 1 to Edit it:

    On Connection Type use OLEDB;

    On Connection – it doesn’t matter which database you use as long as it is the right connection;

    On SQL Statement use: select 1/NULLIF(0,0)

    1. Execute SQL Task No. 2.
      • This task will be responsible for determining Failure, i.e. if the file of interest in the Source Folder is NOT there
      • Drag the precedence constraint from the Foreach Loop Container to the Execute SQL Task No. 2 and use the following Constraint options:

    Evaluation Operation:  Expression And Constraint

    Value:  Success  (Success because the condition specified in the expression below will be fulfilled).

    Expression: @file_name == ""

    Open Execute SQL Task No. 2 to Edit it:

    On Connection Type use OLEDB;

    On Connection – it doesn’t matter which database you use as long as it is the right connection;

    On SQL Statement use: select 1/0

    Drag all these (Foreach Loop and Execute SQL Tasks) into a Sequence Container. Connect the rest of your ETL tasks and containers to this sequence container.

    Test results will give you exactly what you are looking for – i.e. the job will fail if the kind of file that you are looking in the source folder is not there and the job will succeed if the kind of file that you are looking for is in the source folder.


    Mpumelelo

    • Proposed as answer by Mpumelelo S Friday, June 1, 2012 11:56 AM
    • Edited by Mpumelelo S Monday, April 7, 2014 4:39 PM
    Friday, June 1, 2012 11:52 AM