SSIS Package run from Visual Studio but fails from SQL Server Agent with the Error: Exception has been thrown by the target of an Invocation.
-
HI,
I have an SSIS Package which goes to a website using http connection and gets a zip file and loads to the shared drive.
I am using script task to do this.
My package is running fine when I run it from Visual Studio but it is failing when I run from SQL Server Agent.
Below is the error I am getting.
FYI.. Below is the script task code I was using.
The Path I am storing the Zip file is: \\Servername\DBSnapshots\SQL2008\NorthDakota\MyProductFile\well_index.zip
Can you please guide me on this?
-
First thing first...Did you check permissions for the Agent Service account (Or proxy account if that is how the Job step is set to run as)?
- kashyap4007 - Tuesday, September 4, 2018 11:07 AMHI,
I have an SSIS Package which goes to a website using http connection and gets a zip file and loads to the shared drive.
I am using script task to do this.
My package is running fine when I run it from Visual Studio but it is failing when I run from SQL Server Agent.
Below is the error I am getting.FYI.. Below is the script task code I was using.
The Path I am storing the Zip file is: \\Servername\DBSnapshots\SQL2008\NorthDakota\MyProductFile\well_index.zip
Can you please guide me on this?
to be sure what your issue is (permissions, invalid uri etc...) please post the entire error message you are getting in column 3 of the OnError event.
-
Srikanth,
Permission is not the issue because I am able to run other packages and I am the Admin on the server.Smendle,
Attached is the full error message I exported. - kashyap4007 - Tuesday, September 4, 2018 12:22 PM
that is the same error message you show. The entire error message should in the output file for the package at runtime.
- kashyap4007 - Tuesday, September 4, 2018 12:22 PM
When the agent runs jobs, it often runs using a completely different account with (often) completely different permissions and sometimes also the environment variables are different (like "path", which is the list of directory paths to search for executables and such). You should check to make sure in your case that the account that the agent runs under (typically a service account of some sorts) has the permissions needed. If the agent actually runs with a different account then it doesn't matter what YOUR permissions are because that's not the set of permissions in effect when the job actually runs.
Hope that makes sense!
-
Patrick,
I am using the SQL Server Agent Service Account to run my job.
As I told, I am able to run a different step(In the same job) which loads data from Database to Database and also Database to the same shared path( Flat file Destination) which I am using above.
Below is the code I am using in my script task and I noticed that the first step is executing ( //TODO: Add you Code here) correctly and then it is jumping to the catch part and not executing the other steps.try
{
// TODO: Add your code here
bool fireAgain = true;
Dts.Events.FireInformation(0, "Download File", "Start downloading " + Dts.Variables["DownloadURL"].Value.ToString(), string.Empty, 0, ref fireAgain);// Create a webclient to download a file
WebClient mySSISWebClient = new WebClient();//Download file and use the Flat File Connectionstring (D:\SourceFiles\Products.csv)
// to save the file (and replace the existing file)
mySSISWebClient.DownloadFile(Dts.Variables["DownloadURL"].Value.ToString(), Dts.Connections["myProductFile"].ConnectionString);// Logging end of download
Dts.Events.FireInformation(0, "Download File", "Finished downloading " + Dts.Connections["myProductFile"].ConnectionString, string.Empty, 0, ref fireAgain);// Quit Script Task succesful
Dts.TaskResult = (int)ScriptResults.Success;
Dts.Variables["ScriptTaskSucessOrFailure"].Value = A;
//Dts.TaskResult = (int)ScriptResults.Success;
//Dts.Variables["User:ScriptTaskSuccessOrFailue"].Value = A.ToString();
}
catch (Exception Ex)
{
Dts.TaskResult = (int)ScriptResults.Failure;I noticed that seeing the job log as shown below.
-
I am using the SQL Server Agent Service Account to run my job.
I'm thinking that if you yourself can run the job but the sql server agent service account cannot, then the job is ok but the agent service account lacks permissions. Its hard to tell however as the exact error doesn't seem to get listed in your pictures.
I've seen some installations for instance where the sql server agent service account lacks network privileges because its a local service account. Other times the service account has differing permissions. I've also seen service accounts that lack the correct value in the "path" environment variable so that it can't find executables or other pieces of the puzzle that it needs to run.
-
on your catch block output the error exception (and inner exception if any) to your log file and see exactly what the error is.
One thing that is likely to be happening is that the server account does either not have internet access or no access to the website file
-
Frederico,
I think you are right. The Server on which the Job is running doesn't have Internet access.
I did raise an SR to grant me access and when I get that I will try to see how it goes.
Thank you all for the help and will update here as soon as the Server is granted Internet access.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply