Where are those connections hiding?

Introduction

It seems that I encounter my fair share of problems. On the same day that I got slowed by the Target Server issue, I also got around to checking on my push logs log transfer job I had implemented. It was failing miserably and I decided today was the day that I would find out why.

I kept getting a -2147217843 error, login failed, on the very first step of the package, which was a Dynamic Properties task step designed to query the database and set some global variables. Didn't make sense. SQL Server and SQL Agent were both running under a domain account that was a local administrator. Time to troubleshoot.

My first step was to create a test job that selected data from a table. I ran this and used the "Advanced" tab of the job step to capture the output. Success!

Next I decided to create a test package and schedule it so that it would run under the SQLAgent account privileges. It was a simple package with a single T-SQL task that selected some data. This also ran without an issue.

At this point I was scratching my head a bit and decided to start modifying my test package. I added global variables, then a dynamic properties task, slowly building up my test package until it looked like the original package.

And it worked!

At this point I was extremely confused. It was time to go low level. I opened the DTS package on and saved it as a "Visual Basic" formatted file. Next I double clicked the .BAS file and the Visual Basic IDE started. This would allow me to move through the package at a very low level and see exactly what the DTSRun utility would be executing.

The first couple pages looked pretty standard for a DTS package. Creating the package object, adding global variables. Then I encountered a data connection element.

Only it was connecting to my test server. Not the production server. Hmmmmmmmm.

I quickly popped over the Enterprise Manager and checked the DTS Designer. Only one connection element and it was configured to look at the local server. Now I was more confused.

Why the extra connection?

It turns out with a little digging that this package was originally designed with a connection that had the test server name hard coded. Not a big deal, and that's the easiest way to test something on your workstation and the server is remote. When this package was migrated to the production server, the connection was edited and changed to a "new connection" with the server designated as "local".

Funny thing about DTS. It keeps the old connection in the package definition. Not that there's any way for you to change this or remove it, but it's there.

And since the production server account didn't have rights to get to the test server (as it should be), the package failed and no subsequent tasks were executed. I added the production server account to my test server and the package worked.

Conclusions

One note for the security conscious. I removed the production account from my test server and rewrote the package after this to fix the issue.

DTS is a fantastic tool. I love it. But the designer has quirks and I've learned that you really want to be careful when modifying packages. In some cases, like this one, rebuilding the entire package is the best solution.

As always I welcome feedback on this article using the "Your Opinion" button below. Please also rate this article.