CODEWITHSUNDEEP
c#sql-serverssisetlscript-task
TheSacredKiller
TheSacredKiller

Reputation: 141

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

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
};

Upvotes: 3

Views: 5941

Answers (1)

Hadi
Hadi

Reputation: 37368

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
};

Upvotes: 1

Related Questions