ssis error when excel was open

Vicki 46 Reputation points
2022-10-11T18:30:15.667+00:00

Hi,

My SQL job got failed because user who leaves the excel file open. I am thinking adding the step into my ssis package to force the file close or kill the process, but not sure how to start with. Does anyone have a script or tell me how to do it?
Thanks

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,462 questions
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 33,386 Reputation points
    2022-10-12T01:42:08.947+00:00

    Hi @Vicki ,

    Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

    You may consider using File System Task to copy the excel file first and then use the copy file as source in your package to avoid the opening error.

    Regards,

    Zoe Hui


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


  2. Olaf Helper 41,011 Reputation points
    2022-10-12T05:19:35.757+00:00

    to force the file close or kill the process,

    If Excel running on a different machine = some client, then it will be difficult to get & kill the process.
    Remote PowerShell could be an option: https://adamtheautomator.com/how-to-kill-a-windows-process/
    But again: Difficult.

    My SQL job got failed because user who leaves the excel file open

    And what is the SSIS job doing, juts reading the Excel file or ...?

    0 comments No comments