Exception has been thrown by the target of an invocation - While executing a .dtsx file in Visual studio

3
votes

I have developed an ssis package and i have a script which fetches few text command from a .txt file , stores those in variables and i use those variables to see what to do next and which dft to execute.

When i go to the .dtsx file--> right click--> execute -->i get the error message saying "Exception has been thrown by the target of an invocation."

enter image description here

However when i stop running the package and try to re execute it it runs successfully.

I tried to deploy this to the Integration Services Catalogs and it throws the same error which i see in the report files

Script task error: exception has been thrown by the target of an invocation

varERMLoadTxt hold the text file path which stream reader reads 

public void Main()
        {
            // TODO: Add your code here
            string path = Dts.Variables["User::varERMLoadTxt"].Value.ToString();
            using (StreamReader sr = File.OpenText(path))
            {
                string line = File.ReadAllText(path);
                string[] lines = line.Split(',');
                if(lines[0].Equals("load", StringComparison.CurrentCultureIgnoreCase))
                Dts.Variables["User::varIsLoad"].Value = true;
                else if (lines[0].Equals("update", StringComparison.CurrentCultureIgnoreCase))
                    Dts.Variables["User::varIsUpdate"].Value = true;
                Dts.Variables["User::varCommand"].Value = lines[0].ToString();
                Dts.Variables["User::varAnalysisDate"].Value = lines[1].ToString();
                sr.Close();



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

enum ScriptResults
{
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
1
c#sql-serverssisetlscript-task
Just set a break point in your script, run your package and step through it to see where exactly what's going on. – Filburt
Did you already check you set all variables you're using to be accessible by your Script Task? – Filburt
yes everything is accessible .. because one time it runs fine in Visual studio and one time it gives this error.. – TheSacredKiller
the main problem is its very unpredictable of when its failing..i have tried three times now with break point but it passed ..i am so confused of what to do – TheSacredKiller
Have a look at this answer - stackoverflow.com/a/52653538/10376537 shows how to wrap your code in a try-catch so it returns back to ssis a more meaningful error message. – Tim Mylott

1 Answers

1
votes

I have 3 suggestions:

  1. Use a try ... catch block and Dts.FireError method to read the real exception:
  1. Check if the file exists before opening it
  2. Check that variables names are correct (note that variable names are case sensitive)

The whole code should looks like

public void Main()
        {
        try{    

                string path = Dts.Variables["User::varERMLoadTxt"].Value.ToString();
            
        if (File.Exists(path))
        {
            using (StreamReader sr = File.OpenText(path))
                {
                string line = File.ReadAllText(path);
                    string[] lines = line.Split(',');
    
                    if(lines[0].Equals("load", StringComparison.CurrentCultureIgnoreCase))
                        Dts.Variables["User::varIsLoad"].Value = true;
                else if (lines[0].Equals("update", StringComparison.CurrentCultureIgnoreCase))
                    Dts.Variables["User::varIsUpdate"].Value = true;
                
            Dts.Variables["User::varCommand"].Value = lines[0].ToString();
                    Dts.Variables["User::varAnalysisDate"].Value = lines[1].ToString();
                    sr.Close();
                     }
            }   

            Dts.TaskResult = (int)ScriptResults.Success;    

        }catch(Exception ex){

            Dts.FireError(0,"An error occured", ex.Message,String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
 
            }

        }

enum ScriptResults
{
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};