CODEWITHSUNDEEP
sql-server-data-toolsintegrity
Ivan Koshelev
Ivan Koshelev

Reputation: 4260

How to add SQL-based integrity checks to SSDT (.sqlproj)?

I'm using Microsoft SSDT to version DB schema. I have a script to autogenerate some triggers and i need to make sure that we don't forget to run it when tables change. I added a part to PostDeploy script, which takes pristine trigger definitions, compares them to existing ones and raises error if they don't match, but the error doesn't abort PostDeploy script, I don't even see it or preceeding PRINT message (I even tried throwing error at the beginning of script). I suspect this is because script runs in SQLCMD mode. Is there any way I can force this script to fail and attract users attention to the reason?

Im also considering adding this integrity check to build of SSDT project itself, assuming I can somehow run SQL against the .dacpac. Alternatively, I'm looking at SSDT unit tests, but it looks like they require a separate DB to work.

What is an idiomatic way to add such integrity check to SSDT, preferably without requiring access to an existing DB?

Upvotes: 0

Views: 64

Answers (1)

Ivan Koshelev
Ivan Koshelev

Reputation: 4260

We ended up going with Unit Test project ran in the build pipeline. It wasn't a smooth setup, but it works.

Upvotes: 0

Related Questions