![]() Under Result Set changed this from None, to Single Row.ģ. Go into the Properties of the Execute SQL Task and on the General page configure it with the following:Ģ. Next we are going to configure our Execute SQL Task to get its data from our Query we created in steps above, by doing the following:ġ. Get XMLA Query for Process Data into Variableĥ. We gave our Execute SQL Task the following name:ġ. Now drag in an Execute SQL Task and put it below where you generated the Table Name.ġ. Name : XMLAScript_ProcessData_CurrentPartitionĤ. Next we are going to create a variable which will contain the entire XMLA Script generated in the previous step, by doing the following below.ġ. You can click on the Evaluate Expression to ensure that the expression is correct.ģ. From above you can see where we put in our Variable for the SSAS PartitionName highlighted in REDĤ. ‘ as XMLAScript_CurrentPartition_ProcessData”ģ. ![]() Now paste the following into the Expression Builder: This will then open the Expression Builder.Ģ. Then click on the Ellipses under Expression, which is on the far right hand side of the Variable details.ġ. NOTE : It has to be string because we will be passing the entire XMLA Script which we generated above.Ģ. ![]() ![]() Click on Add Variable and configure it with the following:ġ. We are going to create a new variable which will contain our dynamic query by doing the following:ġ. NOTE : The Variable query is used to dynamically create a query which can then be used in later steps within the SSIS package.Ģ.We do this by creating a variable query in our SSIS Package, which is explained on how to create this below. So now you have got your Partition Name and put it into a variable in SSIS Using the XMLA Script and variable to output it to a variable in our SSIS Packageġ. NOTE : In our example we had the variable name of SSASPartitionNameĢ. Drag in an Execute SQL Task, and set it up so that you can populate your variable with the output of the Proc or TSQL in Step 3 above.Ģ. Now go into your SSIS Project and into your Package.ġ. As you can see with the above script we are just getting the same name as what will be required in our XMLA Scriptġ. Set = convert ( varchar ( 28 ), ‘Fact Internet Sales – ‘ ) + convert ( varchar ( 4 ),+ CONVERT ( VARCHAR ( 4 ), ))ġ. In the final part we put in the table name and then put in our month value as well as the year Set = ( select RIGHT( REPLICATE ( ‘0’, 5 )+ CONVERT ( VARCHAR ( 2 ), ), 2 )) The second parts then adds a leading zero to your month number, this is because by default - it escapes or leaves out NOTE: You can use this below to get the current Year - SET = (SELECT DATEPART(YEAR,GETDATE()-1)) Set = ( SELECT DATEPART ( MONTH, GETDATE ()- 1 )) So here is the sample script below that we used:ġ. So next you need to create your Partition Name, and to do this we use TSQL to dynamically change the Monthly partition as we move through the months.ģ. As you can see from step 2a above we have got the PartitionID, this is what will change each month which we will need to process.Ģ. Generating your Partition Name using TSQL and putting it into a variable in SSISġ. This will now then script the details into an XMLA fileģ. NOTE: You can make any other changes you want in the Process Options Window In the Process Partition Window make sure that you change the Process Options to Process Data Now right click on the Partition and Select Process We drilled down to the following level below so that we could get to the partitionġ. In our example we have partitioned our cube so we want to only process the current month’s partition.Ģ. To generate the XMLA script, log into your SSAS instance and then browse down to the cube that you want to process.ġ. Below is the example based on the Adventure Works DW2008R2 Analysis Services Cube.ġ. With doing this in SQL Server 2012, I could then use the new functionality in SSIS as well as the additional reporting. So this has been updated to reflect this below. UPDATE () : I have found a way to complete the process of using the XMLA Script without having to put any files on the server or in a physical location. I found that doing this in SSIS was a quick and easy way in order to get my data processed. What I was looking to do, was to use XMLA and the Analysis Services Execute DDL Task in SSIS to dynamically process just a required partition that has already been created in SQL Server Analysis Services (SSAS). Generating XMLA Script and data from Cube
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |