Generating an SSISDB DACPAC

Creating a DACPAC is easy^*. Within SSMS, you simply select the database node, Tasks, Extract Data-Tier Application. I had need to get a database reference to the SSISDB for some reporting we were building out so I clicked along my merry way.

Set the properties you're interested in, which is really just file name

The next screen simply validates what you selected previously. It'd be nice if they took their cues from the SSISDeploymentWizard folks and built out your commandline options here but no worries.

And we wait for it to build our package, wait, what? Error?


TITLE: Microsoft SQL Server Management Studio ------------------------------ Validation of the schema model for data package failed. Error SQL71564: Error validating element Signature for '[internal].[check_is_role]': The element Signature for '[internal].[check_is_role]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[deploy_project_internal]': The element Signature for '[internal].[deploy_project_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[set_system_informations]': The element Signature for '[internal].[set_system_informations]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[start_execution_internal]': The element Signature for '[internal].[start_execution_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[stop_operation_internal]': The element Signature for '[internal].[stop_operation_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[validate_package_internal]': The element Signature for '[internal].[validate_package_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[validate_project_internal]': The element Signature for '[internal].[validate_project_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[check_schema_version_internal]': The element Signature for '[internal].[check_schema_version_internal]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[get_database_principals]': The element Signature for '[internal].[get_database_principals]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[get_space_used]': The element Signature for '[internal].[get_space_used]' cannot be deployed. This element contains state that cannot be recreated in the target database. Error SQL71564: Error validating element Signature for '[internal].[get_principal_id_by_sid]': The element Signature for '[internal].[get_principal_id_by_sid]' cannot be deployed. This element contains state that cannot be recreated in the target database. (Microsoft.SqlServer.Dac) ------------------------------ BUTTONS: OK ------------------------------

This was with the August release of SSMS 2016 which was beset with some defects so I thought I'd try SSMS 2014 but I got the same results. The best I could gather from searching about was that there was some validation occurring that you should be able to disable but I couldn't find any switches to throw in the GUI. But as I've already said Never trust the SSMS GUI you knew this. To the command line we go!

Generating a DACPAC from the command line

To generate a dacpac from the command line you need to find a version of SQLPackage.exe dir /s /b C:\sqlpackage.exe and I went ahead and made sure I used a version that matched my database instance.

I use named instances on my machines (DEV2012/DEV2014/DEV2016) so the following block shows my extraction of each SSISDB into a version named file.

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin>.\sqlpackage /Action:Extract /SourceDatabaseName:"SSISDB" /SourceServerName:localhost\dev2012 /TargetFile:"C:\Src\SSISDB_2012.dacpac"
Connecting to database 'SSISDB' on server 'localhost\dev2012'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Successfully extracted database and saved it to file 'C:\Src\SSISDB_2012.dacpac'.

C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin>.\sqlpackage /Action:Extract /SourceDatabaseName:"SSISDB" /SourceServerName:localhost\dev2014 /TargetFile:"C:\Src\SSISDB_2014.dacpac"
Connecting to database 'SSISDB' on server 'localhost\dev2014'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Successfully extracted database and saved it to file 'C:\Src\SSISDB_2014.dacpac'.

C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin>.\sqlpackage /Action:Extract /SourceDatabaseName:"SSISDB" /SourceServerName:localhost\dev2016 /TargetFile:"C:\Src\SSISDB_2016.dacpac"
Connecting to database 'SSISDB' on server 'localhost\dev2016'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Successfully extracted database and saved it to file 'C:\Src\SSISDB_2016.dacpac'.

DACPACs are handy as database references but getting the SSISDB extracted took more than a simple point and click.

^*for certain definitions of "easy"