Encrypted DTS packages - Find DTS Package name | SansSQL

Thursday, January 3, 2013

Encrypted DTS packages - Find DTS Package name

Happy New Year 2013 to all of you... I shall now start off with some techie stuff.

Most of us have been working on DTS\SSIS packages. Even though we get to hear very little about DTS packages now a days; sometimes it becomes inevitable to know some of the legacy left behind by older versions.
To identify which 'Encrypted' DTS package is being referenced by scheduled job.

Before I explain the method of doing the above task, let me tell you the reason why the 'DTS package calls' are 'Encrypted' at first place? I am sure many of us are already aware of the reason... but still... here it is...
Most of us are addicted to follow easy methodology; right click the DTS Package (SQL 2000) and schedule a job. The created SQL Job will have an encrypted name for DTS package in SQL job step.

For example:DTSRun /~Z0x99810D5EE6B8FC6BFEB92F1D9EB5849.....
the above value would be much more! I have cut short to keep it simple ;)

So, now the task; to identify which package is being called by scheduled job step.

For each Job which makes an encrypted call to DTS, from SQL Job Step, refer to the following steps:
  1. To the end of Command (DTSRun) line, add /!X /!C
    For example:
    DTSRun /~Z0x99810D5EE6B8FC6BFEB92F1D9EB5 /!X /!C
    /!X means 'do not execute' and /!C means 'copy results onto Windows Clipboard'
  2. Copy complete command DTSRUN with /!X /!C
  3. Run the command from command line. One thing I noticed: The command can be either run from target server where the JOB is or from your local machine if it is the same network as of target server. I hope I did make some sense!
  4. Open notepad, paste the clipboard result onto notepad. So, there it is... name of the package!!

No comments:

Post a Comment