SSIS Script Task doesn't appear to run when scheduled

Question

I have a script task inside SSIS which does not appear to run when the job is triggered from SQL Server Agent. I say does not appear to run because I don't know if it's the SQL Script task or some task preceding it.

The basic goal of this part of the ETL process is to group all files downloaded and process them one set at a time (5 files per set).

enter image description here

The GetFullSourcePath SQL Task outputs an Object data type (Return Type = full result set). Inside the Copy Source Files ForEach Loop Container, I convert that to a string using the SQL Script task and then use a File System task to move the file from Inbound to ToDo for processing.

The SQL Script is a very simple C# operation converting an Object data type to a string. It has 1 input and 1 output variable. However, the output value is never populated which means the next step of the ETL job cannot locate the source file.

 public void Main()
 {
 //Dts.Variables["User::SourceFile"].Value = Dts.Variables["User::FullSourcePath"].ToString();
 object myObjectVar = Dts.Variables["FullSourcePath"].Value;
 string myString = (string)myObjectVar;
 Dts.Variables["SourceFile"].Value = myString;
 Dts.TaskResult = (int)ScriptResults.Success;
 }

When I view the execution report, the script has no errors. I'm using Visual Studio 2015 Enterprise, SSDT BI Preview (December 2015 release), and SQL Server 2014. It works fine if I run the package from VS.

The error I get is can't find "somefile.txt". The read/write variable which is also the output variable of the script task is defaulted to "somefile.txt" in the package variables. This made me assume the script task was not working but equally it could be something else. I'll dig a little deeper in the execution logs to be sure.

Answer 1

I've resolved this. I installed the SSDT Feb update and targeted the version to SQL Server 2014, as suggested by Dave. I did have to tweak one script (unrelated package and script task) but then I managed to build and deploy. It's processing happily.

The Pre/Post Execution messages were all clean, but based on the advice on this question, I did analyse them in more detail so I've picked up a bit of knowledge here too!

Thanks for all the feedback, especially the comments from billinkc:

Look at the Execution Log for the package that has run on the server. My guess is that you will eventually discover there is a permissions issue but at the moment, it could be a number of places.

Specifically, what I'd look at is the Pre/Post execute entries in the Execution report. that would indicate whether the Script task ever fired. If there's no pre/post, then look for informational events related to your Foreach Loop Containers.

If a permission issue prevents the FELC from seeing a directory, it does not error out. Instead, it fails gracefully, which trips up 99.999% of the population

If it is a validation issue (somefile.txt doesn't exist when the package begins b/c it determines what the current file is), then you will want to set the DelayValidation property to true on the Connection Manager and any tasks that reference said Connection Manager.