First, the general process such as:
The parameters that need to be used
Ii. description of each control
1. "Get parameters that need to be processed"
SQLStatement as follows:
SELECT 'Dynamicpartitionssas' asDatabaseID,'Rmyymz' asCubeid,'Fact Mz Visit Table' asMeasuregroupid,'Fact Mz Visit Table' + CONVERT(VARCHAR(6),Dateadd(Month,-1,Getdate()), the) asPartitionIDUNIONSELECT 'Dynamicpartitionssas' asDatabaseID,'Rmyymz' asCubeid,'Fact Mz Visit Table' asMeasuregroupid,'Fact Mz Visit Table' + CONVERT(VARCHAR(6),Getdate(), the) asPartitionID
Note: This article is mainly based on the current month, the dynamic processing of this month and one months of data
2. Dynamic processing of partitions
3. Return the XMLA statement to be processed
C # code:
/*Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C # 2008. The Scriptmain is the entry point class of the script.*/using system;using system.data;using microsoft.sqlserver.dts.runtime;using system.windows.forms;using Microsoft.analysisservices;namespace st_1f03ca252f334e528b0c03196047066b.csproj{[System.AddIn.AddIn ("Scriptmain", Version = "1.0", Publisher = "", Description = "")] Publicpartial class ScriptMain:Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {#region V STA generated code enum scriptresults {Success=Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure=Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure}; #endregion/* The execution engine calls this method when the task executes. To access the object model with the Dts property. Connections, variables, events, and logging features is available as members of the Dts property as shown in the F ollowing examples. To reference a variable, call dts.variables["Mycasesensitivevariablename"]. Value; To post a log entry, call Dts.Log ("This is my log text", 999, NULL); To fire a event, call Dts.Events.FireInformation ("Test", "hits the Help Message", "", 0, true); Use the Connections collection with something like the following:connectionmanager cm = Dts.Connections.Add ("OLE DB "); Cm. ConnectionString = "Data source=localhost;initial catalog=adventureworks; Provider=sqlncli10;integrated Security=sspi; Auto translate=false; "; Before returning from this method, set the value of Dts.taskresult to indicate success or failure. To open Help, press F1.*/ Publicvoid Main () {//Todo:ADDYour code here//?????????????? String Sdatabaseid=(String) Dts.variables["DatabaseID"]. Value; String Scubeid=(String) Dts.variables["Cubeid"]. Value; String Smeasuregroupid=(String) Dts.variables["Measuregroupid"]. Value; String Spartitionid=(String) Dts.variables["PartitionID"]. Value; String sserver="localhost"; ConnectionManager cm=Dts.connections.ADD("MSOLAP100"); Cm. ConnectionString="Provider=MSOLAP.4;D ATA Source=Localhost;integrated Security=Sspi;initial Catalog="+Sdatabaseid; Microsoft.AnalysisServices.Server Aserver=new Server (); Aserver.connect (sserver); Microsoft.analysisservices.DatabaseAdatabase=AServer.Databases.FindByName (Sdatabaseid); Microsoft.AnalysisServices.Cube ACube=ADatabase.Cubes.FindByName (Scubeid); Microsoft.AnalysisServices.MeasureGroup Ameasuregroup=ACube.MeasureGroups.FindByName (smeasuregroupid); //generates an XMLA statement that processes a cube dts.variables["Isnetepresent"]. Value=true; Dts.variables["Xmla_script"]. Value="<Batch xmlns=\ "http://Schemas.microsoft.com/AnalysisServices/2003/Engine\ ">" +"<Parallel>" +"<Process xmlns:xsd=\ "http://www.w3.org/2001/Xmlschema\ "Xmlns:xsi=\ "http://www.w3.org/2001/XmlSchema-Instance\ "XMLNS:DDL2=\ "http://Schemas.microsoft.com/AnalysisServices/2003/Engine/2\" " +"Xmlns:ddl2_2=\ "http://Schemas.microsoft.com/AnalysisServices/2003/Engine/2/2\ "Xmlns:ddl100_100=\ "http://Schemas.microsoft.com/AnalysisServices/ -/Engine/ -/ -\ "xmlns:ddl200=\ "http://Schemas.microsoft.com/AnalysisServices/ ./Engine/ $\ "xmlns:ddl200_200=\ "http://Schemas.microsoft.com/AnalysisServices/ ./Engine/ $/ $\">" +"<Object>" +"<DatabaseID>"+Sdatabaseid+"</DatabaseID>" +"<Cubeid>"+Scubeid+"</Cubeid>" +"<Measuregroupid>"+Smeasuregroupid+"</Measuregroupid>" +"<PartitionID>"+Spartitionid+"</PartitionID>" +"</Object> <Type>ProcessFull</Type> <Writebacktablecreation>Useexisting</Writebacktablecreation> </Process> </Parallel> </Batch>"; Dts.taskresult=(int) scriptresults.success; Dts.taskresult=(int) scriptresults.success; } }}
4. Processing cube
5, scheduled to execute the package task.
SSAS dynamic processing partition (ii)