locked
Invalid Connection Manager - SQL Server Agent Job RRS feed

  • Question

  • Hello Everyone,

    When I run my package on BIDS it runs without problems, but when I run it as a job on SQL Server Agent, I get an invalid connection manager error. I am using SQL 2008R2. Below is the arror that I’m getting (a few words on the directory path changed for anonymity, otherwise the structure is exactly the same):

    Executed as user: MY-SERVER\SYSTEM. ...n 10.50.6000.34 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  10:23:28  Error: 2017-01-24 10:23:30.45     Code: 0xC001401E     Source: My Package Connection manager "validation_results_destination_table_1"     Description: The file name "\\root\fileshare\Project\Technical\DM\Project files ^MASTER^\Project Plans Further Validations in SSIS\Validation Results\Project_Validation_Results_Table_1_Run_On_24-01-2017 1023hrs.csv" specified in the connection was not valid.

    Can someone please help me what I should do to resolve this.

    Kind regards,


    Mpumelelo

    Tuesday, January 24, 2017 11:29 AM

Answers

  • Hi,

    Go to SQL server configuration manager and make sure that the account that runs the SQL server agent service is an individual account rather than the system account. If you change it to an individuals account (who has ample access on both the SQL server and the file location) and then restart the service then the package will work in a giffy.

    Thanks

    Kuleen

    • Marked as answer by Mpumelelo S Tuesday, January 24, 2017 1:21 PM
    Tuesday, January 24, 2017 11:35 AM
  • The reason is simple. You are running the Job in agent using the account SQL Server Agent Service Account that has not privilege in the network for root fileshare...etc. You must use proxy and credentials. I did write an article on this just a week ago. Have a look, I guess it will resolve your problem:

    https://social.technet.microsoft.com/wiki/contents/articles/36797.sql-credentials-and-proxy-for-agent-job.aspx

    Please mark as answer if this post helped you

     
    • Edited by DIEGOCTN Tuesday, January 24, 2017 1:15 PM
    • Marked as answer by Mpumelelo S Tuesday, January 24, 2017 1:22 PM
    Tuesday, January 24, 2017 1:15 PM

All replies

  • Hi,

    Go to SQL server configuration manager and make sure that the account that runs the SQL server agent service is an individual account rather than the system account. If you change it to an individuals account (who has ample access on both the SQL server and the file location) and then restart the service then the package will work in a giffy.

    Thanks

    Kuleen

    • Marked as answer by Mpumelelo S Tuesday, January 24, 2017 1:21 PM
    Tuesday, January 24, 2017 11:35 AM
  • The reason is simple. You are running the Job in agent using the account SQL Server Agent Service Account that has not privilege in the network for root fileshare...etc. You must use proxy and credentials. I did write an article on this just a week ago. Have a look, I guess it will resolve your problem:

    https://social.technet.microsoft.com/wiki/contents/articles/36797.sql-credentials-and-proxy-for-agent-job.aspx

    Please mark as answer if this post helped you

     
    • Edited by DIEGOCTN Tuesday, January 24, 2017 1:15 PM
    • Marked as answer by Mpumelelo S Tuesday, January 24, 2017 1:22 PM
    Tuesday, January 24, 2017 1:15 PM