Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Decrypt DTSRun package name Expand / Collapse
Author
Message
Posted Monday, June 27, 2005 7:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 14, 2006 11:47 AM
Points: 11, 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

Post #194277
Posted Monday, June 27, 2005 10:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 30, 2009 4:02 PM
Points: 7, 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

Post #194570
Posted Monday, June 27, 2005 11:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 20, 2014 9:06 PM
Points: 2,693, Visits: 1,202

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
Post #194578
Posted Tuesday, June 28, 2005 2:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 30, 2009 4:02 PM
Points: 7, 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 /~%')

Post #194622
Posted Tuesday, June 28, 2005 3:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 5:17 AM
Points: 159, Visits: 429
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
Post #194633
Posted Tuesday, June 28, 2005 8:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 11, 2013 1:09 PM
Points: 80, Visits: 580

This utility claims to do what you want.

DTSRunDec

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

 




Post #194747
Posted Tuesday, July 5, 2005 11:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 24, 2014 12:26 AM
Points: 127, Visits: 44

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.
Post #197247
Posted Tuesday, June 6, 2006 3:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 19, 2007 3:10 AM
Points: 2, 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%'

 

Post #285117
Posted Wednesday, January 31, 2007 8:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 31, 2007 8:22 AM
Points: 92, 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?
Post #341342
Posted Thursday, February 1, 2007 12:45 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:34 AM
Points: 4,065, Visits: 5,276

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
Post #341772
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse