Flush failed to run error when using DES

A quick one -

If you're using one of the DirectEventStream BAM API and you're seeing an error "Flush failed to run", check the connection string (and related permissions) to the database.

Happened three times to me recently (don't ask!) - all of which were related to configuration problems.

2 BizTalk Groups, 1 SQL Server

A couple of months ago we needed to deploy our existing solution to a new BizTalk group to serve a different client.

For various reasons we have decided to dedicate a BizTalk server for this client, so we don't have to worry about the impact of deploying changes to our existing clients etc, but to share the SQL server.

Obviously this is not the most ideal setup, but as the new client is in a completely different time zone to the existing ones, and both environment are not (yet) expected to have high volume of traffic, we could be quite confident that the SQL server can support both environments (one during the day, the other at night)

Anyway - BizTalk is quite happy to support that and you can easily configure it to use different databases on the same DB server using the configuration wizard and it all works quite well. nothing to report.

That is - until we wanted to deploy our BAM activities on the server.

For the sake of this discussion let's assume that when we've configured the first BizTalk group we left the default 'BAMPrimaryImport' database name for the BAM main database, and that for the second group we use 'BAMPrimaryImport2'.

Using bm.exe deploying BAM activities is usually a no-brainer and the tool takes all the hassle of creating tables, relationships, views, indexes etc as well as registering them all in the BAM repository.

However, the tool also generates SSIS job for each activity for purge-and-archive purposes; these jobs are simply named based on the activity name they support and are then deployed to the SSIS server (which is not partitioned by an entity such as database as far as I know), and this is where we faced a problem:

As the first group already had our BAM Activities deployed, the corresponding SSIS jobs were already deployed to the server with the generated names; when we came to deploy the activities for the second group, bm.exe went on to try and generate the SSIS packages using exactly the same (generated) names and failed saying such packages already exist.

As far as I know there is no way to control the names of these packages bm.exe would use and so we were a bit stuck.

Fortunately - changing the name of the existing jobs was fairly easy in the SQL management studio, and as they are not referred to by anything (other than a schedule to run in SQL) was fairly safe and so - what we did was to rename the packages created by the first group, so that bm.exe would create the packages required for the second group with the old name without failing.

bm update-all

We've been using BAM for a while now, and reached the point we need to add more data to our tracking profile.

No problem, we thought, as the 2006 version of bm.exe has this shiny new update-all feature that would solve all of our problems.

Either we or MS missed something big-time (and as much as I'd like to think it's not me I do realise most chances are that it is, so plese do correct me if I'm wrong) -

Our first attempt was very naive - we've simply opened our bam excel spreadsheet, added the activity item to the activity, updated the view we had to include the added field, saved the spreadsheet and used bm.exe update-all -definitionFile: hoping to get it all updated in the database.

Only that instead we got the following error:

View "" has a different definition XML from the one already deployed. The view cannot be updated.

At first we thought - DUH! - of course it is different - we just changed it! isn't that the whole point of update-all??

but further playing around revealed that the main point in that error is the word view - while bm.exe will quite happily add items to activities it is not at all happy to update views (I'm guessing the difference lies in the difference between add and udpate, but I'm not at all sure).

To prove this, we went back to the spreadsheet and manually returned the view to it's previous state - the one before we added the extra field; the activity still had the additional item though.

We re-executed bm.exe as before and this time it worked liked a charm. the new field was added to the activity (with all values being null, of course). the view, as you can expect, did not change.

This is good news as we can indeed add fields to our activities, but this is only half the solution as we can't update the views to see them; I don't know of any other way to update the views, surely there must be some solution other then mofifying them by hand? and so - as I've said - either I've missed something big time or MS has.

I found a newsgroups thread in which Ekta Aggarwal [MSFT] suggests deploying the view with a different name, which does work, but, at least in our case, changes everything that uses that view which is less than ideal to say the least (as well as leaving a trail of unwanted views that now have to be removed).

BTW - I'm not sure how this will work in conjuntion with archiving, but already I've spotted a further challange once the data has been partitioned -

if you run the DTS/SSIS job that partitiones the completed tables, and then try to update an activity you get a different error:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I suspect this is because an attempt is made to modify the tables while the views have a union to the partitioned versions.
The only way to overcome this (I think) is to manually remove the union from the views first, update the activity using bm.exe and then re-add the union making sure to add the extra field but this is much more manual editing of the views than I would like to do.