none
How to execute task only when files are present in the folder. RRS feed

  • Question

  • Hi,

    I have a package which imports the excel files from 2 different folder. I am looking a way to execute the package only when both the packages have excel files in it otherwise the package will not be executed on the server.

    But as of now if I schedule it to run for every 15 minutes on the server then the package will be executed after every 15 minutes even if the folders are empty. I am looking for a way which will restrict the package execution if any of the folders are empty.

    Thanks,

    Minhal

    Tuesday, October 8, 2019 3:13 PM

Answers

  • Hi Minhal, 

    May I know where do you use the File System Task?

    According to my test, the tasks inside the Foreach Loop Container will not be executed

    if the For Each File enumerator did not find any files that matched the file pattern

    or the specified directory was empty.

    So, we could put File System Task inside the Foreach Loop Container.




    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by minhalraffat Wednesday, October 9, 2019 6:17 PM
    Wednesday, October 9, 2019 8:00 AM

All replies

  • Hi

    I think below thread resolves similar issue but you just need to update the logic based on your requirement (where it is vice-versa in the thread)

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d5750a66-5d45-4ba9-975e-09c85247ae98/package-needs-to-start-if-folder-is-empty?forum=sqlintegrationservices

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, October 8, 2019 4:15 PM
  • Hi

    I think below thread resolves similar issue but you just need to update the logic based on your requirement (where it is vice-versa in the thread)

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d5750a66-5d45-4ba9-975e-09c85247ae98/package-needs-to-start-if-folder-is-empty?forum=sqlintegrationservices

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Hi nkumar,

    I followed the link which you have provided but the supporting link which implies on the solution is not working.

    Thanks,

    Minhal

    Tuesday, October 8, 2019 5:08 PM
  • Hi Minhal,

    it is a good practice to have the package executed even if there are no files, I do not see why not. So unless there is a strong reason, there is no harm, only benefits.

    This will simply give you confidence that all works and there are no files.

    However, what I suspect you need to rather do is trigger the package once the files arrive. Because having it working every 15 min or any other arbitrary interval irrespective to files size and quantity is not a prident design.

    Consider checking periodically for files using something that starts the packages and waits for their completion.

    I would do it via Windows Service written say in C# that has a FileWatcher then trigger the Agent job that has the packages with sp_start_job system stored procedure.


    Arthur

    MyBlog


    Twitter

    Tuesday, October 8, 2019 5:18 PM
    Moderator
  • Hi Minhal,

    it is a good practice to have the package executed even if there are no files, I do not see why not. So unless there is a strong reason, there is no harm, only benefits.

    This will simply give you confidence that all works and there are no files.

    However, what I suspect you need to rather do is trigger the package once the files arrive. Because having it working every 15 min or any other arbitrary interval irrespective to files size and quantity is not a prident design.

    Consider checking periodically for files using something that starts the packages and waits for their completion.

    I would do it via Windows Service written say in C# that has a FileWatcher then trigger the Agent job that has the packages with sp_start_job system stored procedure.


    Arthur

    MyBlog


    Twitter

    Hi Arthur,

    Yes I am trying to find a way to see if the files exists in both the folders only then the package executes. My package can run for all day there would be no problems with it but I have a file task system which is creating a new folder each time the package is executed and when it has no files it creates a empty folder. So I am trying not to have empty folders on my server.

    Is there a way of doing it without using c#?

    Thanks,

    Minhal

    Tuesday, October 8, 2019 5:24 PM
  • Hi Minhal,

    It is possible by using SSIS Foreach Loop Task and precedence constraint.

    Check it out here: Check if file exists in folder or not

    Tuesday, October 8, 2019 5:36 PM
  • Hi,

    I have a package which imports the excel files from 2 different folder. I am looking a way to execute the package only when both the packages have excel files in it otherwise the package will not be executed on the server.

    How about having:

    SQL Job Step 1 (PowerShell) - Run PS Code to check if files exist in both folders 
               - On Success: Go to Step 2 - Run SSIS package 
               - On Failure: Quit the job or have Step 3 - Send email - "Files not available, job completed without SSIS run


    Regards,
    Vaibhav

    Tuesday, October 8, 2019 5:55 PM
  • Hi Minhal, 

    May I know where do you use the File System Task?

    According to my test, the tasks inside the Foreach Loop Container will not be executed

    if the For Each File enumerator did not find any files that matched the file pattern

    or the specified directory was empty.

    So, we could put File System Task inside the Foreach Loop Container.




    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by minhalraffat Wednesday, October 9, 2019 6:17 PM
    Wednesday, October 9, 2019 8:00 AM
  • Hi Minhal, 

    May I know where do you use the File System Task?

    According to my test, the tasks inside the Foreach Loop Container will not be executed

    if the For Each File enumerator did not find any files that matched the file pattern

    or the specified directory was empty.

    So, we could put File System Task inside the Foreach Loop Container.




    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Hi Mona,

    It works. Thanks for your help.

    Thanks,

    Minhal

    Wednesday, October 9, 2019 6:18 PM