Creating update scripts through Packager API

AvonWyss

I'm trying to write a small tool (to be used on our build server) which should create a migration script for two different versions of a database.

However, I would like to get the update scripts only; I'm not looking for a C# or EXE type project. Basically, I'm looking to get the same scripts one can review when using the UI during the packaging process.

Since the packaging API is quite small, I'm afraid that this functionality has not been exposed through the packaging API. So how can I do this? If the packaging API does not expose it, is there some Compare API and Data Compare API which can be used to achieve this?

Michelle T

The API should support outputting the update script - just set the OutputType to Script in the PackagerEngine constructor.

AvonWyss

Michelle Taylor wrote:

The API should support outputting the update script - just set the OutputType to Script in the PackagerEngine constructor.

In the API V7.0, OutputType had only the values Executable and Project. Thank you for pointing out the Script option, which I now have tried to use. However, I found it odd that it still required the template directory to be valid (what are the C# templates for when only creating scripts?).

Anyways, I still do not get a script file... here's my code snippet I'm using for testing (only schema migration, no data):

using (Database oldScripts = new Database()) {
	Console.WriteLine("Reading old schema version scripts...");
	oldScripts.Register(Path.GetFullPath(commandLine.OldPath), scriptDatabaseInformation, options);
	using (Database newScripts = new Database()) {
		Console.WriteLine("Reading new schema version scripts...");
		newScripts.Register(Path.GetFullPath(commandLine.NewPath), scriptDatabaseInformation, options);
		Console.WriteLine("Finding differences...");
		Differences differences = oldScripts.CompareWith(newScripts, options);
		Work work = new Work();
		work.BuildFromDifferences(differences, commandLine.GetDatabaseOptions(), false, outputPaths);
		Console.WriteLine("Creating migration script...");
		using (PackagerEngine packager = new PackagerEngine(@"C:\Program Files\Red Gate\SQL Packager 6\SQL Packager Code Templates\C#", Environment.CurrentDirectory, "test", work.ExecutionBlock, new ExecutionBlock(), OutputType.Script)) {
			packager.Package();
		}
	}
}

Michelle T

I'm investigating why the Script option isn't quite working at the moment - meanwhile, hopefully you can work around the problem by calling the command line from your code?

I'm sorry about the problem with the API.

BTW, your code seems to be missing a step - you need a line that looks a bit like:

work.GenerateSql(differences, null, Options.Default, false);

after your line which looks like:

work.BuildFromDifferences(differences, Options.Default, false,new WriteToFileOptions());

AvonWyss

Michelle Taylor wrote:

meanwhile, hopefully you can work around the problem by calling the command line from your code?

My understanding is that the SQL Packager (non-API) can only be run against databases, and not against scripts.

Michelle Taylor wrote:

BTW, your code seems to be missing a step - you need a line that looks a bit like:

Thanks for the hint, I'll try that.

AvonWyss

After adding the missin GenerateSql call, I now get an error message in a message box:

Output is not a Project or an Executable
Redgate.SQLPackager.Engine.PackagerException at d.b()

(I would have loved to attach a screenshot, but I cannot find the option to do so in this forum.)

Even worse, this message box is opened by the API, not by my code, which may make the application hang if run on a server without GUI (such as a buld server...) - that should certainly not happen in a non-visible API!

Can you please provide me a working sample to generate the SQL update scripts?

acaterino

I'm getting the same message that it is not a Project or Executeable.