Config files specified in SQL Agent being overridden by design time package configuration

In work, our DEV and our UAT DB environments reside on the same box, but with their own SQL instances. We prefer to store SSIS packages on disk as they are a little (actually a lot) easier to manage that way. Since our DBs are on the same box we could, when configuring SSIS packages just copy the packages to two separate file locations for DEV and UAT and alter the design time package configuration to point to DEV.dtsconfig for one and UAT.dtsconfig for the other.

Rather than this however we attempted to just have the SSIS package in one location and override the default .dtsconfig file which was defined at design time by passing in the the environment specific .dtsconfig as part of the two (DEV & UAT) SQL agent jobs set-up to execute the package on schedule.  This can be done from the configurations tab in the job properties.

overiding-design-time-config

 

We expected the package to take its settings from UAT.dtsConfig as that was what was defined in the job but as we found this was not the case. This is because as of SQL 2008 SSIS packages load configurations in the following order:

  1. The utility first applies the design-time configurations.
  2. The utility then applies the run-time options that you specified on the command line when you started the utility.
  3. Finally, the utility reloads and reapplies the design-time configurations.

which meant the .dtsConfig specified in design time configuration was used. According to Behaviour Changes to Integration Services Features in SQL Server 2008 R2 on the MSDN site one can use /set to change design time settings but not the location of settings.

To get the .dtsConfig specified in the job to be the effective one, we needed to disable package configurations in design time. Not delete the existing one, but just disable it. After that the config file specified in the SQL Agent job was used.

Get all foreign keys and their cascade actions in SQL Server

Cascading deletes and updates are not for everyone and often they can cause problems or are better alternatives to using them. If you do use them and want to confirm what the update and delete actions are, the following query will do the trick to find all foreign keys in SQL Server along with their cascade actions: 

SELECT name, update_referential_action_desc, delete_referential_action_desc
FROM sys.foreign_keys

Restore database backup programmatically with TSQL

I recently had a need to test a migration script which converted product IDs from one ERP to those of another in a .net system local DB and needed a way to run the script and continuously and very quickly restore the original state of the DB.

I used the following TSQL to quickly restore:

—-restore backup
ALTER DATABASE DBNAME SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE DBNAME SET ONLINE
RESTORE DATABASE DBNAME FROM DISK = ‘c:\dbbackup.bak‘ WITH REPLACE
GO

The juicy bit is obviously the restore database command, however you will likely need to kill all processes to avoid getting an ‘Exclusive access could not be obtained because the database is in use‘ type of message. There are a couple of ways of doing this and one of these is to set the DB offline and then immediately bring it back online again just before we attempt the restore.

Can’t receive incoming remote connections to SQL Server database

I was having problems allowing some of my co-workers connect to the instance of SQL Server on my machine. Of course I was aware of the ‘Allow remote connections to this server’ option so I checked that first. It was already on, so I checked out Google to see what I could find.

After fiddling about with firewalls and ports, it turns out that I didn’t have the TCP/IP protocol enabled within the SQL Server Configuration Manager tool. This meant network based communication with any databases on my SQL Server instance was impossible.

As soon as I enabled this protocol and restarted the instance (via SQL Server Services section above) incoming connections were possible. Of course your problem may be caused by something else so check out Daniel Walzenbach’s article on MSDN: How to enable remote connections in SQL server 2008? It helped me a lot.

How to check if a default constraint exists in SQL Server

Perhaps your scripting rollout and rollback DDL SQL changes and you want to check for instance if a default constraint exists before attemping to drop it and its parent column.
Most schema checks can be done using a collection of information schema views which SQL Server has built in. To check for example the existence of columns you can query the information schema view for columns with a query which looks something like:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourtablename' AND COLUMN_NAME = 'yourcolumnname')

however none of the information schema views have anything in them for checking for a named default constraint. For that you need to use something like:

IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE TYPE_DESC =  'DEFAULT_CONSTRAINT' AND NAME = 'yourconstraintname')

or:

IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE TYPE = 'D' AND NAME = 'yourconstraintname')