We’ve recently released a new feature in Red Gate SQL Packager 6 that enables any script to be packaged, not just those created by the tool. A side-effect of this is that it makes the tool far more powerful and flexible.

Earlier versions of SQL Packager worked well for the majority of deployments, but very occasionally the sql script generated would require some modification, which wasn’t a practical option prior to the v6 release. As product manager for SQL Packager I received a fair amount of examples of such amendments. In no particular order here is a selection of issues encountered by our users:

• There are different users and login mappings (users and logins are often customer-specific) between the development and production environments.
• Users want to specify their own default passwords for logins.
• Often it is useful to have the schema and data portions of the deployment script in a single transaction, rather than two.
• Some databases have a version table that needs to be checked prior to running a script and updated after the script has been run successfully.
• If a NOT NULL column has been added to a table that contains data, it is necessary to specify a default value.
• It is always useful to add customer pre and post deployment scripts, either in or out of the transaction.
• It can be useful to add IF NOT EXISTS / DROP statements for tables that may or may not be in the target, to ensure the scripts work.
• Often there are different filegroups in the ‘test’ environment and the production environment.
• Some databases have complex dependencies that require manual reordering.
• It can be useful to add database-level configuration statements, such as ALTER DATABASE SET TRUSTWORTHY
• As part of a deployment it is sometimes necessary to massage data into a different format.
• SQL Packager synchronizes all the data in a table. Often a subset of data in a table needs synchronizing.
• Synchronizating data in tables that don’t have natural comparison keys is support in SQL Data Compare by not SQL Packager
• Additional refactorings or database changes can be required for a complete deployment.

As it is now possible to read in a user-specified SQL Script into SQL Packager 6, all the above requirements can now be met, as the script can be easily amended by the user prior to the creation of a packaged executable. It is even possible to mix and match scripts generated by SQL Compare, SQL Data Compare, SQL Refactor and SQL Packager to create an infinitely versatile and comprehensive deployment script.

More information for SQL Packager can be found at http://www.red-gate.com/products/SQL_Packager/index.htm