?
Solved

ssis script task throws error when run from sql agent job

Posted on 2013-11-14
11
Medium Priority
?
6,608 Views
Last Modified: 2016-02-11
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.
0
Comment
Question by:patd1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 

Author Comment

by:patd1
ID: 39651040
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
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 2000 total points
ID: 39651107
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
 

Author Comment

by:patd1
ID: 39652327

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39652482
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
 

Author Comment

by:patd1
ID: 39656256
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
 

Author Comment

by:patd1
ID: 39659996
I am loosing hope, please respond. I am sure many people are using this in their ETL.

Thanks.
0
 

Author Comment

by:patd1
ID: 39662380
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
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39667061
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
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39667067
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
 

Assisted Solution

by:patd1
patd1 earned 0 total points
ID: 39667263
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
 

Author Closing Comment

by:patd1
ID: 39677074
both answers together provide complete solution.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question