Exception has been thrown by the target of an invocation.

Question

Exception has been thrown by the target of an invocation.

yuvipoy

SSChampion

Points: 10469
More actions
April 22, 2020 at 6:23 am

#3745214

Hi All,
Getting the error as "Exception has been thrown by the target of an invocation." Can some one advise on the issue.
There are two script tasks are there in my package.
One is to zip the files in the share path and second one is to move it to another location (say archive location).
Mostly getting the error when moving to Archive location (this is as per the error steps that was enabled)
I am not getting the error message daily, but getting say some 5 days in a week. this is happening from quite long time.
Not sure what might be the issue. Why it is not happening daily or why it is happening sometimes. not sure what is wrong.
Version of visual studio is 2017 in which the package is created.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

palandri Old Hand Points: 387 More actions · Answer 1

In my experience, this is a general purpose error that may hide the underlying error.

Looking back thru my notes, I once encountered this in a script task method the did not have a try catch block. After adding that, I was found that the underlying error was that my code referenced a variable that was not found.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

As suggested above, this is a generic error. Improving the error-checking/reporting code in your package is a very good idea.

Also, if you check the All Executions report for the package, there should be other error messages there which will be more specific.

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

TangoVictor SSChampion Points: 10975 More actions · Answer 3

As Phil suggested you should have try/catch/finally blocks in your code and be logging these operations to maybe a text file.

add a log file to your process will save you time in the future, that message you get from ssis is only the last message in the chain and useless.

Here's a pattern i would suggest:

using system.io;

// define log file
try
{
// open log file
//write to text file process starting

//all your code

}

catch (exception ex)
{
// write the error to your file include ex.message
}

finally
{
// write the process complete message
// close the file
}

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Why use log files, when you can log natively to SSISDB, as described here? Much easier, in my opinion, to keep all of the logging info in one place.

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

palandri Old Hand Points: 387 More actions · Answer 5

For some imports, I use a text log file as transaction report, showing basic info about rows added or updated in the destination tables. Info about errors, like the subject of this thread, are written to the same file.

TangoVictor SSChampion Points: 10975 More actions · Answer 6

Yeah i like this approach, i never used the dts.events, going to keep this one in mind for future stuff.

I just did a project and needed the info to be visible to the helpdesk staff that is why i used the log file. It wasn't intended to just catch errors it was more of an operational log but does also log errors.

yuvipoy SSChampion Points: 10469 More actions · Answer 7

Below are the following steps involved in the task execution

OnPreExecute

OnPreValidate

OnPostValidate

OnPostExecute

Got error on "OnPostExecute" step ,

Currently it is in production for years now, for past 6 months we are getting the error. Is there anyother way to trace without altering the code. It is occurring only in Prod environment , not in the lower environment.

My question is why it is not happening regularly .

Why it is happening sometime

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

Are you saying that the only error for this package, as appears in the All Executions Report, is this one:

'Exception has been thrown by the target of an invocation'

?

That would be unusual, as the SSISDB logging is usually rather verbose.

This reply was modified 4 years, 7 months ago by Phil Parkin.
This reply was modified 4 years, 7 months ago by Phil Parkin. Reason: Typo fixing

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

yuvipoy SSChampion Points: 10469 More actions · Answer 9

Nope .

While seeing the operation log found that it is getting failed on "OnPostExecute"step.

We have 3 retry option for that package. Interval between retries are 10 mins.

On 3rd retry sometimes it get success-ed, sometimes i was forced to restart the package because of the failure in 3rd time as well, package is been run through SQL Server agent job.

First time retry

OnPreExecute - Success

OnPreValidate - Success

OnPostValidate - Success

OnPostExecute -Exception has been thrown by the target of an invocation

Second time retry

OnPreExecute - Success

OnPreValidate - Success

OnPostValidate - Success

OnPostExecute -Exception has been thrown by the target of an invocation

Third time retry

OnPreExecute - Success

OnPreValidate - Success

OnPostValidate - Success

OnPostExecute - Success

Thanks!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 10

"While seeing the operation log found that it is getting failed on "OnPostExecute"step."

Please describe what this 'operation log' is, thanks.

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

yuvipoy SSChampion Points: 10469 More actions · Answer 11

Have used SSISDB as suggested.

Select * from [SSISDB].catalog.operation_messages where message_type in (130,120)

Message_type is seen below.

https://docs.microsoft.com/en-us/sql/integration-services/system-views/catalog-operation-messages-ssisdb-database?view=sql-server-ver15

message_type message_source_type message

130 40 Script Task Name:The task, 'Script Task Name', failed.

120 40 Script Task Name:Error: Exception has been thrown by the target of an invocation.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 12

Please read this article.

It's a lot easier to view the run logs from the All Executions report than by querying SSISDB directly.

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

yuvipoy SSChampion Points: 10469 More actions · Answer 13

Yes. Based on the SSISDB monitor (the link below)only i got operation_message table.

https://docs.microsoft.com/en-us/sql/integration-services/view-reports-for-the-integration-services-server?view=sql-server-2014

I was still wondering how and what base i am getting those errors. There were no issue related to the error found in windows event viewer as well.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 14

I rarely use absolutes, but I feel comfortable using one here.

Given the generic nature of that error message, no one here is going to be able to solve this for you unless you are able to dig deeper – by increasing logging information, improving error handling, reproducing the issue in debug mode, ...

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