SSIS file exist check works in SSDT not running from SSISDB

Question

this is one that has me stumped and Ive been doing this a long while.

Migrating to SQL server 2016, large number of ETL. Easy enough.

One of the ETL packages has a simple script task to take a table of files, run a file exists foreach loop.

it uses a project parameter to create the unc ( \servername\share) and then binds that to the file name in the script task.

use an environment config setup in SSISDB

execute in SSDT works fine, deploy to catalog and it cant see the file. i know youll say permissions, but ive permissioned everyone group to share and drive in case its that. SSISDB execution means it should be running under my security context and im domain admin, local admin and creator owner of the share.

even strangeR, i have created simple package to grab the contents of one of the files and import into a dump table in case permissions or pathway were duff ( even though they work in SSDT might be the enviroNment config in SSISDB). THIS WORKS FINE, therefore it cant be the envrionment setup of SSISDB being referenced.

please note this is not running from an agent job yet so wont be due to agent server account issue. need to get it running from ssisdb first then ill create an agent job

So -- script task cant see unc share, built from two variables, that works in ssdt and its running under same credentials...

Go

For what its work the script task code is

Dts.Variables("BolFileExists").Value = File.Exists(Dts.Variables("StrLoadFileLocation").Value.ToString & Dts.Variables("StrCurrentFile").Value.ToString)

Answer 1

Well I found the answer and I deserve to punch myself in the face.

Tried everything, it was a file variable and path variable being pulled together in the script task so tried concatenation that before the script task, pumped this into a table to ensure it was going to write table.

Literally everything was fine and still didn’t work.

The issue....

Building it as a 2017 package onto a 2016 Sql server.

I’ve not found what was missing dll wise but it must have been one of those that meant the script task couldn’t find the files but weird it didn’t break and just said the files weren’t there!

Thanks all for input, I’m going to go put my head in the door and slam it

Answer 2

This is a slightly different answer as it shows a different approach and removes the script task. I use a foreach to check if the file exists using GUI tools provided by SSIS: