sql server driver scripts seem awkward, is there a better way to batch sql scripts?

Question

I've been writing 'driver' sql scripts (is that the correct name?) to run multiple sql 'sub' scripts in order, writing errors to log file etc. My Oracle version seems to be more or less ok, but my sql server one I'm struggling with. Specifically, when a script fails, I'm getting line numbers bigger than files for the error location. Is there a best practice 'template' for this? Should I be using dacpacs instead? Any tips appreciated here. Thanks.

SET NOCOUNT ON
GO

PRINT 'upgrading something'

echo 'do this first'
:r  'this.sql'
echo 'now do that'
:r  'that.sql'

PRINT 'done'
GO

Answer 1

For the application we were building, we used SQL DMO and wrote a C# tool that used DMO objects and library to run all the scripts. We wrote all the scripts and put in place a style guide and sql guide. All scripts were re-runnable (idempotent). This included creating database, user permissions, roles, schema, procs, everything. Basically it would spin up a complete instance from scratch. This worked out well, but it was a lot we had to manage and take on.

DacPacs seems to hide some of the "hand-crafty-ness" for you.

Should you be using Dacpacs? If you like the way Dacpacs encapsulate many objects into a single file and allows for idempotent deployments, maybe it's a good choice.

The other way is handcrafting scipts and using running them natively or using a library such as SQL DMO. I think either will work.