November 14, 2013 at 10:45 am
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;
}
Thanks for your help.
Forum User:cool:
November 14, 2013 at 11:49 am
Are there any other errors? The one you posted doesn't really help that much, unfortunately.
Are you saying that all packages containing scripts fail when run under SQL Agent, or just this one?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 14, 2013 at 12:01 pm
Phil Parkin (11/14/2013)
Are there any other errors? The one you posted doesn't really help that much, unfortunately.Are you saying that all packages containing scripts fail when run under SQL Agent, or just this one?
No, there are no other errors. Just this one Error:
Source: Set FS File Parameters Script Task Description: Exception has been thrown by the target of an invocation.
None of the other packages with script task are failing when run by the sql agent under proxy account. Only this one fails, and the difference is this one uses System.IO; and the related code:
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;
}
}
Forum User:cool:
November 14, 2013 at 12:23 pm
OK, maybe you could put some more sophisticated error-handling in the script in an effort to work out exactly which row is causing the error. I don't remember having any System.IO issues in the past, so I'm not sure that I can be of much help.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 14, 2013 at 12:29 pm
The proxy account also needs to be associated with the SSIS subsystem, I think.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 14, 2013 at 12:39 pm
Phil Parkin (11/14/2013)
The proxy account also needs to be associated with the SSIS subsystem, I think.
The proxy account is associated with SSIS Package Execution
In the SSMS, I expand SQL Server Agent, then expand Proxies, then expand SSIS Package Execution -> I see the proxy account under this.
Is there anything else that needs to be done. Please explain what/how.
Thanks for your help in advance.
Forum User:cool:
November 14, 2013 at 1:27 pm
The only difference I see is that script you directed me to uses @subsystem=N'DTS'
while my job script uses @subsystem=N'SSIS'
I changed it to use DTS, but it didn't work. I still creates the job with @subsystem=N'SSIS'
The step name "Run package" in my sql job is using Type = "SQL server Integration Services Package"
Forum User:cool:
November 14, 2013 at 1:30 pm
It this problem because the proxy account does not have access to system.IO (dll) ?
The file and folder is set to have everyone full control.
Forum User:cool:
November 14, 2013 at 1:59 pm
Does your SQL Agent proxy credential have access to the file? Is it a domain account?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 15, 2013 at 5:17 am
The file and folder has everyone full control set.
The account is MyDomain\MyProxy
Forum User:cool:
November 15, 2013 at 6:18 am
yes, my file and folder has everyone 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 {Fab1234D-1588-4BA3-92D4-123431235f3r} and APPID {34B12342-123D-4824-B42E-1234AE61BB05} to the user MyDomain\MyAcct SID (S-1-5-21-1234565741-12412345839-921234558-432120) from address LocalHost (Using qwer). This security permission can be modified using the Component Services administrative tool.
How/What do I need to modify using Component Services administrative tool?
I opened Component Services administrative tool, but did not find an application with id {Fab1234D-1588-4BA3-92D4-123431235f3r} or {34B12342-123D-4824-B42E-1234AE61BB05} under DCOM Config.
Thanks.
Forum User:cool:
November 15, 2013 at 6:20 am
Yes, 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 {FDC1234D-1588-4BA3-92D4-42C12345D7D} and APPID {37g1234-693D-4824-B42E-1234AE61df05} to the user MyDomain\MyAcct SID (S-1-5-21-7654321-1241591234-123409458-121234) 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 {FDC1234D-1588-4BA3-92D4-42C12345D7D} or {37g1234-693D-4824-B42E-1234AE61df05} under DCOM Config.
How/what do I need to modify using Component Services administrative tool?
Forum User:cool:
November 15, 2013 at 6:38 am
A quick search of the registry will tell you that class id is for the DTS server, so another quick look under the DCOM Config section of Component Services and you will find
Microsoft SQL Server Integration Services 11.0
If you open the properties of that, you will find the AppId from your error message - then you know you have found it, so go ahead and grant activation to the user account in your proxy settings.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 18, 2013 at 5:14 am
I did the following:
Opened Component Services,Expand Component Service->My Computer->DCOM Config, Locate “Microsoft SQL Server Integration Services 11.0” Rt click and select “Properties” Select the “Security” tab, In Launch and Activate Permissions, then “Edit” Grant “Local Launch”, “Local Activation” permissions for SQL Agent Service / proxy account MyDomain\MyAcct, Restarted SQL Agent and tried again.
Now the error in the event viewer is gone, but I still get the error in sysssislog: Source: Set FS File Parameters Script Task: Exception has been thrown by the target of an invocation.
What else needs to be changed?
Forum User:cool:
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply