There is a bug in SSDT that causes database options to be generated for SQL Server 2012 even if the target platform is set to SQL Server 2008.
I observed it in a such scenario:
sqlpackage.exe was used to generate publish script to create new database. Sqlpackage target is set to a dacpacfile (just empty model without any tables).
The problem is that publish script has few options set which are available only in 2012. The options are:
But when I generate publish script directly from VisualStudio and specify target database as real 2008 database (not dacpac) then resulting publish script is correct. It doesn't contains above options.
This is already registered as a connect bug:
https://connect.microsoft.com/SQLServer/feedback/details/1049478/ssdt-generates-2012-option-for-target-2008-when-target-is-a-dacpac
I have implemented a deployment contributor that removes such invalid options. The code can be downloaded from following link:
https://gallery.technet.microsoft.com/scriptcenter/SSDT-contributor-to-remove-05488cf9
where should this be included? can you provide step by step instruction to use this code
ReplyDeleteHi,
ReplyDeleteYou can read more details here: https://msdn.microsoft.com/en-us/library/dn306642(v=vs.103).aspx
General instruction is like this:
1. Build the code as c# project
2. Put dll to: C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\Extensions
3. Call it during deployment like this:
C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe /Action:Script /SourceFile:"D:\Databases\ATC.dacpac" /TargetFile:"D:\Databases\Previous\ATC.dacpac" /TargetDatabaseName:ATC /OutputPath:"D:\Databases\ATC_Create.sql" /Profile:D:\Databases\MainProfile.publish.xml /p:AdditionalDeploymentContributors=DeploymentContributor.CompatibilityFor2008Modifier;
How the heck do you debug these things? I'm working on one right now, and I've tried attaching to another VS process, and starting sqlpackage for the start action. Neither have got me anywhere. Any tips?
ReplyDeleteI have had a unit test that was loading dacpac and comparing it with other dacpac so I could debug it this way.
ReplyDeleteYou can try to find some examples here: https://blogs.msdn.microsoft.com/ssdt/2013/12/23/dacfx-public-model-tutorial/
Similarly, the sqlpackage publish script generated a USE statement which is only available for non Azure DBs but we are deploying to an Azure DB. Is there a setting where I can get rid of the USE statement in the deploy script?
ReplyDelete