ssis script task throws error when run from sql agent job

When I run this package from Visual Studio, it works fine, but when I run it from the sql agent job (SQL Server 2012 sp1) it throws error on the Script Task . I am running it under a proxy account in the sql agent job.

Error:  Source: Set FS File Parameters Script Task      Description: Exception has been thrown by the target of an invocation.  

The proxy account is configured for the following subsystems:
ActiveX Script
SQL server Analysis Services Command
SQL server Analysis Services Querry
SQL server Analysis Services Package
PowerShell

I guess it is a problem with the proxy account using System.IO,  because all other packages that do not access the file system are running fine, even though they have script tasks. All file path variables have been set up with UNC paths. The folder and files have everyone full control configuration.


How do I set it up to run from the sql agent job?
How do I check to make sure proxy account has access to file system?

Here is the code in the script task:
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion

public void Main()
		{
            Dts.Variables["User::AS_FileArchivePath"].Value = "";
            Dts.Variables["User::ExcludeProvidersArchivePath"].Value = "";
            Dts.Variables["User::AS_FilePath"].Value = "";
            Dts.Variables["User::ExcludeProvidersFilePath"].Value = "";
            Dts.Variables["User::FeeScheduleFileName"].Value = "";
            Dts.Variables["User::FileArchivePath"].Value = "";


            //get load file name
            String dirPath = Dts.Variables["User::FileDropFolder"].Value.ToString();
            String fileExt = Dts.Variables["User::LoadFileExt"].Value.ToString();
            String FileArchivePath = Dts.Variables["User::FileArchiveFolder"].Value.ToString() + Dts.Variables["User::FileArchiveDateFolder"].Value.ToString();
            String FileName = "";
            String FileType = "";
            int FileSize = 0;
            DirectoryInfo dir = new DirectoryInfo(dirPath);

            foreach (FileInfo file in dir.GetFiles())
            {
                if (file.Extension.Contains(fileExt)
                    && file.Name.StartsWith("DoNotDeleteTemplate") == false
                    && file.Name.Contains("Products") == true
                    && file.Name.Contains("Special") == false
                    && file.Name.Contains("Exclude") == false)
                {
                    FileName = file.Name;
                    FileSize = (int)file.Length;
                    FileType = file.Extension;
                }
            }


            if (FileName != "")
            {
                Dts.Variables["User::FeeScheduleFileName"].Value = FileName;
                Dts.Variables["User::FeeScheduleFileSize"].Value = FileSize;
                Dts.Variables["User::FeeScheduleFileType"].Value = FileType;

                //create archive folder
                bool folderExists = Directory.Exists(FileArchivePath);
                if (!folderExists)
                    Directory.CreateDirectory(FileArchivePath);

                //set full archive path
                Dts.Variables["User::FileArchivePath"].Value = FileArchivePath + "\\" + FileName;


                //set full load file path
                String filePath = Dts.Variables["User::FileDropFolder"].Value.ToString() + FileName;

            }

			Dts.TaskResult = (int)ScriptResults.Success;
		}

Open in new window



Thanks.
patd1Asked:
Who is Participating?
 
Rainer JeschorConnect With a Mentor Commented:
Hi,
this GUID is the DCOM App for "Microsoft SQL Server Integration Services 11.0" (MsDtsServer110).

To fix it please follow these steps:
x Run Dcomcnfg.exe or open Component Services through Startmenu -> Administrative Tools
x Expand Component Service -> My Computer ->DCOM Config
x Locate “Microsoft SQL Server Integration Services 11.0”
x Right click and select “Properties”
x Select the “Security” tab
x In Launch and Activate Permissions, click on “Edit”
x Grant “Local Launch”, “Local Activation” permissions for your SQL Agent Service / proxy account MyDomain\MyAcct

Restart SQL Agent and try again.

HTH
Rainer
0
 
patd1Author Commented:
My folder and files have every one full control.

I checked the event viewer. It is showing the following error: The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {FDC3723D-1588-4BA3-92D4-42C430735D7D} and APPID {83B33982-693D-4824-B42E-7196AE61BB05} to the user MyDomain\MyAcct SID (S-1-5-21-444465741-1241596839-922709458-120520) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.


I opened Component Services administrative tool, but did not find an application id {FDC3723D-1588-4BA3-92D4-42C430735D7D} or {83B33982-693D-4824-B42E-7196AE61BB05} under DCOM Config.

How/what do I need to modify using Component Services administrative tool?
0
 
patd1Author Commented:

Hi,
this GUID is the DCOM App for "Microsoft SQL Server Integration Services 11.0" (MsDtsServer110).

To fix it please follow these steps:
x Run Dcomcnfg.exe or open Component Services through Startmenu -> Administrative Tools
x Expand Component Service -> My Computer ->DCOM Config
x Locate “Microsoft SQL Server Integration Services 11.0”
x Right click and select “Properties”
x Select the “Security” tab
x In Launch and Activate Permissions, click on “Edit”
x Grant “Local Launch”, “Local Activation” permissions for your SQL Agent Service / proxy account MyDomain\MyAcct

Restart SQL Agent and try again.

I checked the AppID for "Microsoft SQL Server Integration Services 11.0" is same as shown in the error message {83B33982-693D-4824-B42E-7196AE61BB05}.
I went ahead and allpied changes as mentioned in the quited text.

Also re-startedand SQL Server Agent and  SQL server Integration services 11.0 , and re-ran my job,

Now there is no error in the event log, but I see the same error in the SQL Job Activity monitor and SSISsysLog table.

Source: Set FS File Parameters Script Task      Description: Exception has been thrown by the target of an invocation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).

What else can I try?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rainer JeschorCommented:
Hi,
to identify the issue, I would simply create a "debug" SSIS package which has just one script task where I would add pretty the same code as above but hard-code any variable content.
Then I would add a lot of log messages:
bool fireAgain = true;
Dts.Events.FireInformation(0, "I am here xxxx", "Proxy debug", string.Empty, 0, ref fireAgain); 
 

Open in new window

and finally I would add an Exception handling in the task code:
... main ...
try
{
...all your debug code goes here ...
}
catch (Exception ex) 
{
Dts.Events.FireInformation(0, ex.ToString() + "---" + ex.InnerException.ToString(), "Proxy debug error", string.Empty, 0, ref fireAgain);
}

Open in new window


This should help to identify the issue.

HTH
Rainer
0
 
patd1Author Commented:
I changed my code as suggested above, but don't see any extra error/logging events.
It still gives me the same error in sysSSISlog and sql agent history log.
Error: Source: Set FS File Parameters Script Task      Description: Exception has been thrown by the target of an invocation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).
Here is my new code:
public void Main()
		{
            bool fireAgain = true;
            Dts.Events.FireInformation(0, "I am here xxxx", "Proxy debug", string.Empty, 0, ref fireAgain); 
           

try
{


            Dts.Variables["User::AS_FileArchivePath"].Value = "";
            Dts.Variables["User::ExcludeProvidersArchivePath"].Value = "";
            Dts.Variables["User::AS_FilePath"].Value = "";
            Dts.Variables["User::ExcludeProvidersFilePath"].Value = "";
            Dts.Variables["User::FeeScheduleFileName"].Value = "";
            Dts.Variables["User::FileArchivePath"].Value = "";


            //get load file name
            String dirPath = Dts.Variables["User::FileDropFolder"].Value.ToString();
            String fileExt = Dts.Variables["User::LoadFileExt"].Value.ToString();
            String FileArchivePath = Dts.Variables["User::FileArchiveFolder"].Value.ToString() + Dts.Variables["User::FileArchiveDateFolder"].Value.ToString();
            String FileName = "";
            String FileType = "";
            int FileSize = 0;
            DirectoryInfo dir = new DirectoryInfo(dirPath);

            foreach (FileInfo file in dir.GetFiles())
            {
                if (file.Extension.Contains(fileExt)
                    && file.Name.StartsWith("DoNotDeleteTemplate") == false
                    && file.Name.Contains("Products") == true
                    && file.Name.Contains("Special") == false
                    && file.Name.Contains("Exclude") == false)
                {
                    FileName = file.Name;
                    FileSize = (int)file.Length;
                    FileType = file.Extension;
                }
            }


            if (FileName != "")
            {
                Dts.Variables["User::FeeScheduleFileName"].Value = FileName;
                Dts.Variables["User::FeeScheduleFileSize"].Value = FileSize;
                Dts.Variables["User::FeeScheduleFileType"].Value = FileType;

                //create archive folder
                bool folderExists = Directory.Exists(FileArchivePath);
                if (!folderExists)
                    Directory.CreateDirectory(FileArchivePath);

                //set full archive path
                Dts.Variables["User::FileArchivePath"].Value = FileArchivePath + "\\" + FileName;


                //set full load file path
                String filePath = Dts.Variables["User::FileDropFolder"].Value.ToString() + FileName; 
            

            }

}
catch (Exception ex) 
{
Dts.Events.FireInformation(0, ex.ToString() + " MyError: " + ex.InnerException.ToString(), "Proxy debug error", string.Empty, 0, ref fireAgain);
}

			Dts.TaskResult = (int)ScriptResults.Success;
		}

Open in new window

0
 
patd1Author Commented:
I am loosing hope, please respond. I am sure many people are using this in their ETL.

Thanks.
0
 
patd1Author Commented:
I changed my try catch block as follows and now I see error "Access to path .... denied", Error message copied below.  I have set everyone full control on the files and folders on E drive. I also copy pasted the path from the error message on windows explorer to check if the path value is correct and it is.

try
{
.......
..........

}
catch (Exception ex)
{

    Dts.Events.FireError(0, "My File Task", ex.Message, String.Empty, 0);
    Dts.TaskResult = (int)ScriptResults.Failure;
}

			Dts.TaskResult = (int)ScriptResults.Success;
		}

Open in new window



Error Message:
Executed as user: MyDomain\MyServiceAcct . Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 7:36:00 AM Error: 2013-11-20 07:36:04.36 Code: 0x00000000 Source: Set FSFileParameters Script Task My File Task Description: Access to the path '\\MyServer\E$\MyFiles\FSP\' is denied. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:36:00 AM Finished: 7:36:04 AM Elapsed: 3.588 seconds. The package execution failed. The step failed.
0
 
Rainer JeschorCommented:
Hi,
sorry - have been really busy in two projects.

Have you tried to logon to the server where the SQL agent is executing the job with the MyServiceAcct ?
What happens if you copy/paste that path (\\MyServer\E$\MyFiles\FSP\) into Windows Explorer?

To access a network share you have to configure two security levels:
- Network Share permission
- File system permissions (NTFS)
Have you checked them both?

HTH
Rainer
0
 
Rainer JeschorCommented:
And in regards to the logging:
sorry I forgot to mention that you should activate the step output into a dedicated log file (configure on step level on the sql agent job)
0
 
patd1Connect With a Mentor Author Commented:
I shared E drive and sub folders for the service account and changed the path to use E instead of E$ (new path value: \\MyServer\E\MyFiles\FSP\).

That solved my problem. Now my package is running from the scheduled job.
Also I think changing the configuration in Dcomcnfg was also necessary.
0
 
patd1Author Commented:
both answers together provide complete solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.