SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Decrypt DTSRun package name


Decrypt DTSRun package name

Author
Message
don farrell
don farrell
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 1

Hi all,

Does anyone know how to decrypt the name of a dts package from within a sql job. As in I have a job that runs a dts packase as step 1 ...the step is DTSRun /~Z0x859DExxxxxxxxxxx

I need to know what package 0x859DExxxxxxx relates to ?

Any help most appreciated ?

Regards,

Don


Jamie Christian
Jamie Christian
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 4

Don, I am not sure I understand the Question but does this help?

-- ===================================================

PRINT ''
PRINT ''
PRINT ''
PRINT '+-----------------------------------------------------------------------------+'
PRINT '¦ DTS Package Details'
PRINT '+-----------------------------------------------------------------------------+'
PRINT ''
-- ===================================================================================
-- ===[ DTS Package Details ]=========================================================
--
-- Purpose : Lists in Detail DTS Package Names, IDs, Decriptions and Creation Dates
--
-- ===================================================================================
SET NOCOUNT ON
BEGIN
CREATE TABLE #DTS_Packages
(
[Package Name] VARCHAR(60) NOT NULL,
[Package ID] VARCHAR(40) NOT NULL,
[Description] VARCHAR(100) NOT NULL,
[LastDate] DATETIME,
[Owner] VARCHAR(60)
)
INSERT #DTS_Packages
SELECT name, id, description, MAX(createdate) AS LastDate, owner
FROM msdb..sysdtspackages
GROUP BY name, id, description, owner

SELECT [Package Name], [Package ID], [Description], [LastDate], [Owner] FROM #DTS_Packages
DROP TABLE #DTS_Packages
END
SET NOCOUNT OFF
GO


Hope so


philcart
philcart
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9601 Visits: 1441

You can't decrypt the DTSRUN command line. It's a combination of package name, package guid, server connection details and any necessary parameters.

You cuold try running SQL Profiler to see what the package is doing.



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Jamie Christian
Jamie Christian
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 4

I am Being Stubborn here

using MSDB

SELECT step_name AS Step_Name, job_id, step_id, subsystem, command, database_name, server
FROM sysjobsteps
WHERE (command LIKE N'DTSRUN /~%')


James Horsley
James Horsley
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 451
You probably can't decrypt but the chances are you can find which package it is by running it - many options really - it depends hwo many DTS jobs you have got setup but my guess is you will be able to isolate a number as "not likely" then in the "likely candidates" you could ensure logging is enabled and check the DTS exec logs after running scheduled task to see which one ran - or you could just add and ActiveX task that e.g. msgbox's you the name of the package and then run the commandline directly (not scheduled) so you see the msgbox




James Horsley
Workflow Consulting Limited
webooth
webooth
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 580

This utility claims to do what you want.

DTSRunDec

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26





hot2use
hot2use
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 71

Hi Jamie

Your script does list the internal ID, but not the ID used in the Jobs section of EM.

Regards

hot2use




__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

HoundDog
HoundDog
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 1

James Christian's answer works for me. Expanding slightly on it to bring back one or two more fields here's what I'm using :

/*This command can be used to determine which DTS package a job is running where the job lists the DTS package in the format DTSRun /~xxxxxxxxxxxxxxxxxxxxxx. */


USE msdb
GO

SELECT sj.name as 'JobName', sjs.step_name AS 'StepName', sjs.job_id,
sjs.step_id, sjs.subsystem, sjs.command, sj.date_created,
sj.originating_server, --name of the server from which the job came.
sjs.database_name --this will be NULL if subsystem is not T-SQL
FROM sysjobsteps sjs INNER JOIN sysjobs sj
ON sjs.job_id=sj.job_id
WHERE sjs.command LIKE N'DTSRun /~Z0xEEF6B66CBA75D66CBD%'


Carlos Urbina
Carlos Urbina
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1
Jamie's first script gives the package names from sysdtspackages. the second script gives the jobs and step names. However, I cannot see the relationship. Please explain further. We cannot assume that the step name is the package name, although I always do that. How can we, knowing the internal ID, link it back to a package name?
Greg Charles
Greg Charles
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11283 Visits: 5985

Neither of the scripts will help unless the package name is used in the job step name. The original question was how to decrypt an encrypted DTSRUN command and the only way I've found to do that is by using the DTSrundec tool mentioned above. I've found a compiled version here:
http://maiux.com/dr/node/21 and after unzipping it I was able to run it at a cmd prompt and decrypt an encrypted DTSRUN command.

Greg



Greg
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search