Can I retrieve config values for SSIS from XML in a table?

Question

My current client stores all of their configuration information for the enterprise applications in a single table that holds XML. They then use a custom built front end to maintain the configuration values.

I'm writing a fairly straight-forward import process for them using SSIS. I need to make the connection strings and some other information configurable and they want me to use their table. It seems like SSIS expects a file though. Is there any way that I can point SSIS to retrieve its configuration values from an XML stream instead of a path to a file?

The configuration table that they use does not match the structure of a standard SSIS configuration table that you would get using SQL Server as a configuration source with the standard wizard.

Thanks for any advice!

Answer 1

You can retrieve values from the table, put it in variables, and using a script, transfer the varaibale values into the SSIS parameters.

Having the XML formatted just like the SSIS XML file is a huge bonus, though.

Answer 2

Is there a away to put a trigger on thier table to update the SQL Server config table or create a new XML document anytime an SSIS configuration is inserted or updated? Then you could use what you need and they could do what they need and all would be happy.