Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

ssis script task throws error when run from sql agent job Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 10:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 12:04 PM
Points: 33, Visits: 113
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
Post #1514393
Posted Thursday, November 14, 2013 11:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:42 AM
Points: 5,317, Visits: 12,357
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1514424
Posted Thursday, November 14, 2013 12:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 12:04 PM
Points: 33, Visits: 113
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
Post #1514429
Posted Thursday, November 14, 2013 12:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:42 AM
Points: 5,317, Visits: 12,357
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1514442
Posted Thursday, November 14, 2013 12:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:42 AM
Points: 5,317, Visits: 12,357
The proxy account also needs to be associated with the SSIS subsystem, I think.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1514445
Posted Thursday, November 14, 2013 12:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 12:04 PM
Points: 33, Visits: 113
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
Post #1514448
Posted Thursday, November 14, 2013 1:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:42 AM
Points: 5,317, Visits: 12,357
Have a look here and see whether it helps.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1514457
Posted Thursday, November 14, 2013 1:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 12:04 PM
Points: 33, Visits: 113
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
Post #1514466
Posted Thursday, November 14, 2013 1:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 12:04 PM
Points: 33, Visits: 113
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
Post #1514467
Posted Thursday, November 14, 2013 1:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:42 AM
Points: 5,317, Visits: 12,357
Does your SQL Agent proxy credential have access to the file? Is it a domain account?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1514473
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse