SSIS package fails sometimes and works fine

Kundur Rudresh 1 Reputation point
2022-08-09T14:59:46.467+00:00

We have a scheduled SQL Job which runs the SSIS package and process the files (Hourly Job, and 24 execution's daily). From past few weeks we are seeing minimum 2 failures in a day randomly, post failure also Job executes run fine in next execution cycle i.e = next hour.

Very recently we have upgraded our SSMS to 2019

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 15.0.2000.5 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: [TIME] Error: [DATE TIME] Code: 0x00000005 Source: File count File count Description: Failed to compiled scripts contained in the package. Open the package in SSIS Designer and resolve the compilation errors. End Error Error: [DATE TIME] Code: 0x00000005 Source: File count File count Description: The directory is not empty. (Exception from HRESULT: 0x80070091) End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: [TIME] Finished: [TIME] Elapsed: #### seconds. The package execution failed. The step failed.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,815 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-08-09T15:08:15.493+00:00

    That looks like a user defined error in your script. You should look at the cause of "The directory is not empty." in your script.

    0 comments
  2. Bjoern Peters 8,781 Reputation points
    2022-08-09T15:11:20.46+00:00

    1.) It has nothing to do with your Upgrade of SSMS.
    2.) Your job is missing an error handling ;-)
    The error message in the failure states

    Source: File count File count Description: The directory is not empty

    So you need to check a.) why isn't there a file and/or b.) why do your job not handle this "error."
    Your job fails because it is expecting a file to process, and if there isn't any... the next step will fail...

  3. ZoeHui-MSFT 33,296 Reputation points
    2022-08-10T01:58:28.14+00:00

    Hi @Kundur Rudresh ,

    Failed to compiled scripts contained in the package. Open the package in SSIS Designer and resolve the compilation errors.

    It seems that the script could not be compiled. Open the script editor and look for errors in your code.

    Also make sure the TargetServerVersion match with your SQL Server when you deploy the package.

    Regards，

    Zoe

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments