In this post I will give a brief overview of some problems that arise during deployment of BAM activity updates. In particular when partitioning is enabled for the activity the error below can occurre upon regeneration of the view that union the partitions.
“All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists”
When using BAM often the first deployment contains just a few data items and milestones. But when users become aware of the possibilities of this technology they want to build richer queries. Therefore I frequently need to add data items and milestones in my existing activity definitions. Of course business users don’t want to lose the existing BAM data. So I can almost never redeploy the activity definition which would delete all the data.
Now the bm.exe tool provides me with an option to update an activity. In this case no data would be lost. But after playing around for a while with this ‘update-all’ feature I discovered some problems.
Let me explain what happens if one would like to add a data item to an activity which is already deployed and for which a view is created.
In my test environment I called my activity “BAMdeployment” and my view “BAMdeploymentView”. After first deployment the activity contains one data item StartData and two milestones Start and Stop. In the second deployment I want to add a StopData item to the activity and view. First I modified my spreadsheet and then exported the updated definition file.
Because I don’t want to lose data, I used the update-all command. Seems straightforward but I got the following error.
After looking around on the net I found some posts about this error and the solution is to first remove the view before running the update-all command. So after removing the view, the update-all completed without errors. The new data item StopData became visible in my view. Fine! But not for long.
The tests I did till now were done on a small set of data. But in a real scenario I probably have already a lot of instances of my activity in the BAMPrimaryImport database. Hence BAM archiving/partitioning becomes an issue.
After deploying an activity an SSIS package is created for partitioning and archiving the data. Here you can find the theory about this partitioning. Its all about performance.
When I tried to repeat the ‘update-all’ deployment strategy on my activity but now with partitioned tables I ran again into trouble. I got a new error.
Digging into this I ended up with a view in the BAMPrimaryImport database with the name bam_BAMdeployment_CompletedInstances. This view runs a UNION statement for every partitioned table. My custom view (the one defined in the spreadsheet) is afterwards build upon this one. This guarantee that all the data within my predefined activity window will be shown in the BAM portal.
Now the problem is that the partitioned tables are not updated with the new data item StopData. So the regeneration of the view bam_BAMdeployment_CompletedInstances fails. Only the first table bam_BAMdeployment_Completed has the new StopData column. While all the subsequent partitioning tables have not.
To fix this problem all new data items and milestones must be added manually in the partitioning tables. While there will never be more than 254 partitioned tables with every activity, this is definitely an exhausting and error prone job to do. So I wrote a script to help you a little bit. Just fill in the name of the activity and the new data item or milestone you want to add. Be sure that you use the same names as given in the definitionfile of the activity. The script will update every partitioning table.
All at once, the right order of actions to take
1/ Update the activity in the spreadsheet and export the definitionfile.
2/ When partitioned tables exist use the script to add the new items.
3/ Remove the views defined for the activity.
4/ Execute the bm update-all command.
-- Script to update the partition tables after adding a data item or milestone to the activity definition.
DECLARE @activityName nvarchar(128)
DECLARE @column nvarchar(100)
DECLARE @columnType nvarchar(100)
DECLARE @instancePartition sysname
SET @activityName = 'BAMdeployment'
SET @column = 'StopData'
SET @columnType = 'varchar(50)'
IF NOT EXISTS(SELECT ActivityName FROM bam_Metadata_Activities
WHERE ActivityName = @activityName)
RAISERROR (N'ActivityDoesNotExist', 16, 1)
DECLARE partition_cursor CURSOR LOCAL FOR
WHERE ActivityName = @activityName
ORDER BY CreationTime ASC
FETCH NEXT FROM partition_cursor
WHILE @@fetch_status = 0
EXEC ('ALTER TABLE ' + @instancePartition + ' ADD ' + @column + ' ' + @columnType)
FETCH NEXT FROM partition_cursor