Solved

ssis script task throws error when run from sql agent job

Posted on 2013-11-14
11
6,324 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 500 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

689 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