SSIS Configuration problem: not returning SQL Server Table config values

Question

This was posted on server fault, but in retrospect that may not be the best spot for it, so I've moved it here.

I have a package that runs fine on my dev machine but in production (and also on another dev desktop) 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 on my machine. It retrieves the correct path from the table and populates the variable as expected. Why this stops working with no error elsewhere is a mystery. Is there someplace that configuration needs to be 'switched on', apart from the checkbox on the front page of the config wizard.

Simon Simon · Accepted Answer · 2011-05-12T15:13:08

Try Event Handlers 1. Select Event Handler OnVariableValueChanged 2. Add a script task to the pane 3. Pass in System::VariableName,System::VariableValue as InputVariables 4. Add the following to the code:

Public Sub Main()

    Dts.Events.FireInformation(0, "VariableChangedEventLog", "Variable " & Dts.Variables("System::VariableName").Value.ToString() & " Changed to:- " & Dts.Variables("System::VariableValue").Value.ToString(), "", 0, True)

    Dts.TaskResult = Dts.Results.Success
End Sub

This will print out the variables and will at least give you an indication of whether the correct values are passed into the package.

I have the problem that my package receives the correct configuration file, but then is not passing this value onto my child packages. May well post a link to a new post on this.

SSIS Configuration problem: not returning SQL Server Table config values

1 Answers