Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Bulk Insert data into SQL Server using Powershell via a filestream. Supporting blog post is located here https://blog.darrenjrobinson.com/updated-identifying-active-directory-users-with-pwned-passwords-using-microsoftforefront-identity-manager/
#######################
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
Function insert2DB($r) {
$table = $r | Out-DataTable
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=SQL-SERVERNAME;Integrated Security=SSPI;Initial Catalog=PASSWORDS-DB");
$cn.Open()
$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
$bc.DestinationTableName = "dbo.PASSWORDS-TABLE"
$bc.WriteToServer($table)
$cn.Close()
}
$reader = new-object System.IO.StreamReader("D:\pwned-passwords-update-1.txt")
[int]$i=0
$rows = @()
while(($line = $reader.ReadLine()) -ne $null)
{
$rows += New-Object PSObject -prop @{
password_id = ""
passwords = $line
}
$i++
# Every 1000 rows insert
if ($rows.count -eq 1000){
write-host "Inserting 1000 rows"
write-host "$i inserted into DB"
# insert into DB
insert2DB($rows)
# Reset
$rows = @()
}
}
$reader.Close()
# insert the last batch
write-host "$rows.count inserted into DB"
insert2DB($rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.