I have a client at which I am currently utilizing a method similar to
the one found in the Integration Services examples where a SQL query is
used to return the partitions that should exist in an SSAS cube. Then
the SSIS packge uses the results to see if the partitions exist and
creates them if necessary.

This works well, however, I now have the need to handle partitions in a
"rolling" fashion. So I want to process the 3 previous months of data
into an active partition (or partitions) and roll the "old" stuff in to
year partitions.

My thought is that I will want to do this using code rather than
relying on a SQL query to tell me what to do because there will be a
greater dependency on what actually exists in the SSAS cube. I think I
will have to create some logic to see what partitions actually exist
and then act on that information. I will also want to merge "old
partitions" into the larger year chunks.

Has anyone done anything like this? If so, what did you find worked
best and what kind of pitfalls did you find? If I was not clear in my
description, please let me know.