I ran into an interesting SSIS issue that I thought I'd share in hopes that it may save someone from bruising their head after repeatedly banging it on the desk like I did.
I was trying to setup what I believe is referred to as "indirect configuration" in SSIS. This is where you store your configuration in some repository like a database or a file, then store the location of that repository in an environment variable and use that to configure the connection to your configuration repository. In my specific situation, I was using a SQL database. I had this all working, but for reasons I'll not bore you with, I had to move my SSIS development to a new VM last week.
When I got my new VM, I set about creating a new package. I finished up development on the package and started setting up configuration. I created an OLE DB connection that pointed to my configuration table then went through the configuration wizard to have the connection string for this connection set through my environment variable. I then went through the wizard to set another property through a value stored in the configuration table. When I got to the point where you select the connection, my connection wasn't in the list:
As you can see in the screen capture above, the ConfigurationDb connection isn't in the list of available SQL connections in the configuration wizard. Strange. I canceled out of the wizard, went to the properties for ConfigurationDb, tested the connection and it was successful. I went back to the wizard again and this time ConfigurationDb was there. I completed the wizard then went to test my package. Unfortunately the package wouldn't run, I got the following error:
Unfortunately, googling for this error code didn't help much as none of the results appears related to package configuration. I did notice that when I went back through the package configuration and tried to edit a previously saved config entry, I was getting the following error:
I checked the connection string I had stored in my environment variable and noticed that indeed, it did not have a provider name. I didn't recall having included one on my previous VM, but I figured I'd include it just to see what happened. That made no difference at all.
After a day and a half of trying to figure out what the problem was, I'm pleased to report that through extensive trial and error, I have resolved the error.
As it turns out, the person who setup this new VM for me named the server SQLSERVER2008. This meant my configuration connection string was:
Initial Catalog=SSISConfigDb;Data Source=SQLSERVER2008;Integrated Security=SSPI;
Just for the heck of it, I tried changing it to:
Initial Catalog=SSISConfigDb;Data Source=(local);Integrated Security=SSPI;
That did the trick! As soon as I restarted BIDS, I was able to run the package with no errors at all. Crazy.
So, the moral of the story is, don't name your server SQLSERVER2008 if you want SSIS configuration to work when using SQL as your config store.
