Is it possible to retrieve name(s) of SSIS package(s) that have an Exec SQL task that executes a specific SP?

Question

Is it possible to retrieve name(s) of SSIS package(s) that have an Exec SQL task that executes a specific SP?

VoldemarG

Hall of Fame

Points: 3883
More actions
August 23, 2018 at 2:14 pm

#367561

Is it possible to find out names of SSIS packages scheduled via Agent that that execute a specific stored procedure by Stored Procedure name?
Likes to play Chess

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

VoldemarG - Thursday, August 23, 2018 2:14 PM
Is it possible to find out names of SSIS packages scheduled via Agent that that execute a specific stored procedure by Stored Procedure name?

Not sure what Agent has got to do with this, but you can just search the package XML for the name of your proc.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

True. but there are 1100 packages on 3 prod servers... 🙂

Likes to play Chess

frederico_fonseca SSCoach Points: 16157 More actions · Answer 3

it may not be as easy as you would like.

It will all depend on how your packages are build, executed and where they are stored.

Storage
- SSISDB
- syspackages
- filesystem

Execution
- Executed directly from sql job (e.g. ssis step)
- Through a stored procedured (directly or cmdshell call)
- Command line call (.cmd or powershell)
- called from another SSIS package
--- SSIS package name hardcoded on parent package
--- SSIS package name from config table/file

SP Naming location
- from config table
- from config file
- hardcoded on package

All the above can affect the amount of work you may (or not) have to identify what you need.

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

VoldemarG - Thursday, August 23, 2018 3:03 PM
True. but there are 1100 packages on 3 prod servers... 🙂

Heh... Phil wasn't suggesting a manual search. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 5

Some of the packages are under File System and most of them are under MSDB.

Likes to play Chess

Eric M Russell SSC Guru Points: 125561 More actions · Answer 6

SSIS packages are contained in .dtsx files and the underlying data format is XML. You should see things like referenced database objects and T-SQL commands in clear text. Other protected properties like passwords are encrypted. So, you can use your version control system or a text search tool to scan across all the SSIS project files looking for object names.

If you don't have access to the SSIS source project files, then you can export them from the SSISDB catalog to a local folder. From SSMS, drill down on 'Integration Services Catalogs', highlight a project, right-click and then choose option 'Export'. You'll have to do this for each SSIS project, or google a PowerShell script to automate the process.

If someone is looking for a cool side project, developing a SSMS add-in to search the SSISDB catalog, something similar to RedGate SQL Search, would make you a community hero.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

VoldemarG Hall of Fame Points: 3883 More actions · Answer 7

VoldemarG

Hall of Fame

Points: 3883

August 28, 2018 at 2:12 pm

#2003722

THANK YOU! great info.

Likes to play Chess