CODEWITHSUNDEEP
c#ssisscript-task
perplexedDev
perplexedDev

Reputation: 835

"Runtime error Exception has been thrown by the target of an invocation" from Script task

I have a SSIS package with a script task, I get the following error when i try to run it in my local system. It works fine for my collegues as well as in production. However, I am not able to run it locally, to test. I keep a debug point in the main method, but it is never reached, I get the error before it goes to main method.

enter image description here

DTS Script Task has encountered an exception in user code:
Project Name: ST_...
Exception has been thrown by the target of an invocation.

   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

I am using VS 2010, .Net framework 4.5.

The script task does compile. I get the following messages SSIS package "..\Test.dtsx" starting. Error: 0x1 at Test: Exception has been thrown by the target of an invocation. Task failed: Test SSIS package "..\Test.dtsx" finished: Success. The program '[2552] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

The following is the code:

public void Main()
{
try
{
 LogMessages("Update Bug package execution started at :: " + DateTime.Now.ToLongTimeString());
 LogMessages("Loading package configuration values to local variables.");

strDBConn = Dts.Variables["User::DBConnection"] != null ? Dts.Variables["User::DBConnection"].Value.ToString() : string.Empty;
strTPCUrl = Dts.Variables["User::TPCUrl"] != null ? Dts.Variables["User::TPCUrl"].Value.ToString() : string.Empty;

TfsTeamProjectCollection objTPC = new TfsTeamProjectCollection(new Uri(strTPCUrl));
WorkItemStore objWIS = new WorkItemStore(objTPC);
WorkItemCollection objWIC = objWIS.Query("SELECT...");

foreach (WorkItem wi in objWIC)
{
}

}
catch(Exception ex)
{
}

When I commented the code from TfsTeamProjectCollection objTPC = new TfsTeamProjectCollection(new Uri(strTPCUrl)); The script executes successfully. However, if i keep TfsTeamProjectCollection objTPC = new TfsTeamProjectCollection(new Uri(strTPCUrl)); and comment the rest, i get the exception. I do have access to the URL.

I am using Microsoft.TeamFoundation.Client.dll and Microsoft.TeamFoundation.WorkItemTracking.Client.dll, in my script task. However the dll version in the package is 10.0, and the version of the dll in my GAC is 12.0. Would that cause a problem?

Upvotes: 18

Views: 103878

Answers (10)

questionto42
questionto42

Reputation: 9502

I had a blocked mail address in the loop. I found this with a try/catch block around the C# Code in SSIS. With a breakpoint in the catch block, I could see the error "e" in the "Autos" window, see Is there an easier way to stop at a breakpoint only when an error happens? - Stack Overflow.

-       e   {"Transaction failed. The server response was: 5.7.1 
<[email protected]>: 
Recipient address rejected: 
[email protected] is locked"}    
System.Exception {System.Net.Mail.SmtpException}
+       Data    {System.Collections.ListDictionaryInternal} System.Collections.IDictionary {System.Collections.ListDictionaryInternal}

Upvotes: 0

efel1010
efel1010

Reputation: 13

For me what fixed the issue was updating the string parameter I passed to the script. it was missing "" at the end of the path (i.e. "e:\arcive" - needed to add "" at the end)

Upvotes: 0

Daniel Jonsson
Daniel Jonsson

Reputation: 3883

My problem was that I, in the script task, tried to fetch data like this:

public void Main()
{
    using (var connection = Dts.Connections["localhost.Test"].AcquireConnection(Dts.Transaction) as SqlConnection)
    {
        connection.Open();

        var command = new SqlCommand("select * from Table;", connection);
        var reader = command.ExecuteReader();
        while (reader.Read())
        {
            MessageBox.Show($"{reader[0]} {reader[1]} {reader[2]}");
        }
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

However, my connection is of the type OLE DB, and therefore I needed to connect to it like this instead:

public void Main()
{
    var connectionString = Dts.Connections["localhost.Test"].ConnectionString;
    using (var connection = new OleDbConnection(connectionString))
    {
        connection.Open();

        var command = new OleDbCommand("select * Table;", connection);
        var reader = command.ExecuteReader();
        while (reader.Read())
        {
            MessageBox.Show($"{reader[0]} {reader[1]} {reader[2]}");
        }
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

Notice that I'm using OleDbConnection here.

Upvotes: 0

Dodecaphone
Dodecaphone

Reputation: 202

After not having any luck with the other answers here, I finally found that in my package, the problem was that I had created a new variable but not carried its name across into my new copy of the C# script. The variables were the ones to be used as connection string expressions. So it was ultimately a matter of changing:

Dts.Variables["Exists"].Value = File.Exists(Dts.Variables["OldSSISPackageVariableName"].Value.ToString());

to:

Dts.Variables["Exists"].Value = File.Exists(Dts.Variables["NewSSISPackageVariableName"].Value.ToString());

Once I kept them in sync, it worked fine.

Upvotes: 1

casenonsensitive
casenonsensitive

Reputation: 950

In my case it was missing DLLs or not having the correct version installed on the server.

Locally all tests were fine but on the server the error message Runtime error Exception has been thrown by the target of an invocation kept popping up.

No exception handler would be able to catch that error - the code in the script task would not even be executed, as soon as a DLL would be needed, that is not in the assembly cache on the server (it could happen on a local machine as well, with the same error).

The difficulty here is finding out what is missing and then either update the references to the correct version or install the missing DLL in the assembly cache with gacutil. The way I approached the debugging was to remove parts of the code in the script task until that error wouldn't appear, then analyze the missing part for references.

Upvotes: 3

Nicholas Emond
Nicholas Emond

Reputation: 31

I fixed this error by changing the TargetServerVersion of the SSIS Project.

Integration Services Project Property Pages

Upvotes: 3

dyslexicanaboko
dyslexicanaboko

Reputation: 4275

This is just a different situation and not intended to be the end all be all solution for everyone.

When I was installing my DLLs into the GAC I forgot to run my script as Administrator and the script ran silently without error as though it was working.

I felt really dumb when I realized that's what I did wrong. Hopefully this can help prevent other people from wasting time on something so silly.

For reference this is what I use for installing my DLLs into the GAC and I modified it to tell me when I am not running it as Administrator now:

#https://superuser.com/questions/749243/detect-if-powershell-is-running-as-administrator
$isAdmin = ([Security.Principal.WindowsPrincipal] `
  [Security.Principal.WindowsIdentity]::GetCurrent() `
).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)

if($isAdmin -eq $false)
{
    Write-Host "You have to run this script as Administrator or it just won't work!" -ForegroundColor Red

    return;
}

$strDllPath = "C:\PathToYourDllsHere\"

#Note that you should be running PowerShell as an Administrator
[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")            
$publish = New-Object System.EnterpriseServices.Internal.Publish            


$arr = @(
    "YourDLL01.dll",
    "YourDLL02.dll",
    "YourDLL03.dll"
)

get-date

foreach($d in $arr)
{
    $p = ($strDllPath + $d);

    $p

    $publish.GacInstall($p);
}

#If installing into the GAC on a server hosting web applications in IIS, you need to restart IIS for the applications to pick up the change.
#Uncomment the next line if necessary...
#iisreset

Credit for how to determine if your PowerShell script is running in Admin mode or not: https://superuser.com/questions/749243/detect-if-powershell-is-running-as-administrator

Upvotes: 2

renaissanceMan
renaissanceMan

Reputation: 433

I got this error message when I referred to a passed ssis variable in Dts.Variables["User::xxxx].Value(); where xxxx did not exist and was not passed from the calling program. It was a simple Console.Writeline referring to a passed variable that didn't exist.

Upvotes: 3

Ylli Prifti
Ylli Prifti

Reputation: 373

I had the same Problem (i.e. the same error code Error: 0x1 ...).

The issue was with some of the libraries referenced from a missing folder.

Removing the references and adding them back from the correct path fixed the issue.

The Microsoft Reference (https://msdn.microsoft.com/en-us/library/ms345164.aspx) related to the Error code is very generic and doesn't help you much. However, reading other articles it is quite likely it indicates an unknown failure reason to run the Script Task.

  • Hexadecimal code: 0x1
  • Decimal Code: 1
  • Symbolic Name: DTS_MSG_CATEGORY_SERVICE_CONTROL
  • Description: Incorrect function.

Upvotes: 3

perplexedDev
perplexedDev

Reputation: 835

Its fixed, when added reference to dll version 12.0.0 and changed Target Framework to .Net Framework 4.5

Upvotes: 0

Related Questions