Error While executing a SSIS package which contains a script task through SQL Server Agent job

Question

I have a SQL Server 2012 SSIS package with a script task along with other tasks [data flow, execute sql tasks ]. When I manually executed the job its completed successfully.

But when I executed the package through SQL Job Agent, it is getting failed with the below error message.

"Error: 2014-05-13 12:45:14.67

Code: 0x00000001

Source: Script Task

Description: Exception has been thrown by the target of an invocation.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 12:45:10 PM

Finished: 12:45:14 PM

Elapsed: 4.353 seconds"

My Script Task is for load data in Text File and add to variable

public void Main()
    {
        // TODO: Add your code here
        StreamReader sr = new StreamReader("E:\\IEREVISIPDKE\\IELOADING.txt");

        string st = sr.ReadToEnd();
        this.Dts.Variables["IE"].Value = st;

        //MessageBox.Show(st);

        Dts.TaskResult = (int)ScriptResults.Success;

Thanks,

Does that local path (E:\\IEREVISIPDKE\\IELOADING.txt) exist in the SQL Server that is running the job? Does the SQL Server agent account have access to that path? — Nick.McDermaid
i found that SQL Server Agent doesn't have access to file path...i follow this thread stackoverflow.com/questions/14742324/… and now it worked! thanks — ian

SqlKindaGuy SqlKindaGuy · Accepted Answer · 2017-08-22T11:35:59

I think you need to add permission to your SQL Job Agent Service account to the specified folder.

My best guess is that when you execute from SSIS, you run as yourself, and you have full access to your folder.

When running SQL Job Agent, it uses the service to access your folder, and this user has no access to your specified folder.

Error While executing a SSIS package which contains a script task through SQL Server Agent job

2 Answers