Sql-server – Error While executing a SSIS package which contains a script task through SQL Server Agent job

sql serverssis

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,

Best Answer

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.

Related Topic