wagnerblog.com

Here is an odd situation I came across today. This probably wont happen to many other people because it is so specific to certain network settings. But if it happens to you, maybe I can save you the 4 hours I spent on hold today with MS SQL Server PSS… whilst paying $250 for the privilege.

I have an SSIS package that runs perfectly well in my Integration Studio dev environment, locally. Once it was published to a staging server it kept failing with an assortment of errors pointing to login credential issues. Nothing I tried seemed to help.

If you are coming to SSIS from SQL Server 2000 DTS - boy are you in for a surprise. Some things are way nice, but others - like the configuration management aspects are tough to get used to. As it stands, SSIS Packages maintain various settings in configurations once they are deployed. The first clue to that aspect is when you try to run a package on the server and find that the config string doesn’t contain the password anymore. Say hello to the MS Trustworthy Computing initiative. To the unsuspecting developer this little diddy can be a real doozy. Ok enough with the funny language. Because this wasn’t funny, especially after holding the phone fo four hours waiting for PSS to answer.

Ostensibly the issue had to do with the configuration file used by the package. But even recreating the file and rebuilding / redeploying the package didnt make any difference. I found that using dtexec to run the package from the command line was very useful because it provided immediate feedback. Little by little the PSS Engineer and I narrowed the problem down.

In the end it turns out that a common network design practice got in the way of my configs. You see, our data center has servers with publicly mapped IP addresses and privately mapped ones. In other words there is a public set of addresses that is severely constraints and an internal set of IP’s for the same boxes. My dev environment was able to connect to the staging database using the public IP of that box because our office is physically and from a topology point of view very seperate from the datacenter holding the server. So my package when run locally executed fine but when run on the staging box at the data center it failed because it could not resolve the IP address used. It could not resolve the external IP address used in the config because…. it wasnt running external to the server. See? The solution to all this was to change the config to use the named instance of the database server - as in LA-Staging01 instead of 216.53.93.11 - that did the trick. The package writes whatever is defined in the connection object for SQL Server connection to its config file. Hence the use of the external IP, while completely fine for my dev box, caused the error on deployment.

You might say “d’oh - everybody knows you shouldnt use IP addresses in your config”. Well, I dont know if its that well known but I can say the reason why I wasnt using a named instance for my connections was the fact that I couldnt get the name to resolve properly. Turns out there are two ways one can the name to resolve. One is via the via the the hosts file. Now why didnt I think of that? When working with web sites I am in the hosts file all day long. What was it about this SQL box that made me not connect the dots here. Oh well. The other method is via a SQL utility called cliconfg , which allows you to define a local SQL alias by name and then point that alias to a specific IP. That did the trick.

Now all my connections are using a consistent name for the server the problem is taken care of. Lesson learned.