0

I have a package that runs fine on my dev machine but in production the Config seems to stop working. I'm using SQL Server Table configuration to pull a value from a table and populate a variable. My Database connection string is passed in at runtime so this should be taken care of in the production environment. There are no errors, but the value is not retrieved and the variable remains blank (it is actually a path, so I get a file not found as the path is blank).

The package works perfectly on my machine. If I run the package it retrieves the correct path from the table as expected. Why this stops working with no error in prod is a mystery.

Any ideas?

Glenn M
  • 121
  • 3

2 Answers2

2

The problem turned out to be very specific to the way we execute all packages at our site. We use a middle tier service to execute packages and it passes in a few variables as it does so, one of those variables being a connection string to the database the package talks to.

The problem was simply that when the package opened the VERY FIRST thing it did was attempt to configure itself (before the passed-in variables had been set). So it attempted to run package configurations before it had the connection string it needed to get the data from the SQL table. The config failed silently, and the package then set the variables that were passed in and carried on with execution. Only to fail because the config had not updated an important path in another variable.

To resolve this we had to remove package configuration entirely for these packages and replace it with a SQL task as step 1 in the package that manually queries the SSIS Config table and populates the variables. Not tested yet, but we're pretty sure this will resolve it.

Thanks to previous posters suggestion of running a trace as the trace confirmed no activity on the SSIS Config table, which meant the config process was not running, and most likely reason was not being able to connect.

Glenn M
  • 121
  • 3
0

I recommend performing a trace during the package execution in PROD. Confirm your successsful connections and the sql being passed.

If you're still stuck, post the results of your trace, and we'll go to the next step.

EngineeringSQL
  • 275
  • 1
  • 2
  • 8