locked
Does sqlserveragent account needs full control on a fileshare to modify a excel ? RRS feed

  • Question

  • I have a SSIS package which formats data using c# code in ssis script task .I can run it using sqlserveragent account which has full control permissions on the file share folder in DEV but in QA when I try to run I get error innvocation error at c#\script task.

    The sqlserveragent account has modify permissions set on the file share folder in QA 

    The flow of ssis package is as follows.

    Prep data and dump it in a sql table and then use the table to write data in excel over a oledb connection . Once the data write is done format using c# /script task . 

    SSIS 2014 and SQL server 2014.

    Excel format- .xls





    • Edited by MLTC Wednesday, May 13, 2020 2:35 AM
    Monday, May 11, 2020 11:16 PM

Answers

  • It seems permission issue - please check SQL Server agent have required(full) permissions on QA share folders ?

    http://uk.linkedin.com/in/ramjaddu

    Tuesday, May 12, 2020 9:21 PM

All replies

  • Hi MLTC,

    Could you please share the error messages in detail?

    The following link will be helpful:

      Working with Excel Files with the Script Task

    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

    Tuesday, May 12, 2020 1:37 AM
  • Hi MLTC,

    Could you please share the error messages in detail?

    The following link will be helpful:

      Working with Excel Files with the Script Task

    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

    Exception has been thrown by the target of an invocation. 



    • Edited by MLTC Tuesday, May 12, 2020 9:04 PM
    Tuesday, May 12, 2020 9:04 PM
  • Error in detail

    Executed as user: SQLserverAgent. 

    Microsoft (R) SQL Server Execute Package Utility  Version 12.0.4100.1 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.   

     Started:  4:54:48 PM  Error: 2020-05-12 16:54:52.40     Code: 0x00000001     Source: Format Excel      Description: Exception has been thrown by the target of an invocation.  

    End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:54:48 PM  Finished: 4:54:52 PM  Elapsed:  4.234 seconds.  The package execution failed.  The step failed. 




    • Edited by MLTC Tuesday, May 12, 2020 9:07 PM
    Tuesday, May 12, 2020 9:06 PM
  • It seems permission issue - please check SQL Server agent have required(full) permissions on QA share folders ?

    http://uk.linkedin.com/in/ramjaddu

    Tuesday, May 12, 2020 9:21 PM
  • Hi,

    May I know if you have anything to update?

    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

    Friday, May 15, 2020 10:07 AM
  • Hi,

    May I know if you have anything to update?

    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

    We are still working to get the permissions applied .


    • Edited by MLTC Saturday, May 16, 2020 11:46 PM
    Saturday, May 16, 2020 11:46 PM
  • Hi MLTC,

    Please refer to the following links:

      Giving file system access to the SQLSERVERAGENT virtual account

      Configure File System Permissions for Database Engine Access

    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

    Tuesday, May 19, 2020 6:24 AM
  • Hi MLTC,

    Please refer to the following links:

      Giving file system access to the SQLSERVERAGENT virtual account

      Configure File System Permissions for Database Engine Access

    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

    Thanks the folder has full permissions and hence now this issue is resolved.



    • Edited by MLTC Friday, May 29, 2020 8:14 PM
    Friday, May 29, 2020 8:14 PM