Save and restore BizTalk BAM activities

Posted on by

The BAM is a very useful tool that my clients appreciate more and more. The most interesting aspect is that it is a framework on which you can build reports and dashboards using Analysis Services, the OLTP database or web services.

When you implements a sets of very complex activities and views, may become difficult to update the structure of activities adding or removing columns. Update the schema of the activities often requires its redefinition that causes the loss of data already present.

With my team we have created a set of stored procedures based on SQL Server in order to save and restore the data.

Below I have reported some examples.

 

Database Creation:

 

CREATEDATABASE[BAMPrimaryImportUtilities]

GO

 

 

Procedure that saves the data:

 

USE[BAMPrimaryImportUtilities]

GO

 

CREATEPROCEDURE[dbo].[SaveActivity]

       @activityNameNVARCHAR(128)

AS

BEGIN

 

       SETNOCOUNTON;

 

       DECLARE@resultTABLE (actionPartVARCHAR(200),valuePartVARCHAR(200));

       DECLARE@sqlStatementNVARCHAR(2000);

 

       IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[bam_’+@activityName+‘_Completed]’)ANDtypein(N’U’))

       BEGIN

             SET@sqlStatement=‘DROP TABLE [dbo].[bam_’+@activityName+‘_Completed]’

 

             EXECUTEsp_executesql@sqlStatement;

       END

 

       IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[bam_’+@activityName+‘_CompletedRelationships]’)ANDtypein(N’U’))

       BEGIN

             SET@sqlStatement=‘DROP TABLE [dbo].[bam_’+@activityName+‘_CompletedRelationships]’

 

             EXECUTEsp_executesql@sqlStatement;

       END

 

       SET@sqlStatement=‘

                    SELECT

                           *

                    INTO

                           dbo.[bam_’+@activityName+‘_Completed]

                    FROM

                           BAMPrimaryImport.dbo.[bam_’+@activityName+‘_Completed]’;

                                              

       EXECUTEsp_executesql@sqlStatement;

       INSERTINTO@result(actionPart,valuePart)VALUES (‘bam_’+@activityName+‘_Completed: ‘,CONVERT(VARCHAR,@@ROWCOUNT));

 

       SET@sqlStatement=‘

                    SELECT

                           *

                    INTO

                           dbo.[bam_’+@activityName+‘_CompletedRelationships]

                    FROM

                           BAMPrimaryImport.dbo.[bam_’+@activityName+‘_CompletedRelationships]’;

                                              

       EXECUTEsp_executesql@sqlStatement;

       INSERTINTO@result(actionPart,valuePart)VALUES (‘bam_’+@activityName+‘_CompletedRelationships: ‘,CONVERT(VARCHAR,@@ROWCOUNT));

 

       SELECT*FROM@result;

 

END

 

 

 

Procedure that restores the data

CREATEPROCEDURE[dbo].[RestoreActivity]

       @activityNameNVARCHAR(128)

AS

BEGIN

 

       SETNOCOUNTON;

 

       DECLARE@resultTABLE (actionPartVARCHAR(200),valuePartVARCHAR(200));

       DECLARE@columnListVARCHAR(4000)=”;

       DECLARE@columnVARCHAR(256);

       DECLARE@sqlStatementNVARCHAR(4000);

      

       — Column List

       DECLAREcurColumnListCURSORFOR

             SELECT

                    PrimaryImportInfo.COLUMN_NAME

                   

             FROM

                    BAMPrimaryImport.INFORMATION_SCHEMA.COLUMNSPrimaryImportInfo

                   

                    INNERJOINBAMPrimaryImportUtilities.INFORMATION_SCHEMA.COLUMNSSavedInfo

                    ONPrimaryImportInfo.TABLE_NAME=SavedInfo.TABLE_NAME

                    ANDPrimaryImportInfo.COLUMN_NAME=SavedInfo.COLUMN_NAME

             WHERE

                    PrimaryImportInfo.TABLE_NAME=‘bam_’+@activityName+‘_Completed’

 

             ORDERBY

                    PrimaryImportInfo.ORDINAL_POSITION;

                   

       OPENcurColumnList;

       FETCHNEXTFROMcurColumnListINTO@column;

      

       WHILE@@FETCH_STATUS= 0

       BEGIN

             SET@columnList=@columnList+‘[‘+@column+‘],’+CHAR(13)

            

             FETCHNEXTFROMcurColumnListINTO@column;

       END

      

       CLOSEcurColumnList;

       DEALLOCATEcurColumnList;

      

       SET@columnList=LEFT(@columnList,LEN(@columnList)–2)+CHAR(13);

      

       — Insert Statement

       SET@sqlStatement=‘SET IDENTITY_INSERT BAMPrimaryImport.dbo.[bam_’+@activityName+‘_Completed] ON;

                                       

                                        INSERT INTO BAMPrimaryImport.dbo.[bam_’+@activityName+‘_Completed]

                                        (‘+@columnList+‘)

                                        SELECT

                                        ‘+@columnList+‘

                                        FROM

                                               BAMPrimaryImportUtilities.dbo.[bam_’+@activityName+‘_Completed];’;

                                              

       EXECUTEsp_executesql@sqlStatement;

       INSERTINTO@result(actionPart,valuePart)VALUES (‘bam_’+@activityName+‘_Completed: ‘,CONVERT(VARCHAR,@@ROWCOUNT));

 

       SET@sqlStatement=‘INSERT INTO BAMPrimaryImport.dbo.[bam_’+@activityName+‘_CompletedRelationships]

                                        SELECT

                                               *

                                        FROM

                                               BAMPrimaryImportUtilities.dbo.[bam_’+@activityName+‘_CompletedRelationships]’;

                                              

       EXECUTEsp_executesql@sqlStatement;

       INSERTINTO@result(actionPart,valuePart)VALUES (‘bam_’+@activityName+‘_CompletedRelationships: ‘,CONVERT(VARCHAR,@@ROWCOUNT));

 

       SELECT*FROM@result;

 

END

 

 

 

 

The previous procedures represent a starting point. You can enrich them, working on views, for example, you can save all the activities of a view by accessing the table “BAMPrimaryImport.dbo.bam_Metadata_ActivityViews”.

In addition, you can automate the deployment by implementing a console application that calls the stored procedure.

Something like that:

 

clip_image002[4]

 

Leave a comment