Continual "Error 18456" problem solved - the quick and dirty version

Looking at the log today for my local SQL Server Express 2008 R2, I noticed quite a lot of these:

Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>] 
Error: 18456, Severity: 14, State: 38.

This was happening once a minute, and made it difficult for me to look through the log for what I really needed to see.   None of the databases were offline or otherwise disabled.  So I asked myself, "what database is it trying to open?"

Enter SQL Profiler and the event called "User Error Message" - under "Errors and Warnings".  That provided me with this info:

Cannot open database "SQLdmRepository" requested by the login. The login failed.

Hmmm...  To be completely and embarrassingly honest, I didn't know why that was happening.  And since it was only a local server that I used only occasionally and is not critical, I simply created a new database called "SQLdmRepository", which stopped all those log entries.  

Obviously, it doesn't fix the real problem.  I could dig into this more and solve it for good, but the immediate need was to read my log without all that extraneous stuff.

Later that day...

OK, so I couldn't help it.  I had to find out.  It turns out that there is a service running on my machine for Idera SQL Diagnostic Manager.  When I uninstalled and reinstalled SQL Server Express last month, I inadvertently overlooked its database.  Now, however, my version of SQL Server is not compatible with the version of Idera SQL Diagnostic Manager on my machine.  Live and learn...

Dynamic SSIS package configuration location at run time

I love package configurations.  They really simplify deploying SSIS packages. 

For this example, a SQL Server package configuration is used.  We have a package that imports data nightly from a text file into a SQL Server table.  The package has been created on my local server.  Here are the connection strings for the connections used by the package:

1.  Flat File Connection:  "myFlatFile"

c:\ftp\mydata.txt

2.  OLEDB Connection:  "MyDB"

Data Source=(local);Initial Catalog=MyDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-DemoPackage-{86D8F799-4C46-4C1F-A0DD-50908FA54A04}(local).MyDB;Auto Translate=False;

3.  OLEDB Connection:  "ConfigDB"

(this is entered into the Expressions:  Connection String in the Properties of the connection)"Data Source=" + @[System::MachineName]   + ";Initial Catalog=SSISConfigurations;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;"

Now, for the package configuration, right-click on the Design area of your package and choose "Package Configurations".  The Package Configurations Organizer will appear.  Check the box for "Enable package configurations" and click Add.  The wizard will appear.  Here are the values for this demo:

On the next screen, expand each of your connections and check "Connection String" for each (for the purposes of this demo; you may want to choose additional elements as well). 

The last step is to name and save your configuration. 

Now - in the SSISConfigurations table in my local SSISConfigurations database, here are the values.  Note that in the ConfiguredValue column for the ConfigDB package connection, my machine name was put into the connection string by the dynamic connection string I used above.

When you deploy this package to another server (let's call it "SQLPROD"), all you have to do is import the data from the SSISConfigurations table into a database/table of the same name on SQLPROD.  Then you simply change the strings on SQLPROD to reflect the server name, like so:

And that's it!