Script Task Error: Exception has been thrown by the target of an invocation

• DinoRS

  SSCrazy

  Points: 2683

  More actions

  September 7, 2018 at 5:51 am

  #372352

  Hi Folks,

  I have this pretty little script component which does what it is supposed to do as long as I run the Package through BIDS. When it runs from BIDS all I get to see is some unused UA Output columns not being used, that's it.

  As soon as I build the SSIS Package and attempt to run it, the script component fails.

  Here are the basics of the Script Task:

  ReadOnlyVariables - User::VarFolderPath
  ReadWriteVariables - USER::VarFileName

  #region Namespaces

  using System;

  using System.Data;

  using Microsoft.SqlServer.Dts.Runtime;

  using System.Windows.Forms;

  #endregion

  namespace ST_4185452d2953477499a1d2a6c1aa2345

  {

  [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

  public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

  {

  public void Main()

  {

  string[] files = System.IO.Directory.GetFiles(Dts.Variables["User::VarFolderPath"].Value.ToString());

  System.IO.FileInfo finf;

  DateTime lastDate = new DateTime();

  string lastFile = string.Empty;

  foreach (string f in files)

  {

  finf = new System.IO.FileInfo(f);

  if (finf.CreationTime > lastDate)

  {

  lastDate = finf.CreationTime;

  lastFile = f;

  }

  }

  Dts.Variables["User::VarFileName"].Value = lastFile;

  Dts.TaskResult = (int)ScriptResults.Success;

  }

  #region

  ScriptResults declaration

  enum ScriptResults

  {

  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

  };

  #endregion

  }

  The User::VarFolderPath is a static Network location and the User::VarFileName get's the full Network path including the filename, this is supposed to be called in a flat-file connection manager to get the most recent file based on this variable.

  unfortunately it doesn't work out as expected, first it runs for several minutes (compared to 8 seconds for the whole Project) before it decides to throw in some error Messages.

  These look like this: <Package Name>: Error: The Expression for variable "VarFileName" failed Evaluation. There was an error in the expression

  Now I'm completely lost because I have no clue about C# and things, at least SQL Server did provide a dump - which I don't really understand. Can anyone explain to me what's going wrong?

  The Package is created with VS2010 and runs on SQL Server 2012.

• DinoRS

  SSCrazy

  Points: 2683

  More actions

  September 10, 2018 at 6:10 am

  #2005028

  I solved my own issue with this by having the SQL Server Service Account run as a Domain Account or at least have an execution account with permissions on the share.