How to add DML scripts to DacPackageExtensions.BuildPackage(...)

[lucas-mv]

Greetings,

I am having a hard time understanding how to generate a .dacpac file with post execution scripts with the DacFx and DacPackageExtensions.

I can't seem to get a valid .dacpac output that contains my DML scripts.

Here is my base .dacpac generating class, the rest of the project is zipped and attached here

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using Microsoft.SqlServer.Dac.Model;
using Microsoft.SqlServer.Dac;
using System.Text;
using static System.Environment;
using Microsoft.SqlServer.TransactSql.ScriptDom;

namespace Pokemon.DacpacConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Begining scripts generation...");
            var dacpacScripts = GenerateDacCreateScriptList();
            Console.WriteLine("Finished generating scripts.");

            var tSqlModelOptions = new TSqlModelOptions
            {
                AnsiNullsOn = true,
                Collation = "Latin1_General_CI_AI",
                CompatibilityLevel = 150,
                QuotedIdentifierOn = true
            };

            var outputFilename = "pokemon.dacpac";

            Console.WriteLine("Begining .dacpac generation...");
            using (var tSqlModel = new TSqlModel(SqlServerVersion.Sql150, tSqlModelOptions))
            {
                foreach(var script in dacpacScripts)
                    tSqlModel.AddObjects(script);

                var postDeployScripts = GenerateDacPostDeploymentScript();
                foreach(var script in postDeployScripts)
                    tSqlModel.AddOrUpdateObjects(
                        inputScript: script.Value,
                        sourceName: script.Key,
                        options: new TSqlObjectOptions {
                            AnsiNulls = true,
                            QuotedIdentifier = true
                        });
                                
                if (File.Exists(outputFilename))
                    File.Delete(outputFilename);
                    
                DacPackageExtensions.BuildPackage(
                    outputFilename, 
                    tSqlModel, 
                    new PackageMetadata { Name = outputFilename, Version = "poke-1.0.0" }, 
                    new PackageOptions {  });
            }
            Console.WriteLine("Finished generating .dacpac, check output folder.");
        }

        private static List<string> GenerateDacCreateScriptList()
        {
            var tableFiles = Directory
                .GetFiles(
                    Environment.GetEnvironmentVariable("TABLE_SCRIPTS_PATH"), 
                    "*.sql",  
                    new EnumerationOptions() { RecurseSubdirectories = true, IgnoreInaccessible = true });

            var sqlScripts = new List<string>();
            
            foreach(var file in tableFiles)
            {
                Console.WriteLine("Found table file: " + file);
                sqlScripts.Add(File.ReadAllText(file));
            }

            return sqlScripts;
        }

        private static IDictionary<string, string> GenerateDacPostDeploymentScript()
        {
            var postScriptFiles = Directory
                .GetFiles(
                    Environment.GetEnvironmentVariable("POST_EXECUTION_SCRIPTS_PATH"), 
                    "Insert*.sql",  
                    new EnumerationOptions() { RecurseSubdirectories = true, IgnoreInaccessible = true });

            var scripts = new Dictionary<string, string>();

            foreach(var file in postScriptFiles)
            {
                Console.WriteLine("Found post execution script file: " + file);
                scripts.Add(file, File.ReadAllText(file));
            }

            return scripts;
        }
    }
}

[ErikEJ]

It should just be a single script: https://github.com/GoEddie/DacpacMerge/blob/master/src/MergeEm/Program.cs#L112
If you have multiple linked scripts, see this: rr-wfm/MSBuild.Sdk.SqlProj#9