How to find SSIS package that's calling / Executing a stored procedure?

Question

May I know a SQL query OR some other way to know which SSIS package is calling / executing a stored procedure? Thanks, Kumar

Answer 1

Hi @Kumar Rnd, SSIS packages are XML files with the .dtsx extension. So, you can use SQL Server's T-SQL and XQuery to query packages to answer any question.

Answer 2

Hi @Kumar Rnd Here is a solution using PowerShell:

function Get-StoredProcedure {
    [CmdletBinding()]
    [OutputType('System.Management.Automation.PSObject')]
    param(
        [Parameter(Position = 0, ValueFromPipeline)]
        [System.IO.FileInfo[]] $Path = (Get-ChildItem -Recurse -File)
    )

    process {
        foreach ($item in $Path) {
            [pscustomobject]@{
                'Path'    = Split-Path -Parent -Path $item.FullName
                'Name'    = $item.Name
                'Command' = ($item | Select-String -Pattern 'exec(ute)?\s[^\s]+').Matches.Value
            }
        }
    }
}

Referring from this similar thread: Use Powershell to Find Stored Procedures in SSIS Packages.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.