Encrypted DTS packages... continued...

Yesterday I posted the way of getting to know about the DTS Package name from a job step which is encrypted "Encrypted DTS packages - Find DTS Package name". So, now once we are aware of the proper DTS calls used in the scheduled jobs... we may want to replace the encrypted DTS names with correct readable names... which helps us to understand better.

To perform the same:
Following list of SP's come in handy; one can refer to books online to know more about the same.

sp_update_jobstep
sp_add_jobstep
sp_delete_jobstep
sp_help_jobstep

I made use of one of the above SP's and created scripts to have the DTS jobs updated:

One such example is as shown below:

USE msdb
GO
EXEC dbo.sp_update_jobstep
@job_name = 'Job Name',
@step_id = 1,
@command = 'DTSRun /S "ServerName" /N "DTS package name" /E '
GO

This will do the trick!

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!!

DTS packages in SQL Server 2005 and SQL Server 2008

As you all know that the DTS packages in SQL server 2000 is replaced by the giant called Integration Services which has more features than DTS, and DTS is no more supported in SQL Server 2005 and up.
To give a backward compatibility until the DTS packages are migrated(or converted by you) to SSIS packages, you can import the DTS packages into SQL Server 2005 and SQL Server 2008 and call them in the jobs to do their work.

These Imported packages will be present at the below location in SQL Server management Studio.
SQLServer>> Management>> Legacy>> Data Transformation Services.

The DTS packages under this path cannot be edited in SQL Server 2005 and SQL Server 2008.
For editing these packages, you need to install "DTS Designer Components"

If you want to edit DTS packages in SQL Server 2005 go here, download and install the component "SQLServer2005_DTS.msi"

If you want to edit DTS packages in SQL Server 2008 go here, download and install the component "SQLServer2005_BC.msi"

Once you install the DTS Designer Components, it will allow you to edit the DTS packages as you do in SQL Server 2000.
Just right-click on the package and choose the option "open" and this will open up the DTS designer for you.

I strongly recommend to convert the DTS packages to the SSIS packages as the support to DTS will be completed removed in future versions of SQL Server.