I have inherited a bunch of SSIS packages. In some of these packages there is a connection manager being used to grab a flat file. These flat files are downloaded daily from FTP and unzipped - then used in these packages. The packages are executed via SQL agent job and the job is currently succeeding.
Now we have an additional flat file that we didn't used to have and we want it included (we are just dumping the data from the flat file into a SQL server table). So, this new flat file is going to be dumped into a new table. The file and table are the exact same schema as one of the files/tables that is already in use - so I've set everything up to match.
The problem is, I can't get one of these packages to execute when I run it manually because it says it can't locate the flat file referenced in the connection manager. What's weird is I see a specific flat file hard coded in the connection manager. The files include a simple date stamp in the filename - so... if the SQL job is still succeeding, and assuming the packages I'm looking at are the same version that is executed by the job (I've verified this by exporting the live packages/importing into BIDS and comparing) then how is it possible that the package is referencing an old file that no longer exists. (part of the sql job also deleted old files - so it is impossible that the package is grabbing an old one).
The only other thing I've noticed is a variable in the package called "SearchFileName" - this makes perfect sense, but I cannot see anywhere in the package that it is used. It's probably is, but I can't find where. Maybe there is a specific way to look for it?
So to summarize - a scheduled job executes a package - but when I look at the package in BIDS there is a hardcoded reference to a file that no longer exists that is in use in the package. I can't figure out how this is working, and I need to know so that I can scheduled another package to pull from another, newer, similar file.
Here is a screenshot of the Varibale and Connection Manager that is in use w/ hardcoded filename.
