Solved

ssis script task throws error when run from sql agent job

Posted on 2013-11-14
11
5,261 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
  • 7
  • 4
11 Comments
 

Author Comment

by:patd1
Comment Utility
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
Comment Utility
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
Comment Utility

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
 
LVL 44

Expert Comment

by:Rainer Jeschor
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

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

Thanks.
0
 

Author Comment

by:patd1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
both answers together provide complete solution.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server  2012 Release with lots of Enhancements in Database Engine functions, SSIS, SSRS and some of new services like Data Quality Server and Master Data Service. Of particular interest, and the focus of this Article is SSIS. So, time to elab…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now