functions/Send-DSCEACSVtoSQL.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
#The function below was inspired by Chrissy LeMaire's post at https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/

function Send-DSCEACSVtoSQL {
    param(
        [Parameter(Mandatory=$true)][string]$SQLserver,
        [Parameter(Mandatory=$true)][string]$DatabaseName,
        [Parameter(Mandatory=$true)][string]$TableName,
        [Parameter(ParameterSetName='UserNameAuth')][String]$UserName,
        [Parameter(ParameterSetName='UserNameAuth')][SecureString]$Password,
        [Parameter(ParameterSetName='CredentialAuth')][PSCredential]$Credential,
        [Parameter(Mandatory=$true)]$InputCSV,
        [switch]$RemoveHeaders
    )
    
    #Review For Relevancy
    #if($PSBoundParameters.ContainsKey('RemoveHeaders')) {
    # $firstRowColumnNames = $true
    #}
    #else {
    # $firstRowColumnNames = $false
    #}
 
    ################### No need to modify anything below ###################
    [void][Reflection.Assembly]::LoadWithPartialName("System.Data")
    [void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")
 
    # 50k worked fastest and kept memory usage to a minimum
    $batchsize = 50000
 
    # Get Username and Password from Credential Object
    if($PSBoundParameters.ContainsKey('CredentialAuth')) {
        $connectionstring = 'Data Source={0};User Id={1};Password={2};Initial Catalog={3};' -f $SQLServer,($Credential.UserName),($Credential.GetNetworkCredential().Password),$DatabaseName
    }
    # Use Username and Password that were passed in
    elseif ($PSBoundParameters.ContainsKey('UserName')) {
        $connectionstring = 'Data Source={0};User Id={1};Password={2};Initial Catalog={3};' -f $SQLServer,$UserName,(Convert-SecureStringToString -SecureString $Password),$DatabaseName
    }
    # Use Integrated Authentication
    else {
        $connectionstring = 'Data Source={0};Integrated Security=SSPI;Initial Catalog={1};' -f $SQLServer,$DatabaseName
    }

    $bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock)
    $bulkcopy.DestinationTableName = $table
    $bulkcopy.bulkcopyTimeout = 0
    $bulkcopy.batchsize = $batchsize
    
    $csvDataTable = Import-CSV -Path $InputCSV | out-DataTable

    $bulkcopy.WriteToServer($csvDataTable) 

    # Clean Up
    $bulkcopy.Close()
    $bulkcopy.Dispose()
    $csvDataTable.Dispose()
 
    # Sometimes the Garbage Collector takes too long to clear the huge datatable.
    [System.GC]::Collect()
}