Blog » Via Cathy Dumas
In my post on doing a ProcessAdd in AMO, I promised something ugly in Integration Services to do a ProcessAdd. Actually I just tried it and it’s not too bad. Aesthetically, it’s as pleasing as using AMO (which is to say it’s a little painful but once you figure it out it’s maintainable). Here is how to do the same ProcessAdd with Integration Services.
ProcessAdd is one of the tabular processing operations that can be performed using the Analysis Services Processing Task, so we will use it to load the data. The major problem with using this task in SQL Server 2012 is that there is no way to make a connection with a project in your existing solution. You therefore cannot deploy both the tabular model and the IS package together in a single solution and have the connection string get updated. Instead, if you want to use the graphical interface for the Analysis Services Processing Task, you must first connect to a live server, manipulate the model, then change the connection string manually at deployment time to point to the production server. Not ideal, but not a total blocker.
That said, let’s do the ProcessAdd.
- From SSDT, create a new Integration Services project.
- Right click in the Connection Managers pane, add a new connection to your tabular server.
- Drop the Analysis Services Processing Task into the Control Flow area.
- Double-click to configure the task.
- In the dialog box that appears, click Processing Settings.
- Set the Analysis Services connection manager to the connection you created in step 2.
- Click the Add button. You see a dialog like this one:
- As in AMO, you must drill down from the cube level to find the measure group and partition you want to process. In our case, let’s go ahead and add more data to that Partition 1800 we created. Let’s select that partition as pictured below, then click OK.
- Now we’re back in the Processing Settings dialog, with Partition 1800 selected for processing. Set the Process Options for this partition to Process Add, then click the Configure link that appears.
- Lo and behold, a dialog appears that allows you to set the out of line query binding for the Process Add. I think we are up to the year 1803, so let’s set the query binding to SELECT [dbo].[DemographicFacts].* FROM [dbo].[DemographicFacts] WHERE ([Year] = 1803), as pictured below:
- Click OK on all dialogs, then press F5 to execute the package. Processing should succeed, and you can now go and verify from SSMS that the data was loaded successfully.
See? Not so bad. Where it gets uglier is when you do incremental processing using the ProcessData + ProcessRecalc pattern, as I will show in a future post.