Based on https://www.simple-talk.com/blogs/2011/08/31/storing-windows-event-viewer-output-in-a-sql-server-table-with-powershell/ Running into issues using SchemaNo.sql despite the article's claim "because the Write-DataTable cmdlet uses sqlbulkcopy, you need the pass the columns to it in the same order as they occur in the table". Error is Write…

EventLogParser.ps1

#######################
function Get-Type
{
    param($type)

$types = @(
'System.Boolean',
'System.Byte[]',
'System.Byte',
'System.Char',
'System.Datetime',
'System.Decimal',
'System.Double',
'System.Guid',
'System.Int16',
'System.Int32',
'System.Int64',
'System.Single',
'System.UInt16',
'System.UInt32',
'System.UInt64')

    if ( $types -contains $type ) {
        Write-Output "$type"
    }
    else {
        Write-Output 'System.String'
        
    }
} #Get-Type

#######################
<#
.SYNOPSIS
Creates a DataTable for an object
.DESCRIPTION
Creates a DataTable based on an objects properties.
.INPUTS
Object
    Any object can be piped to Out-DataTable
.OUTPUTS
   System.Data.DataTable
.EXAMPLE
$dt = Get-psdrive| Out-DataTable
This example creates a DataTable from the properties of Get-psdrive and assigns output to $dt variable
.NOTES
Adapted from script by Marc van Orsouw see link
Version History
v1.0  - Chad Miller - Initial Release
v1.1  - Chad Miller - Fixed Issue with Properties
v1.2  - Chad Miller - Added setting column datatype by property as suggested by emp0
v1.3  - Chad Miller - Corrected issue with setting datatype on empty properties
v1.4  - Chad Miller - Corrected issue with DBNull
v1.5  - Chad Miller - Updated example
v1.6  - Chad Miller - Added column datatype logic with default to string
v1.7 - Chad Miller - Fixed issue with IsArray
.LINK
http://thepowershellguy.com/blogs/posh/archive/2007/01/21/powershell-gui-scripblock-monitor-script.aspx
#>
function Out-DataTable
{
    [CmdletBinding()]
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject)

    Begin
    {
        $dt = new-object Data.datatable  
        $First = $true 
    }
    Process
    {
        foreach ($object in $InputObject)
        {
            $DR = $DT.NewRow()  
            foreach($property in $object.PsObject.get_properties())
            {  
                if ($first)
                {  
                    $Col =  new-object Data.DataColumn  
                    $Col.ColumnName = $property.Name.ToString()  
                    if ($property.value)
                    {
                        if ($property.value -isnot [System.DBNull]) {
                            $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)")
                         }
                    }
                    $DT.Columns.Add($Col)
                }  
                if ($property.Gettype().IsArray) {
                    $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1
                }  
               else {
                    $DR.Item($property.Name) = $property.value
                }
            }  
            $DT.Rows.Add($DR)  
            $First = $false
        }
    } 
     
    End
    {
        Write-Output @(,($dt))
    }

} #Out-DataTable

#######################
<#
.SYNOPSIS
Writes data only to SQL Server tables.
.DESCRIPTION
Writes data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.
.INPUTS
None
    You cannot pipe objects to Write-DataTable
.OUTPUTS
None
    Produces no output
.EXAMPLE
$dt = Invoke-Sqlcmd2 -ServerInstance "Z003\R2" -Database pubs "select *  from authors"
Write-DataTable -ServerInstance "Z003\R2" -Database pubscopy -TableName authors -Data $dt
This example loads a variable dt of type DataTable from query and write the datatable to another database
.NOTES
Write-DataTable uses the SqlBulkCopy class see links for additional information on this class.
Version History
v1.0   - Chad Miller - Initial release
v1.1   - Chad Miller - Fixed error message
.LINK
http://msdn.microsoft.com/en-us/library/30c3y597%28v=VS.90%29.aspx
#>
function Write-DataTable
{
    [CmdletBinding()]
    param(
    [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
    [Parameter(Position=1, Mandatory=$true)] [string]$Database,
    [Parameter(Position=2, Mandatory=$true)] [string]$TableName,
    [Parameter(Position=3, Mandatory=$true,ValueFromPipeline = $true,ValueFromPipelinebyPropertyname = $true) ] $Data,
    [Parameter(Position=4, Mandatory=$false)] [string]$Username,
    [Parameter(Position=5, Mandatory=$false)] [string]$Password,
    [Parameter(Position=6, Mandatory=$false)] [Int32]$BatchSize=50000,
    [Parameter(Position=7, Mandatory=$false)] [Int32]$QueryTimeout=0,
    [Parameter(Position=8, Mandatory=$false)] [Int32]$ConnectionTimeout=15
    )
    
    $conn=new-object System.Data.SqlClient.SQLConnection

    if ($Username)
    { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
    else
    { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }

    $conn.ConnectionString=$ConnectionString

    try
    {
        $conn.Open()
        $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
        $bulkCopy.DestinationTableName = $tableName
        $bulkCopy.BatchSize = $BatchSize
        $bulkCopy.BulkCopyTimeout = $QueryTimeOut
        $bulkCopy.WriteToServer($Data)
        $conn.Close()
    }
    catch
    {
        $ex = $_.Exception
        Write-Error "$ex.Message"
        continue
    }

} #Write-DataTable

function PPrint
{
    [CmdletBinding()]
    param(
        [Parameter(Position=0, Mandatory=$true,ValueFromPipeline = $true,ValueFromPipelinebyPropertyname = $true) ] $DataTable
    )

    if($false)
    {    
        foreach($table in $DataTable.Columns)
        {
            Write-Host $table.ColumnName
        }
    }

    foreach($row in $DataTable.Rows)
    {
        Write-Host $row[0], $row[1],$row[2],$row[3],$row[4],$row[5]
    }
}

$LogList = @("Application", "System")
$server = $env:computername
$dbServer = "localhost\dev2014"
$dbName = "tempdb"
$logTable = "dbo.StagingEventLog"
foreach ($logName in $LogList) 
{ 
    $variable = 
    ( 
        Get-EventLog -ComputerName $server -LogName $logName -Newest 10 | select @{Expression={$($server)};Label ="ServerName"}, @{Expression={$($logName)};Label ="LogName"},index,TimeGenerated,EntryType,Source,InstanceID, Message 
    ) 
    
    $valuedatatable = Out-DataTable -InputObject $variable 
    
    PPrint -dataTable $valuedatatable

    Write-DataTable -ServerInstance $dbServer -Database $dbName -TableName $logTable -data $valuedatatable
}

SchemaNo.sql

DROP TABLE dbo.StagingEventLog

CREATE TABLE dbo.StagingEventLog
(
    EventLogID int IDENTITY(1, 1) NOT NULL
,   ServerName sysname
,   LogName nvarchar(15) NOT NULL
,   LogIndex [int] NOT NULL
,   LogDate [datetime] NULL
,   EntryType [varchar](30) NULL
,   Source [varchar](120) NULL
,   InstanceID [varchar](30) NULL
,   Message [varchar](MAX) NULL
,   CONSTRAINT PK_dbo_StagingEventLog_ServerName_LogName_LogIndex PRIMARY KEY (ServerName,LogName, LogIndex)
);

SchemaOriginal.sql

CREATE TABLE dbo.StagingEventLog
(
    EventLogID int IDENTITY(1, 1) NOT NULL
,   LogName nvarchar(15) NOT NULL
,   LogIndex [int] NULL
,   LogDate [datetime] NULL
,   EntryType [varchar](30) NULL
,   Source [varchar](120) NULL
,   InstanceID [varchar](30) NULL
,   Message [varchar](MAX) NULL

);