SSIS Not building file path when executed via Agent Job but works in SSDT

Question

I'm just after some advice\thoughts.

I have a SSIS Package (SQL Server 2016) that performs the following:

• It loops through a folder on a network share (I have even granted the 'Everyone' group full control to this folder and assigns the file paths on files in the folder to a variable (@FilePaths)

• This variable is then used as an attachment in a Send Email task

• When I execute interactively in Visual Studio\SSDT it works fine - I receive an email with the attachment(s)

• If I call via a SQL Agent Job I receive the email, but no attachment

• As a test, I have also inserted the @FilePaths as the body of the email - again when executed via SSDT I receive the path of the files in the email and when via the Agent Job the body is blank

• I'm using a script task within a loop to retrieve the file names

In summary, when executed via the SQL Agent job, it cannot 'see' the files to retrieve the paths, even though I have set the Everyone group to the folder and also the account the Agent is being run as.

Does anyone have any ideas what I could be missing here? It has to be permissions of some sort.

Thanks.

Answer 1

Just to finish this - the overnight job was successful - so the fix was to change the 'TargetServerVersion' from 2017 to 2016 and re-deploy to the SSIS catalog.