BAM SSIS Error: "RegenerateViews_ViewDefinitionTooLong"

Problem:

If you configure your BAM_DM_<ActivityName> SSIS packages execute very frequently in SQL agent job (e.g: every day) and the Online Window of the activity data is set to a long time (e.g: 6 months), then you may see thousands of partition tables get generated and meet the following errors:

 "EXEC [dbo].[bam_Metadata_SpawnPartition] N'JobOrde..." failed with the following error: "RegenerateViews_ViewDefinitionTooLong". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1).

 

 

This is because that too many partition tables causes the merged SQL query has exceeded the default buffer size used by bam_Metadata_RegenerateViews stored procedure (default is 48K). If the online window cannot be changed to remove some partition tables, a work around is to modify the stored procedure and add more buffers:

 

Recommendation:

1. Do NOT configure your BAM SSIS packages execute too frequently. If online window is 6 months, then running the packages weekly should be appropriate setting.

2. Backup databases before doing any customized changes.

 

Best regards,

WenJun Zhang