Use Powershell to Find Stored Procedures in SSIS Packages

Question

I'm attempting to using PowerShell to search all SSIS Packages within a directory, and return the following:

• The path and name of the file
• The line containing the stored procedure name, i.e. "exec mystoredproc"

The criteria I'd like to use to search is simply "exec", or "exec or execute" if possible, and I'd like the space at the end of the string following exec to be the delimiter as far as what is returned.

I've tried:

Get-ChildItem -recurse | Select-String -pattern "exec" | group path | select name

However this only returns the file name, not the additional text that is required.

If there is a better way to do this than PowerShell, I'm open to it, but based on what I've found so far this seemed to be the right path. Thank you in advance!

Edit: Having continued to research, I've found that this command

Get-ChildItem -recurse | Select-String -pattern exec,execute | Select filename,linenumber

seems to give the correct file and line number for the commands I'm looking for. Is there some way to have the line that the linenumber references print instead of the number? I've looked at ForEach and Get-Content, but can't seem to get the syntax quite right.

Answer 1

Here's a function that should prove useful for you:

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

This will go through whatever FileInfo objects you pass to it (do note to use -File with Get-ChildItem or filter out the PSIsContainer property). It returns an object with the path, filename, and command in a string in the form of exec(ute) <contiguous non-spaces>