Wednesday, April 18, 2007

Decrypting DTSRun

Those of you who still use SQL Server 2000 will probably recognise this little problem. If you create a DTS package and then schedule it, a SQL job is created and the contents of the step will look a little like this:

DTSRun /~Z0x4555AB9D3224BC0C759856EF83AA07080B2...

This is about as much use a chocolate teapot if you want to see what this string actually does or, more commonly, you have inherited such a job and want to know what DTS package it refers to. Ask most SQL bods and you will get one of two answers; either that you can't decrypt it or that you can use a small utility written by some Russian dude. The truth is, though, that you can decrypt it and there is a utility written by some Russian dude out there to do it, but it's almost impossible to find and if you do it will probably be an uncompiled piece of C code which isn't a lot of use to most people.

But don't despair because the real solution is a lot easier than that. Microsoft actually provide the ability to do this with the DTSRun utility itself. It's documented, but very few people have ever read the documentation for this utility it would seem, but one person who did (or perhaps found out from someone else who did) is a chap called James Pua. He posted to the SQL Server Magazine forums with a handy tip about two switches for DTSRun, /!X and /!C which, if used in conjunction with your encrypted string, will decrypt the string and output the result to the clipboard ready for you to paste somewhere for reading. It looks like this in use:

DTSRun /~Z0x4555AB9D3224BC0C759856EF83AA07080B2... /!X /!C

And the output (once pasted into Notepad or similar) will look like this:

DTSRun /S "<server>\<instance>" /U "<uid>" /P "<pwd>" /N "<package name>" /!X /!C

Obviously I shortened the encrypted string (you wouldn't believe how many people say "But I get a longer string and no dots") but that's it. Now you can decrypt those mysterious strings into readable English.

You can find the full documentation for DTSRun on the MSDN.

No comments: