Here's a little tip for you.
Some database pros are skeptical about the arrival of the XML data type in SQL Server 2005. They see it as an opportunity for cowboy developers to store all of their data in a single field with no regard for atomicity, normalisation or referential integrity. OK, I'm exaggerating a bit but you get the point.
Well the XML data type DOES have uses and we're using it in a very handy way on my current project. We process AS2005 objects numerous time during our nightly batch load and we realised that building a seperate task for each time we needed to execute a processing job was creating needless effort.
Instead, we have a package that contains an Analysis Services 2005 Processing Task. We call that package whenever we need to process something in AS. We pass it an integer parameter (using a Parent Package Configuration) which keys the following table:
CREATE TABLE ASProcessingCommands(
ASProcessingCommandsID int PRIMARY KEY,
ProcessingCommands xml
)
The ProcessingCommands column contains an XML snippet that tells the Analysis Services Processing Task which tasks to process. We retrieve the appropriate value from the table and then set the ProcessingCommands property of the task using an expression.
So, not only have we made use of the XML type but we have also implemented a parameterized, reusable, SSIS package. Anyone that reads this blog regularly will now that I have lamented on many occasions the lack of reusability in SSIS so I am very pleased that we have gone some way toward reusability here.
-Jamie