I'm fairly new to SQL Packager and my client's requirements are as follows:

1/ We will initially ship our application with a new copy of the main database that is constantly changing.
I’m writing a custom DLL that will copy the Main database and only one table.
I need to know how to copy the database structure with only one table via the api.

2/ Later stages require us to only ship the one table (for users who already have the application).
Please supply instructions of how to overwrite the existing table with the new version via the api.

We do not have the credentials of the SQL server user - can this be over-ridden using the Microsoft MSI Installer (as believed)?

Thanks for your post. It is possible to wrap the SQL Toolkit API calls up into a Class Library project that inherits System.Configuration.Install.Installer. This would allow you to tightly integrate Toolkit with an MSI, in a way that allows you to display dialogs to the user that will let them specify server, database, and user names in the installer and pass the information to your installer class DLL.

This would work well for comparing an existing database or comparing a database on the customer's machine to a SQL Compare snapshot and synchronizing it, but it's not so practical for deploying Packager data.

The reason for this is because SQL Packager outputs an executable or project to create an executable only. This executable will also assume that the table does not already exist -- it will not use the typical 'if exist drop...' that SQL Server management tools use, so a SQL command would need to be run outside of the package anyway to drop the table.

If you only want to deploy a schema containing a single table, it may honestly be easier to script the table in SSMS and deploy the script in your Installer assembly as an embedded resource and then run it using ADO .NET.

The SQL Compare API is much more useful if you want to compare and synchronize a customer's schema to a reference snapshot file as part of a product installation.

Only by outputting the package as a C# project and hand-editing the embedded resource (.resx) files in Visual Studio. You can add a new batch before Create Table to do if exists drop table, but you have to remember to add 1 to the batch count section of the resource. I don't personally recommend hand-editing resources as they are serialized ExecutionBlock classes, but some customers have said they have done it successfully so it may be worth doing in a pinch.

Maybe this isn't the best example -- you don't need to specify a selectiondelegate to GetMigrationSql as there is an overload that doesn't require it.

The point is, to make sure that you set the session options by creating an EngineDataCompareOptions object and including DropConstraintsAndIndexes by Or-ing them to the default SqlOptions:
Dim opts as SqlOptions=SqlOptions.Default Or SqlOptions.DropConstraintsAndIndexes