locked
Problem adding config file to agent job step to execute SSIS package RRS feed

  • Question

  • Greetings, have an issue adding a config file through ssms on SQL 2014. I am moving numerous 2008 R2 file based packages to a new server. I have copied about 100 packages over, scripting off the jobs from the old server, making required changes to the configs and packages to get them to run on the new 2014 server again using the file based packages not SSISDB yet. The issue I have hit with 1 package is I needed to rewrite this, i also renamed it to remove the spaces and was going to manually set up the job as I have done many times on the older servers. I added the step and browsed to the "ssis packages" folder, which would be on my machine since I was setting it up remotely and it worked fine. Then I tried to add config files which would need to be on the server and they are but the explorer window does not show any folders on the server and there are about 100. I tried remoting in to the server to do this but that doesn't work either. What am I missing here as to why I cant browse to the config files? I can simply script the job from the old server and edit the job but this should work? I tried SSMS 17 and SSMS 18 but neither would allow me to browse to the config files.

    any ideas appreciated. See pic below of what I am seeing when I try to add a config file and the explorer window below shows the folder on the server.


    • Edited by Abeljdang Tuesday, February 11, 2020 9:32 PM picture
    Tuesday, February 11, 2020 9:30 PM

Answers

  • so the issue was, the sql server service account did not have permissions to the folder, only to the share. The sql server agent account had full permissions as did i, but when you are adding a job step it must use the sql server service accounts access to browse the folders. 
    • Marked as answer by Abeljdang Wednesday, February 12, 2020 4:33 PM
    Wednesday, February 12, 2020 4:33 PM

All replies

  • Hi Abeljdang,

    May I know if the user that execute the package has the permission to access the folder?

    The following link will be helpful:

      How to Manage SSIS Configuration and SQL Agent Job Setup to Read From Multiple Configuration Files


    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

    Wednesday, February 12, 2020 9:23 AM
  • yes I am the user, I am local admin on the machine and sa on the sql server. the link doesnt apply to the issue, I can add a configuration to a package being executed in an agent job on other servers just  not this one as it appears. The issue is I can't seem to "see" any of the folders under the parent folder that contains the SSIS packages and SSIS configurations when I click "add" to add a config file. I see the directory but when I try to expand the folder containing the packages and configuration files, it appears empty as shown above.
    Wednesday, February 12, 2020 1:50 PM
  • so the issue was, the sql server service account did not have permissions to the folder, only to the share. The sql server agent account had full permissions as did i, but when you are adding a job step it must use the sql server service accounts access to browse the folders. 
    • Marked as answer by Abeljdang Wednesday, February 12, 2020 4:33 PM
    Wednesday, February 12, 2020 4:33 PM