Dynamic Partitions using SSIS

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.

Try to look at the materials coming with documentation for project REAL: http://www.microsoft.com/sql/solutions/bi/projectreal.mspx#E3B

There is tons of useful material you'd find there.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamic ORDER BY within stored procedure

I am trying to do something similar to the following where I want to perform dynamic ordering on two tables that have been unioned as shown below.

CREATE PROCEDURE procedure_name
@.regNum varchar(14),
@.sortOrder tinyint = 1
AS
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblSPG_Header
WHERE
REG = @.regNum
UNION
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblRCH_Header
WHERE
REG = @.regNum
ORDER BY Obs_Date DESC
GO

Note that I am only sorting on the Obs_Date column, but I'd like to be able to sort on any column within the selection list. I know that I need to use:

ORDER BY CASE WHEN @.sortOrder = 1 THEN Obs_Date END DESC

but I frequently get the following error when I try to do so:

"ORDER BY items must appear in the select list if the statements contain a UNION operator"

If anyone can offer any suggestions, I would appreciate it. Thanks.try something like :

EXEC ( ' SELECT Filler_OrdNum As ''Accession'', RTrim(Obs_Code) As ''Observation'', REG As ''Register'',Obs_Date As ''Observation Date''
FROM tblSPG_Header WHERE REG = ' + @.regNum + ' order by ' + @.ordcolumn +
' UNION
SELECT Filler_OrdNum As ''Accession'', RTrim(Obs_Code) As ''Observation'', REG As ''Register'',
Obs_Date As ''Observation Date'' FROM tblRCH_Header WHERE REG = ' + @.regNum + ' order by ' + @.ordcolumn )

hth