# I will use Out-DbaDataTable and Write-DbaDataTable from dbatools, so import it
Import-Module dbatools

# find recursively all Executable nodes in .dtsx file
function Find-Executables($fileName, $executable)
{
    foreach($item in $executable)
    {
        # are we there, yet? (if no - recursion)
        if($item.Executables)
        {
            Find-Executables $fileName $item.Executables.Executable
        }

        # if yes - the result
        $prop = @{
            'refId' = $item.refId
            'creationName' = $item.CreationName
            'description' = $item.Description
            'objectName' = $item.ObjectName
            'executableType' = $item.ExecutableType
            'objectData' = $item.ObjectData.ExpressionTask.Expression
        }

        $prop

    }
}

# get all Precedence Constraints; simpler than Executables, because all of them are in single node
function Find-PrecedenceConstraints($fileName, $precedenceConstraints)
{
    foreach($item in $precedenceConstraints)
    {
        $prop = @{
            'refId' = $item.refId
            'from' = $item.From
            'to' = $item.To
            'logicalAnd' = [boolean]$item.LogicalAnd
            'evalOp' = [int]$item.EvalOp
            'objectName' = $item.ObjectName
            'expression' = $item.Expression
            'value' = [int]$item.Value
        }

        $prop
    }
}

# the data collectors
$allPackagesInfo = @()
$allExecutables = @()
$allPrecedenceConstraints = @()

# loop through every .dtsx file in folder; all my packages' names start with 0, so there is an example how to filter it
foreach($file in (Get-ChildItem C:\Users\brata_000\Source\Repos\SSIS_Graph\SSIS_Graph\SSIS_Graph\* -Include 0*.dtsx))
{

    # read .dtsx into XML variable
    [xml] $pkg = Get-Content $file

    # create hash table with package information
    $pkgInfo = @{
        'Name' = $file.Name
        'Executables' = Find-Executables $file.Name $pkg.Executable
        'PrecedenceConstraints' = Find-PrecedenceConstraints $file.Name $pkg.Executable.PrecedenceConstraints.PrecedenceConstraint
    }

    # add the table as PSobject to variable with all the package information
    $allPackagesInfo += New-Object psobject -Property $pkgInfo

}

# I don't want to confirm TRUNCATE TABLE for Write-DbaDataTable (when I'm running it few times)
$ConfirmPreference = 'none'

# Having all the information in one place - save it in the database; loop through all the packages (see the filter, again?)
$allPackagesInfo | Where-Object -Property Name -Like '0*' | ForEach-Object {

    $pkgName = $_.Name

    # all the Executables in the package
    $_.Executables | ForEach-Object {
        $d = @{
            'pkgName' = $pkgName
            'refId' = $_.refId
            'creationName' = $_.creationName
            'description' = $_.description
            'objectName' = $_.objectName
            'executableType' = $_.executableType
            'objectData' = $_.objectData
        }

        $allExecutables += New-Object psobject -Property $d
    }

    # all the Precedence Constraints in the package; casting to proper types will automaticaly create
    # columns of types other than nvarchar(max) when using -AutoCreateTable on Write-DbaDataTable
    $_.PrecedenceConstraints | ForEach-Object {
        $d = @{
            'pkgName' = $pkgName
            'refId' = $_.refId
            'from' = $_.from
            'to' = $_.to
            'logicalAnd' = [boolean]$_.logicalAnd
            'evalOp' = [int\]$_.evalOp
            'objectName' = $_.objectName
            'expression' = $_.expression
            'value' = [int]$_.value
        }
        $allPrecedenceConstraints += New-Object psobject -Property $d
    }
}

# I'm using SQL Server for Linux, connecting to the VM, so I use SQL authentication (for now) - why not use 'sa' then?
$cred = Get-Credential sa

#save all Executables to the database
$allExecutables | Out-DbaDataTable | Write-DbaDataTable -SqlInstance 127.0.0.1:14333 `
                    -SqlCredential $cred -Database SSISGraph -Schema dbo -Table Executables -AutoCreateTable -Truncate

# save all Precedence Constraints to the database
$allPrecedenceConstraints | Out-DbaDataTable | Write-DbaDataTable -SqlInstance 127.0.0.1:14333 `
                    -SqlCredential $cred -Database SSISGraph -Schema dbo -Table PrecedenceConstraints -AutoCreateTable -Truncate