2

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.

enter image description here

enter image description here

Makyen
  • 31,849
  • 12
  • 86
  • 121
default_noob_network
  • 1,204
  • 3
  • 19
  • 39
  • Does the variable have "evaluate as expression" set? Does the flat file connection manager use any expressions? Are there any package configurations? What version of SSIS? – Martin Smith Jun 08 '16 at 17:38
  • Sounds like you have an expression on the connection manager. Right click - Properties - Expressions. – Joe C Jun 08 '16 at 17:39
  • I do have an expression here. But it is @[User::FileName[ - I would expect SearchFileName to be in use! @MartinSmith Eval as expression is set to false. SQL Server 2008 – default_noob_network Jun 08 '16 at 17:42
  • Did you check the Job-configuration? Maybe one of the variables / parameters / connection managers is being manipulated there? Also, if you use the Integration Services Catalog, then check the package-configuration. – Johannes Jun 08 '16 at 17:46
  • @Johannes the only thing I see in the job configuratoin is under data sources for each step/package - it shows the connection managers used in the packages (where i found the hardcoding of file names) but they are unchecked in the job config. – default_noob_network Jun 08 '16 at 17:56
  • Open your package in Code view, and search the code for "SearchFileName" to see if the variable is being used anywhere. – Tab Alleman Jun 08 '16 at 18:51
  • @TabAlleman I have added another picture at the end of the original question. I found in code a reference to SearchFileName - it is used in the enumerator as shown in the new image. I still am confused on what's going on here (and why it would fail when I execute it manually!) – default_noob_network Jun 08 '16 at 19:43
  • As I asked before: Are you using the Integration Services Catalog? If yes: check the package-configuration there too. If the package runs on the server but not on your dev-client, than there must be some config-diffs in the package- or job-config. Also check your priviliges on the file-server, maybe you're missing some on certain directories? – Johannes Jun 09 '16 at 09:32
  • Grab a copy of [BidsHelper](http://bidshelper.codeplex.com) It's very handy for SSIS, specifically those working with 2005/2008. If there are Expressions or Configurations on items, they'll show up with color highlighting. It'll help identify where some of the magic is happening in the package – billinkc Jul 07 '16 at 19:56

0 Answers0