Convert SQL varbinary content into .pkg file using PowerShell

Question

I use SQL Server and I have a SQL table called [dbo].[TemplatePackageContent] which has only two fields:

[TemplatePackageContentId] [uniqueidentifier] NOT NULL,
[Content] [varbinary](max) NULL

I'd like to create PowerShell script which reads whole content from this table and for each row, it will generate a file, in a given directory with format {TemplatePackageContentId}.pkg based on the Content field.

So far I've managed how to read the whole content of the table:

param(
   [string] $dataSource = "(localdb)\mssqlLocalDb",
   [string] $database = "Hda_tenancy1",
   [string] $sqlCommand = $("SELECT * FROM TemplatePackageContent")
)

$connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$dataSet.Tables

Now I would like to make a mentioned conversion of received result, presumably looping thru each row.

I found this article which solves a similar problem in c# and I was thinking about using some of the logic from there and try to convert it into a PowerShell script.

What is the most optimal way to convert all the "Content" fields into files with .pkg format and what library should I use or what approach? Any ideas? Cheers

Answer 1

I ended up with this solution:

param(
   [string] $dataSource = "(localdb)\mssqlLocalDb",
   [string] $database = "Hda_tenancy1",
   [string] $templatePath = "C:\dev\hubadvance\Seeding\Templates\"
)

$sqlCommand = $("SELECT * FROM TemplatePackageContent");
$connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$dataSource;Integrated Security=SSPI;Initial Catalog=$database");
$cmd = new-object System.Data.SqlClient.SqlCommand($sqlCommand, $connection);

$connection.Open();
$reader = $cmd.ExecuteReader();

$results = @();
while ($reader.Read())
{
    $row = @{}
    for ($i = 0; $i -lt $reader.FieldCount; $i++)
    {
        $row[$reader.GetName($i)] = $reader.GetValue($i);
    }
    $results += new-object psobject -property $row;         
}
$connection.Close();

foreach ($row in $results) 
{
    Write-Host $row.TemplatePackageContentId;
    $path = $templatePath + $row.TemplatePackageContentId + ".pkg";
    [System.IO.File]::AppendAllText($path, $row.Content);
}