Database Helpers for PowerShell

PowerShell has a lot of great functionality to pull data from various sources on a server, like event logs, WMI objects, Registry, etc. However, preparing data for logging in a database is an area PowerShell is lacking.

As supporting material for SQL Saturday/Data Saturday presentations, here are a couple functions, with basic functionality, to aid in scripting bulk loads to SQL Server. If a different database platform is being used, such as PostgreSQL, Oracle, etc., this code will need to be updated to support that platform.

Get-SQLColumnsForDataTable, as the name should imply, returns an array of objects representing the column names and data types from a table in a SQL Server database. The object array can be used by the second function, Out-DataTableWithDataTypes to predefine the data types of columns in a System.Data.DataTable object. This does not return the length of strings or the scale and precision of decimal and numeric data types. Additionally, the query translates common SQL data types to .Net data types. If the table includes binary, image, CLR data types, etc., those data types will need to be translated to an equivalent .Net data type.

function Get-SQLColumnsForDataTable
{
        Param(
		[Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
		[string]$ServerInstance,
		[Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
		[string]$Database,
		[Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$true)]
		[PSCredential]$Credential,
        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [String] $SchemaName,
        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [String] $TableName
        )
    $Query = "SELECT c.Name ColumnName, ty.Name DataType, case when ty.Name like '%char' then 'String' 
                                                               when ty.Name = 'int' then 'Int32' 
                                                               when ty.name = 'Integer' then 'Int32' 
                                                               when ty.name = 'Smallint' then 'Int16' 
                                                               when ty.Name = 'BigInt' then 'Int64' 
                                                               when ty.name = 'money' then 'Decimal' 
                                                               when ty.name = 'numeric' then 'Decimal' 
                                                               when ty.name = 'float' then 'Decimal'
                                                               when ty.name = 'date' then 'DateTime'
                                                               when ty.name = 'bit' then 'Boolean'
                                                               else ty.name 
                                                          end DataTableType
    FROM sys.tables t
	    INNER JOIN sys.columns c ON c.object_id = t.object_id
	    INNER JOIN sys.types ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
		left JOIN sys.identity_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
    WHERE t.name = '$TableName' AND t.schema_id =SCHEMA_ID('$SchemaName') "

    if ($Credential -eq $null)
    {
	    $columns = invoke-sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query
    }
    else
    {
        $columns = invoke-sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query -Username $Credential.UserName -Password $Credential.GetNetworkCredential().Password
    }

    return , $columns
}

Out-DataTableWithDataTypes has been derived from code by Chad Miller and taken over by Rambling Cookie Monster (GitHub repository link). The original code attempted to determine the data type of incoming object. However, the data is loaded into the object as strings, so that’s the only data type that can be determined. This adaptation adds a second input parameter, $Columns. The best way to define this parameter is with the function above, Get-SQLColumnsForDataTable.

function Out-DataTableWithDataTypes
{ 
    [CmdletBinding()] 
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject,
          [Parameter(Position=1, Mandatory=$true)] [PSObject[]]$Columns)
 
    Begin 
    { 
        
        $dt = new-object Data.datatable 
        if ($columns.Count -gt 0)
        {
            foreach($column in $Columns)
            {
                [void]$dt.Columns.Add($column.ColumnName, $column.DataTableType)
            }
        }
    } 
    Process 
    { 
        foreach ($object in $InputObject) 
        { 
            $DR = $DT.NewRow()   
            foreach($property in $object.PsObject.get_properties()) 
            {     
                if ($DT.Columns.Contains($property.Name))
                {
                    if ($property.value -eq $null -or $property.Value -eq "" -or $property.Value -eq "NULL")
                    {
                        $DR.Item($property.Name) = [System.DBNull]::Value
                    }
                    else 
                    {
                        $DR.Item($property.Name) = $property.value
                    }
                } 
            }   
            $DT.Rows.Add($DR)  
             
        } 
    }  
      
    End 
    { 
        Write-Output @(,($dt)) 
    } 
 
}

With these 2 functions, the basic functionality is there to load data into a SQL Server database table with the proper data types. As with any data loading process, if the data types do not match, Out-DataTableWithDataTypes will fail on the load.

As with most every blog on the Internet, this code has been working for me and the needs I have for it. I fully recognize this does not meet every scenario. Your use of this code is welcome, but there are no guarantees with this code.