Scheduling BAM Packages – failure in ‘Set global connection string variables’

I recently hit an issue while configuring a sql job to process my BAM cubes by running the BAM_AN_* packages with the following error:
Code: 0x00000000
Source: Set global connection string variables
Description: Object reference not set to an instance of an object.

I later found that this was due to the user under which the SQL Server Agent Service runs not having enough permissions on SQL Server. I changed the user to a sql server service account and I no longer got this error while running the sql job. Alternatively, you can also run this service under the network service account if your BAM databases are on a different server.

“the binary code for the script is not found…”

When you deploy a BAM activity, it creates at least one SSIS package for the activity which will create partition tables and move any activity instances within the archive window to the BAMArchive database.  If you’ve created any BAM views which create an OLAP cube, an SSIS package will also be created to move the data between the relational tables and the cube as well.  These SSIS packages are stored on the SQL server, in the MSDB database instance on the default server instance.

So SSIS needs to be installed on the SQL Server not the BizTalk server.  But what does need to be installed on the BizTalk server? See below…

You need to install:
1) Client Tools SDK
2) Client Tools Backwards Compatibility (this is critical).
+ the management tools as shown above.

I recently hit this issue while trying to run the BAM_AN_ SSIS package in SQL Server 2008 in order to load the activity data to the cube and got “the binary code for the script is not found”. What this means is the SSIS packages created by the BM utility has not been compiled. If you try and open the resulting dtsx package in Visual Studio, you are likely to see a dialog indicating that the package is corrupt. Lastly I had to  undeploy the existing BAM view and redeploy and the error went away!

Below are the screens I saw before and after installing the missing components.



Hope it helps someone!