SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ssis script task throws error when run from sql agent job


ssis script task throws error when run from sql agent job

Author
Message
ForumUser3
ForumUser3
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 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 UserCool
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54136 Visits: 21211
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ForumUser3
ForumUser3
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 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 UserCool
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54136 Visits: 21211
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54136 Visits: 21211
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ForumUser3
ForumUser3
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 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 UserCool
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54136 Visits: 21211
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ForumUser3
ForumUser3
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 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 UserCool
ForumUser3
ForumUser3
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 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 UserCool
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54136 Visits: 21211
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search