SqlPackage.exe & Pre/Post scripts

Question

Hello SSDT Team!

We're working with "Database Project" for many many years and using it to update production database and I'd like to say the new tool is dramatically better in the all areas! Starting from designers and ending up with deploying. Great Job!

We're now migrating our ifrastructure to the new SSDT and (as always with new product :) ) we've got few issues:

Issue 1:

1. We have a new Database Project that contains database schema and post script that populates initial data.

2. To generate schema upgrade we use SqlPackage.exe, it compares dacpac we're getting after the build and production database and generates the upgrade script.

The problem is that generated upgrade script contains post deployment script. We would like not to include post script in our upgrade scripts as we use it only during database screation. So, it would be great if you add an option to ignore pre/post scripts in to the SqlPackage.exe. The only workaround I see at the moment for our problem is to have 2 projects. 1 full project with schema & post script and second only with schema. 1st we will use to create initial database and second for the upgrades. That is possible but not very elegant :)

Issue 2:

In the new Database Project we use CLR. And we have a tests project that references the new database project. The reference works fine if you build from the Visual Studion but build fails if you build using MSBUILD. It looks like MSBUILD simply do not add a reference to the CLR assembly compiled from the database project when building a tests project.

Issue 3:

In our scenario, on the production we have many databases with same schema. During upgrade we have to generate an upgrade script once and apply it the to the every database. The problem is that generated upgrade script has USE statement. So, currently we need to postprocess generated script and cut some things. We had the same issue with Data Dude 2010, so, it is not something new. As in the 1st issue it would be great to control this behaviour through the command line parameters. Same problem for the SQLCMD variables. In other words what we need to get from the SqlPackage.exe is only real schema upgrade statements. No usings, no SQLCMD variables and nothing else. Just schema upgrade statements.

Thanks a lot for reading all this :)

Regards

Yahor Sinkevich

---

Best Regards -- Yahor Sinkevich

Answer 1

Hello SSDT Team!

We're working with "Database Project" for many many years and using it to update production database and I'd like to say the new tool is dramatically better in the all areas! Starting from designers and ending up with deploying. Great Job!

We're now migrating our ifrastructure to the new SSDT and (as always with new product :) ) we've got few issues:

Issue 1:

1. We have a new Database Project that contains database schema and post script that populates initial data.

2. To generate schema upgrade we use SqlPackage.exe, it compares dacpac we're getting after the build and production database and generates the upgrade script.

The problem is that generated upgrade script contains post deployment script. We would like not to include post script in our upgrade scripts as we use it only during database screation. So, it would be great if you add an option to ignore pre/post scripts in to the SqlPackage.exe. The only workaround I see at the moment for our problem is to have 2 projects. 1 full project with schema & post script and second only with schema. 1st we will use to create initial database and second for the upgrades. That is possible but not very elegant :)

The way I get around this is have a SqlCmd variable called "DeployData" with a default value of "No". In my Post Deployment script I have:

IF ('$(DeployData)' = 'Yes')
BEGIN
    PRINT '******Data being deployed******';
    :r .\datafile1.sql
    :r .\datafile1.sql
    -- etc....
END
ELSE
BEGIN
    PRINT '******No data to be deployed******';
END

Issue 2:

In the new Database Project we use CLR. And we have a tests project that references the new database project. The reference works fine if you build from the Visual Studion but build fails if you build using MSBUILD. It looks like MSBUILD simply do not add a reference to the CLR assembly compiled from the database project when building a tests project.

Not sure about this one as I don't use CLR. Sorry. Hopefully someone else here knows.

Issue 3:

In our scenario, on the production we have many databases with same schema. During upgrade we have to generate an upgrade script once and apply it the to the every database. The problem is that generated upgrade script has USE statement. So, currently we need to postprocess generated script and cut some things. We had the same issue with Data Dude 2010, so, it is not something new. As in the 1st issue it would be great to control this behaviour through the command line parameters. Same problem for the SQLCMD variables. In other words what we need to get from the SqlPackage.exe is only real schema upgrade statements. No usings, no SQLCMD variables and nothing else. Just schema upgrade statements.

Hmmm....difficult one. Might be worth posting something to http://connect.microsoft.com/sqlserver/feedback about that one. I can't think of a way around it right now without some post-processing, though I stand to be corrected.

---

ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 2

Hey Yahor,

Thanks for your detailed feedback on several SSDT/DACFx items above.

With regards to the first issue, Jamie is correct in pointing out that a way of accomplishing your scenario is to have a conditional include of insert script files in your Post-Deployment script that depend on the value of a SQLCMD variable. You can specify/override SQLCMD variables when publishing a dacpac via SqlPackage, so this should enable you to control the inclusion. Issue number 3 is also an interesting scenario and good feature suggestion. We have seen a lot of requests in the space of environment-specific deployments and this definitely falls into that category.

I will be sure to take note of these two items and pass them on to the rest of the team, but just to make sure the information is not lost, I would follow Jamie's suggestion and file a Connect bug for these two feature asks! This will help us keep track of the issue and make sure they are appropriately addressed.

Hopefully someone from the project system will jump on and take a look at issue number 2!

Thanks,

Adam

---

Adam Mahood - Program Manager - Data-Tier Application Framework (DACFX)/SQL Server Data Tools

Answer 3

Hi Adam,

It is really pleasurably to see the feedback is not lost :)

Keep going!

And guys,  You're Awesome!

Regards

Yahor

---

Best Regards -- Yahor Sinkevich

Answer 4

Hi Yahor, I've investigated issue 2 and I can confirm this is a bug in our MSBuild task. Sorry you ran into this. We are actively investigating this and will triage it against other work items.

For now, I believe a workaround is to set the BuildingInsideVisualStudio flag to true:

msbuild TestProject1.csproj /t:Rebuild /p:BuildingInsideVisualStudio=true

Please let me know if this gets around the problem for now.

Thanks,

Kevin