August 18, 2011 at 5:34 pm
Hello,
I have done some searching on this and have not been able to find an answer. I'm wondering if someone would point me in the right direction or could answer my question (which could be fairly simple).
I'm using an XML configuration file to populate a DateTime Variable in my SSIS package. I'm then using that variable when building a SQL statement to execute within the package. If all control flow and data flow items complete successfully, I would like to update the value in the Configuration file. In this way, I can filter data in my SQL query, based on the last successful execution of the package.
In order to accomplish this currently, I'm re-writing the appropriate value in the configuration file by using a Script Task which uses DataReader object to read the contents of the XML file, doing some substring replacements, and finally using a DataWriter object to re-write the value (System.IO namespace). I tried using the script task to update the variable, which I can do, but I don't know how to get the updated value to make it back in to the XML Configuration file. Does anyone know how to do this without using the cumbersome method that I'm utilizing to accomplish this? There's got to be a better way...
Thanks in advance!
August 19, 2011 at 4:25 am
A configuration file is made for reading. If you really want to replace a value (I would use a table in SQL Server for that), you'll need to parse the XML file and update the node's value.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 19, 2011 at 4:49 am
I think it would be simpler if, instead of updating the config file, either you evaluate the variable using an expression, or you output a value at the end of your processing into the variable.
John
August 19, 2011 at 4:54 am
John Mitchell-245523 (8/19/2011)
I think it would be simpler if, instead of updating the config file, either you evaluate the variable using an expression, or you output a value at the end of your processing into the variable.John
But the OP wants to pick this up when the package next runs - so it needs to be persisted somewhere and not just thrown into a variable.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 19, 2011 at 5:00 am
You're quite right, Phil. In that case, I would go with Koen's suggestion and store the value in a table somewhere. It's much easier to modify a database than a configuration file.
John
August 19, 2011 at 7:13 am
Thanks for the replies & suggestions. Makes sense to me that in a solution where we're wanting to update the config value that a SQL table would be a better location for that. The absence of web content programmatically updating a config file was pointing me in that direction. In your environments, do you have a central "key-value" table where you store SSIS package configurations or would you dedicate a separate db to this? Seems a little silly to have a db dedicated to one table with one value, but on the other hand, if you have multiple packages pointing to one table, it becomes difficult to move the database or table. Thoughts?
August 19, 2011 at 7:21 am
I usually have a "Run Table" for each SSIS package that needs this sort of functionality.
I would however not store only the last runtime, but all of them. This way, I have some sort of archive of when the SSIS package has run, and I'll look for the last rundate using MAX.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 19, 2011 at 7:22 am
It all depends how many packages you have, and how distributed are the servers that they read or update. Usually, I would want my config information close to my data, but if you have separate packages or separate databases that need to share the same information, that may not be appropriate.
John
August 19, 2011 at 8:11 am
Alright. Thanks, everyone. I appreciate the help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply