About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

Search

Report Portal
blank

SSIS Package to drop/create partitions based on partition list in the SQL Server table

April 18th, 2007 by Vidas Matelis

In my past blogs I showed how to use SSIS package to process dimensions, cubes or build aggregates. I am slowly rebuilding my existing packages thanks to points from Darren Gosbell and Jess Orosz. I found that using CaptureXML method is more convenient for me and it is definitely faster.

Just to explain again about this method. I can change Analysis Services objects (create, drop, process) in the script using AMO commands. Normally each AMO command is executed in a separate batch. But AMO has an option to save XMLA script from the multiple AMO commands and then submit them in one batch. This is done my assigning Analysis Services server property CaptureXML value ‘True’ (to start capturing XMLA) or ‘False’ (to stop capturing XMLA). Then XMLA log can be executed using ExecuteCaptureLog statement where first parameter specifies if statements have to be run in batch.

Past weekend I had some time to rewrite my partition synchronization package to use CaptureXML method. I maintain a list of partitions in SQL Server database table iwSSASPartitions. I use a SSIS script to drop partitions if they do not exists in my partition table and create new partitions if they do not exists in SSAS database. I have a SQL Server table with the list of partitions that should exist in SSAS database. Here is the script to create this table:

CREATE TABLE [dbo].[iwSSASPartitions](
 [Partition] [varchar](100) NOT NULL,
 [CubeName] [varchar](100) NOT NULL,
 [MeasureGroup] [varchar](100) NOT NULL,
 [SQL] [varchar](4000) NOT NULL,
 [NeedsReProcessing] [bit] NOT NULL,
 CONSTRAINT [PK_iwSSASPartitions] PRIMARY KEY CLUSTERED ( [Partition], [CubeName], [MeasureGroup])
)

Normally I would deploy SSAS database (no processing) with just one partition per measure group. Then I would run my SSIS package to synchronize partitions between SQL Server table and SSAS database. For initial deployment I would drop about 35 partitions and then create about 250 new partitions. During normal monthly load I would need to create about 30 partitions.

My old package was runnign XMLA scripts to create/drop partitions. I was creating/dropping partitions one by one. With old method my initial drop of 35 partition was taking about 5 min. Step to create new 250 partitions was taking about 15 min. As this is not done everyday, 20min to me seemed like a reasonable time. But after I re-wrote my package to take advantage of batch executions, step that drops partitions run for 15 seconds and step that create partitions run for 30 seconds. At first I actually thought that my code is not executing! So this is power of executing XMLA in the batch!

Steps to create SSIS package that drops/create partitions based on partition list in the SQL Server table:

  • Create new connection to SQL Server database that contains SQL table with list of partitions. It is important here that connection type is ADO.NET and not OLE DB. I had to use ADO.NET connection as I wanted to pass query result as parameter into Script task.
  • Create new connection to SSAS database. Name that connection DM
  • Create new variable with Scope package, name “PartitionListInDW” and type “Object”. This variable will contain list of partitions from table.
  • Add new “Execute SQL Task” component in Control Flow area. Change following properties for this control:
    • ResultSet: “Full Result Set”
    • ConnectionType: “ADO.NET”
    • Connection: From drop down list choose connection you create in first step
    • SQLStatement: SELECT CubeName,MeasureGroup, Partition, SQL FROM dbo.iwSSASPartitions ORDER BY 1,2,3
    • In ResultSet tab click “Add” and enter 0 for “ResultName” and for “VariableName” choose “user::PartitionListInDW”
    • Change step name and description to your liking and click OK.
  • Add new “Script Task” component and connect it to prevoiusly create task. In “Script” tab change property “ReadOnlyVariables” to “PartitionListInDW” and click on “Design Script” button. In “Project Explorer” add reference to “Analysis Management Objects” and “System.XML.dll”. Rename this component to “Drop Partitions”. Add following code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Public Sub Main()

  Dim ExecutionSuccessfull As Boolean = True ‘ If true, package executed without errors
  Dim dataBytes(0) As Byte

  Dim PartListInDM(999, 2) As String ‘ expecting no more than a 1000 partitions.
  Dim DropPartCount As Integer = 0

  ‘ Following objects are used to capture execution results
  Dim oResults As Microsoft.AnalysisServices.XmlaResultCollection
  Dim oResult As Microsoft.AnalysisServices.XmlaResult
  Dim oMessage As Microsoft.AnalysisServices.XmlaMessage

  Dim oPartitionList As Variable = Dts.Variables(“PartitionListInDW”) ‘ From variable
  Dim oDS As DataSet = CType(oPartitionList.Value, DataSet)

  Dim oTbl As DataTable = oDS.Tables(0)
  Dim oRow As DataRow
  Dim oConnection As ConnectionManager
  oConnection = Dts.Connections(“DM”)
  Dim sServer As String = CStr(oConnection.Properties(“ServerName”).GetValue(oConnection))
  Dim sDatabase As String = CStr(oConnection.Properties(“InitialCatalog”).GetValue(oConnection))

  Dim oServer As New Microsoft.AnalysisServices.Server
  oServer.Connect(sServer) ‘ connect to the server and start scanning down the object hierarchy
  Dim oDB As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabase)

  If oDB Is Nothing Then
    Dts.Log(“Did not find expected database: ” & sDatabase, 0, dataBytes) ‘ You need to setup proper package loging to see this!
    ExecutionSuccessfull = False
    GoTo Done
  End If

  Dim oCube As Microsoft.AnalysisServices.Cube
  Dim oMeasureGroup As Microsoft.AnalysisServices.MeasureGroup
  Dim oPartition As Microsoft.AnalysisServices.Partition
  Dim bNeedToDropPartition As Boolean
  For Each oCube In oDB.Cubes
    For Each oMeasureGroup In oCube.MeasureGroups
      For Each oPartition In oMeasureGroup.Partitions
        ‘ Check if this partition exists in our partition table
        bNeedToDropPartition = True
        ‘ oTbl.Reset()
        For Each oRow In oTbl.Rows
          If oRow(“CubeName”).ToString = oCube.Name.ToString And oRow(“MeasureGroup”).ToString =     oMeasureGroup.Name.ToString _
  And oRow(“Partition”).ToString = oPartition.Name.ToString Then
            bNeedToDropPartition = False ‘ Found partition in DW list, no need to drop it
            Exit For
          End If
        Next
        If bNeedToDropPartition Then
          PartListInDM(DropPartCount, 0) = oCube.Name
          PartListInDM(DropPartCount, 1) = oMeasureGroup.Name
          PartListInDM(DropPartCount, 2) = oPartition.Name
          DropPartCount = DropPartCount + 1
          Dts.Log(“Found partition that needs to be dropped: ” & oPartition.Name, 0, dataBytes) ‘ You need to setup proper package loging to see this!
  ‘       oPartition.Drop()
       End If
     Next
   Next
  Next
  ‘ MsgBox(DropPartCount)

  oServer.CaptureXml() = True
  Dim i As Integer = 0
  For i = 0 To DropPartCount – 1
    oCube = oDB.Cubes.FindByName(PartListInDM(i, 0))
    oMeasureGroup = oCube.MeasureGroups.FindByName(PartListInDM(i, 1))
    oPartition = oMeasureGroup.Partitions.FindByName(PartListInDM(i, 2))
    oPartition.Drop()
  Next i
  oServer.CaptureXml() = False
  ‘ Executing log that contains all XMLA commands
  oResults = oServer.ExecuteCaptureLog(True, False)
  ‘Log the errors and warnings
  For Each oResult In oResults
    For Each oMessage In oResult.Messages
       If oMessage.GetType.Name = “XmlaError” Then
         ‘The processing failed
         Dts.Log(oMessage.Description, 0, dataBytes) ‘ You need to setup proper package loging to see this!
         ExecutionSuccessfull = False
       Else
         ‘It is just a warning.
         ExecutionSuccessfull = True ‘ if you want to fail on warning, change this to False
      End If
    Next oMessage
  Next oResult
  ‘ Finished code that drops partitions
  ‘ ————————————————————————————————————————-

Done:
  oServer.Disconnect()

  If ExecutionSuccessfull Then
    Dts.TaskResult = Dts.Results.Success
  Else
    Dts.TaskResult = Dts.Results.Failure
  End If

End Sub

End Class

  • Few notes about last script:
    • Trying to run dropping of partitions in a single batch in parallel (ExecuteCaptureLog(True, True)) generated error: “The Delete element at line 9, column 94 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) cannot appear under Envelope/Body/Execute/Command/Batch/Parallel”
    • You cannot drop partition in For Each partition loop. This is because dropping would change list of members in For each statement. That is way I save all partitions that have to be dropped in array and drop it latter.
    • You need to enable proper package and (!) step logging to see errors/warnings in the log
  • Add new “Script Task” component and connect it to prevoiusly create task. In “Script” tab change property “ReadOnlyVariables” to “PartitionListInDW” and click on “Design Script” button. In “Project Explorer” add reference to “Analysis Management Objects” and “System.XML.dll”. Rename this component to “Create New Partitions”. Add following code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Public Sub Main()

  Dim ExecutionSuccessfull As Boolean = True ‘ If true, package executed without errors
  Dim dataBytes(0) As Byte

  ‘ Following objects are used to capture execution results
  Dim oResults As Microsoft.AnalysisServices.XmlaResultCollection
  Dim oResult As Microsoft.AnalysisServices.XmlaResult
  Dim oMessage As Microsoft.AnalysisServices.XmlaMessage

  Dim oPartitionList As Variable = Dts.Variables(“PartitionListInDW”) ‘ From variable
  Dim oDS As DataSet = CType(oPartitionList.Value, DataSet)

  Dim oTbl As DataTable = oDS.Tables(0)
  Dim oRow As DataRow
  Dim oConnection As ConnectionManager
  oConnection = Dts.Connections(“DM”)
  Dim sServer As String = CStr(oConnection.Properties(“ServerName”).GetValue(oConnection))
  Dim sDatabase As String = CStr(oConnection.Properties(“InitialCatalog”).GetValue(oConnection))

  Dim oServer As New Microsoft.AnalysisServices.Server
  oServer.Connect(sServer) ‘ connect to the server and start scanning down the object hierarchy
  Dim oDB As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabase)

  If oDB Is Nothing Then
    Dts.Log(“Did not find expected database: ” & sDatabase, 0, dataBytes) ‘ You need to setup proper package loging to see this!
    ExecutionSuccessfull = False
    GoTo Done
  End If

  Dim oCube As Microsoft.AnalysisServices.Cube
  Dim oMeasureGroup As Microsoft.AnalysisServices.MeasureGroup
  Dim oPartition As Microsoft.AnalysisServices.Partition

  oServer.CaptureXml() = True
  For Each oRow In oTbl.Rows
    oCube = oDB.Cubes.FindByName(oRow(“CubeName”).ToString)
    If oCube Is Nothing Then
      Dts.Log(“Did not find cube: ” & oRow(“CubeName”).ToString, 0, dataBytes) ‘ You need to setup proper package loging to see this!
      Continue For
    End If
    oMeasureGroup = oCube.MeasureGroups.FindByName(oRow(“MeasureGroup”).ToString)
    If oMeasureGroup Is Nothing Then
      Dts.Log(“Did not find measure group: ” & oRow(“MeasureGroup”).ToString, 0, dataBytes) ‘ You need to setup proper package loging to see this!
      Continue For
    End If
    oPartition = oMeasureGroup.Partitions.FindByName(oRow(“Partition”).ToString)
    If (oPartition Is Nothing) Then
      Dts.Log(“Need to create partition: ” & oRow(“Partition”).ToString, 0, dataBytes) ‘ You need to setup proper package loging to see this!
      oPartition = oMeasureGroup.Partitions.Add(oRow(“Partition”).ToString)
      oPartition.StorageMode = StorageMode.Molap
      oPartition.Source = New QueryBinding(oDB.DataSources(0).ID, oRow(“SQL”).ToString)
      If oMeasureGroup.AggregationDesigns.Count > 0 Then ‘
        oPartition.AggregationDesignID = oMeasureGroup.AggregationDesigns(0).ID ‘ Taking first available aggregation design!
      End If
      oPartition.Update(UpdateOptions.ExpandFull)
    End If
  Next
  oServer.CaptureXml() = False

  ‘ Executing log that contains all XMLA commands
  oResults = oServer.ExecuteCaptureLog(True, False)

  ‘Log the errors and warnings
  For Each oResult In oResults
    For Each oMessage In oResult.Messages
      If oMessage.GetType.Name = “XmlaError” Then
        ‘The processing failed
        Dts.Log(oMessage.Description, 0, dataBytes) ‘ You need to setup proper package loging to see this!
        ExecutionSuccessfull = False
      Else
        ‘Its just a warning.
        ExecutionSuccessfull = True ‘ if you want to fail on warning, change this to False
      End If
    Next oMessage
  Next oResult
  ‘ Finished code that creates new partitions
  ‘ ————————————————————————————————————————-

  Done:
    oServer.Disconnect()

  If ExecutionSuccessfull Then
    Dts.TaskResult = Dts.Results.Success
  Else
    Dts.TaskResult = Dts.Results.Failure
  End If

End Sub
End Class

  • Few notes about last script:
    • Trying to run creation of partitions in a single batch in parallel (ExecuteCaptureLog(True, True)) generated error: “The Alter element at line 9, column 141 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) cannot appear under Envelope/Body/Execute/Command/Batch/Parallel.”
    • You need to enable proper SSIS package and (!) step logging to see errors/warnings in the log
  • Populate table dbo.iwSSASPartitions with the list of partitions you desire
  • Test run package. Be careful, if you run it with empty table, all partitions will be dropped.

Vidas Matelis

Note 2008-July-03, it appears to me that this code might be used by many people. I had multiple requests for actual package and finally I posted ziped version of package here.

And thank you very much to everybody who left me (or will leave) comments here about this post. I am very happy that you found my code useful!

 Note 2009-July-05: Added code that explains how I create SQL Server table and populate partition list here.

Posted in SSAS, SSIS | 43 Comments »

43 Responses

  1. Kamal Tuteja Says:

    Good , this help me a lot

  2. Kathryn Says:

    You are the bomb !!!! – AWESOME – helps me alot
    Thank you !!!!

  3. Kathryn Says:

    Just one question – is this going to work in 64bit environment?

  4. Vidas Matelis Says:

    Yes, I am running this script on 64bit environments. Just for 64bit environment make sure that on the script task you setup parameter “PrecompileScriptIntoBinaryCode” to True.

  5. Kathryn Says:

    Thank u – again great script!

  6. Brian Says:

    Hi Vidas,
    This script synchronize the partition from SQL server right, how about if we are using OLE DB to connect to a datbase which is not SQL server. FOr example we have Oracle instance and cube uses OLEDB connection to fetch data from Oracle. So can I still use this script to create a partition or can I just creata a XMLA from the existing partiton and do the appropriate changes to it inorder to create a new partion ?

    Thanks
    Brian

  7. Vidas Matelis Says:

    Brian, I have not tested this, but all I need from SQL Server is just a table with a list of partitions and partition parameters. So I do believe this should work with Oracle DB. But you will have to test and maybe do some adjustments (like type of connection) yourself. Please post here if you were able to make it work.

  8. John Hardin Says:

    Vidas:

    In the comments to each script you say:

    Trying to run … in a single batch in parallel (ExecuteCaptureLog(True, False)) generated error

    Yet in both cases the code says:

    oResults = oServer.ExecuteCaptureLog(True, False)

    This is confusing – does the ExecuteCaptureLog(True, False) run correctly, or generate an error? Or does it run correctly even though it generated an error message?

  9. Vidas Matelis Says:

    John Hardin, Thanks for pointing out my mistake. I fixed it now. ExecuteCaptureLog(True,True) generated error.

    Thanks!

  10. John Hardin Says:

    Great, thanks Vidas!

    A suggestion for the Drop Partition script: rather than capturing the object hierarchy name strings associated with the partition you want to drop, and in the later loop doing a bunch of FindByName()s, why not just define a collection object and add to that collection the partition objects to drop, then iterate over the collection and drop the objects?

    Roughly:


    Dim oDrops as Collection

    ... loop through existing partitions and inspect
    ... if you want to drop it:
    oDrops.Add(oPartition)

    ...finished looping through partitions, now drop them:

    For Each oP as Microsoft.AnalysisServices.Partition In oDrops
    oP.Drop()
    Next

  11. John Hardin Says:

    You need a “preview” button. :(

  12. Brian Says:

    Vidas,
    Just wanna Let you know that, the creation of partion for a Oracle database via OLEDB works. Thanks a lot !!

    Just one question, I’ve created 60 partition by executing the script task, I also see them in Mangement Studio, however my project still has the partition, I’ve created in BI Studio. Does it mean , when I do some changes and deploy my changes will it be overwritten ?

    Sorry if the question is too basic.

    thanks

  13. Vidas Matelis Says:

    Brian, scripts creates partitions online, so you can see them through Management Studio when connected to the cube. But this script does not touch your offline project. So yes, after you change your project and re-deploy it, you have to re-run partition generation script.

  14. Brian Says:

    Vidas, I guess I’m not very specific about my question,sorry about that.
    What I meant was , how to keep the offline project (in BI studio)
    and the management studio when connected to cube in Synch.

    For an instance,I developed a new cube with just two partitions, however the cube suppose to have more than 60 partitions, so I create them in Mangement studio. Now, If I’ve to apply some change in the cube,I have to do the changes in BI Studio,so at this point of time, If I would like to have all the partitions in BI studio to be displayed,Do I have to ‘create a new project by importing from AS database’ or can I make it synch by someother means?

    Thanks

  15. Vidas Matelis Says:

    Brian, if you need to keep partitions in offline project in sync, then probably importing project from AS database is right option. Is there any reason why you want to keep them in sync?
    In my case list of partitions changes over time – most (but not all) of the partitions are based on DateKey. And I do not care during development (in BIDS studio offline) about actual partitions. But as soon as I deploy new structure, I run this synchronization script and have all partitions created.
    In any case, this script does partition build just for online database. I don’t know how I could do this for offline project.

  16. tao Says:

    You are great !!

  17. Brian Says:

    Thanks again Vidas, the only reason I’m looking to have them in Synch is, from time to time we get production queries from our users. In AS2000, its lot easier to browse the cube in Mangement Studio, however I couldn’t do that in SSAS2005.Also, we wanted to resolve the issues / answer queries asap, that’s the foremost reason we wanna keep partition in synch in BIDS.

  18. Brian Says:

    Hello Vidas,

    I just want to know if it’s possible to paramaterise the ‘AS database’.
    We have several AS databases and each of them contianing atleast one cube.Also, we create one partition every fortnight. Ofcourse, I can create one script per database,but it would be great if it can be acheived in 1 script rather then one per AS database.

    Pls advise.

    Thanks

  19. Brian Says:

    Sorry, Ignore my question. it can be done with Variables.

    thnx

  20. Brian Says:

    Hi,
    Can we pass a Package variable as a parameter to the Execute SQL Task ?

    Ideally I would like to override the where clause of the SQL in the Execute SQL Task.
    At the moment, I’ve the SQL as direct input.
    If I manage to pass variable to the SQL, this would help immensly in reducing the number of jobs we have.

    Thanks in advance.
    Rajesh

  21. Vidas Matelis Says:

    Brian,

    Yes you can pass package variable as a parameter into the Execute SQL Task. This package is using ADO.NET connection type, and passing parameters is different from OLE DB connection type. Example is
    here

  22. Tim Webber Says:

    Hi Vidas, thankyou for posting this solution. I am working with it right now. I wanted to follow up on an earlier question…how to keep the online cube partitions in sync with the VS project for that cube.

    The issue I see is that you now have to fully process all patitions over again if you make a change to you cube – simple as the change may be. I guess that many cube changes would require full reprocessing but how about a new calculation or changing an attribute description?

    Would these require a full reprocess of all paritions? If not (which I suspect), it would be nice to have a way to get the ssis-sql generated partitions in the VS project otherwise they will be clobbered when you re-deploy and you will be forced to update the needsreprocessing field of the sql table, run the SSIS package to recreate all the partitions and do a full cube reprocessing – which can take time for large cubes.

    Any thoughts?

  23. Vidas Matelis Says:

    Tim,

    you are right, there is a challenge if you want to change cube structure. My script does all changes to the online database and I do not know how one would script such changes and apply to the BIDS solution instead.

    So if you want to do small change to the cube, then your options depends on the change. Here is what I do depending on specific change:

    If I want to change the cube calculation script (for example: add new calc measures, fix existing ones), then I use BIDS Helper. This utility allows to deploy just MDX cube script and it does that very fast.
    For clients where I cannot install BIDS Helper, I do my changes to online database and then copy them to offline BIDS solution. For those who don’t know how to edit database online: Start BIDS, select File->Open->Analysis Services Database and choose your database. The edit cube script and click Save. After that don’t forget to copy script to offline solution.

    If I want to apply small change/fix to the structure, I again usually do this to online database and then copy change to offline BIDS solution. You have to be very careful with this as even if you might think that cube will not be invalidated, BIDS might decide otherwise. For critical systems I always test such changes first on the QA machine.

    Of course you also have an option to create BIDS solution from existing online database and then edit it. To do this start BIDS and select menu item “New->Import Analysis Services 2005 Database”

    I hope this will help you,

    Vidas Matelis

  24. C Says:

    implementing the following code:
    Dim oDrops as Collection

    … loop through existing partitions and inspect
    … if you want to drop it:
    oDrops.Add(oPartition)

    , the script fails at .Add partition saying that reference was not set to instance of object..has anyone tried this?

    i am trying to use the .merge method on a partion and the first step is to generate a collection of source partitions to merge. the script fails trying to generate a collection of partitions.

    thanks,
    C

  25. C Says:

    solved the collection problem by
    setting:
    oDrops = measuregroup.partitions()
    oDrops.clear
    oDrops.Add(oPartition)

    I get an error at:
    oTarget.merge(oDrops)

    saying The operation requires a connected parent Server at Microsoft.AnalysisServices.Partition.Merge(IEnumerable sources)

  26. NS Says:

    Can you use the same table for multiple cubes. I have multiple cubes in the environment and i would like to use one single table to manage all the paritions list in the same table.

    Can that be done without changing any code.

  27. Bart Says:

    C,

    Just encountered the same problem and found the following solution:
    – oMG is the measure group object within which two partitions need to be merged
    – oPart is the Partition object to merge into
    – the “History” partition in oMG

    Dim oColl As Partition() = New Partition(0) {oPart}
    oMG.Partitions.GetByName(“History”).Merge(oColl)

    Hope this works for you.

  28. Sergio de Argentina Says:

    Excuse me to write in Spanish, but my English is not good
    Quiero felicitarte por el paquete ssis , es muy breve , performante y muy util…
    Lo ajuste a mis necesidades y me vino al dedillo

    Thank you!!
    saludos desde el sur

  29. Vidas Matelis Says:

    Sergio,

    Thank you for your comments. I translated them in “Google Translate”:
    “I want to congratulate you for the SSIS package, is very brief and very useful performante …
    What fits my needs and I came to dedillo”

  30. Puneet Says:

    Hi,

    My requirement is to process the cube twice a day with approx 75 million rows of data. It is partitioned on monthly basis. Now how to directly load the data from SSIS package to Cube in SSAS and then process directly the partition itself. So that I need not to process the whole cube. Also there are SCD’s as well.
    Please guide me on it, if possible with code.

    Thanks in advance.

  31. Frank Says:

    Anyone have a VS2008 version of this package? I am having a difficult time getting this to work in 2008.

  32. Vidas Matelis Says:

    Frank,

    I have not tested this package on 2008 yet. But I would expect it to work.

    Have you copied file
    C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.DLL to folder:
    C:\WINDOWS\Microsoft.NET\Framework\v3.5

    What specific error message are you getting?

  33. Frank Says:

    Vidalis – I got it working. Seems I was selecting the wrong .dll in my references. Awesome code! Is there a processing piece of it that use the NeedsProcessing column of the table?

  34. Vidas Matelis Says:

    Frank,
    That code is currently not available for public. But based on my other processing scripts you could create it quite easily. Simple logic – if ALL partitions require processing, then I do full dimension processing and then full cube processing. If at least one partition does not require processing, then I do incremental dimension processing and full processing just for partitions that require it.

  35. Frank Says:

    well I will give it a shot. What other processing scripts were you referring to?

    Thanks btw Vidas!

  36. chaitanya Says:

    The code is very usefull.

  37. Script to automate SSAS partition management (SQL + SSIS) | Vidas Matelis Analysis Services Blog Says:

    […] SSIS Package to drop/create partitions based on partition list in the SQL Server table […]

  38. Ns Says:

    Vidas

    I also have proactive caching setting in my partition. I am new to Ado.net. Is their any way you can guide us to deal with cube that have Proactive caching set uped.

    thanks

  39. Dave Says:

    Hi

    This is very useful code , can we use this code to even if we do not have to sync sql server partitions and SSAS Partitions by Populating required partitions in the iwSSASPartitions by a Stored Proc ?

    I don’t have to drop paritions based on the SQL server dataware ouse , so will have to disable that task

    Thanks for your help

  40. Vidas Matelis Says:

    Dave,

    This code specifically looks for list of partitions and filter conditions in the table. If you will not use SP to populate that table, you need to do add records there somehow as SSIS creates new partitions based on data in that table. Of course, you can disable step that drops partitions.

  41. Prashant Mhaske Says:

    i have issue regarding dynamic partition processing using SSIS Script Task. while processing selected partition , other partition are also getting process. Why ?

  42. Ben Says:

    I have just implemented this change, along with some logic to process newly added partitions.

    I have noticed that when a partition is deleted, the data is still available in the cube. Do I need to “Unprocess” this partition?

  43. tawargerip Says:

    Can you Please pass on the Insert scripts for the partition table in backend which is used for processing