SSIS Database Configuration & SQL Agent

When using a database to store the runtime arguments for an SSIS Package, be aware that this isn't used when running your package via a SQL Agent Job.

The solution I've used is the insert a Script Task at the beginning of the Package, which loads the items from the configuration table and update the Variables within the Package.

Execute the following SQL and create an OleDbDataReader:

SELECT PackagePath, ConfiguredValue, ConfiguredValueType FROM <TableName> WHERE PackagePath LIKE '\Package.Variables%'

Once you have that, you need to update the variables via the following script:

Dim sVariable as String
Dim oVariables As Microsoft.SqlServer.Dts.Runtime.Variables

While oOleDbReader.Read   
    sVariable = <-- Extract Variable name from PackagePath -->
    If Dts.VariableDispenser.Contains(sVariable) Then
        ' Lock variable for write
        Dts.VariableDispenser.LockOneForWrite(sVariable, oVariables)
        oVariables.Item(sVariable).Value = Convert.ChangeType(oOleDbReader.GetValue(1), oVariables.Item(sVariable).DataType)
    End If
End While

The benefit of this approach is that you can continue using the same database table.