Hello all!
Kindly please for help - I have strange issue with PS script.
Here is a small summary what it does: it creates CSV file from shared drive, update SQL database and when it is done it updates Active Directory user objects with attributes valuses from CSV such as position, lastname, firstname, department, manager etc.
Due to unknown reason, some AD objects cannot be updated due to error message:
Set-ADObject : replace
At D:\TETA_IMPORT\TEST\sqlinject-test.ps1:183 char:1
+ Set-ADObject $guid -Replace @{
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (c5dce716-4965-4360-8e89-f82517e8f512:ADObject) [Set-ADObject], ADInvalidOperationException
+ FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.SetADObject
The full code is below:
Import-module ActiveDirectory
#Start-Transcript -path D:\HR\log-file.txt
Copy-Item \\domain.com\shared_drive\file\data_file.CSV D:\shared_drive\data_file.CSV -Force
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=server01.domain.com;Database=YARIS;Integrated Security=False;uid=Awaw00dbs0001Yaris;pwd=p@sswd2!"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "truncate table GSE_DANE"
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$SqlCmd.ExecuteNonQuery()
$SqlConnection.Close()
####################################################
#
# PowerShell CSV to SQL Import Script
#
####################################################
# Database variables
$sqlserver = "database_serv.domain.com"
$database = "Yaris"
$table = "GSE_DANE"
# CSV variables
$csvfile = "D:\shared_drive\data_file.CSV"
$csvdelimiter = "`t"
$FirstRowColumnNames = $true
################### No need to modify anything below ###################
Write-Host "Script started..."
$elapsed = [System.Diagnostics.Stopwatch]::StartNew()
[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
# Build the sqlbulkcopy connection, and set the timeout to infinite
$connectionstring = "Data Source=$sqlserver;Initial Catalog=$database;uid=Awaw00dbs0001Yaris;pwd=p@sswd2!"
$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock)
$bulkcopy.DestinationTableName = $table
$bulkcopy.bulkcopyTimeout = 0
$bulkcopy.batchsize = $batchsize
# Create the datatable, and autogenerate the columns.
$datatable = New-Object System.Data.DataTable
# Open the text file from disk
$reader = New-Object System.IO.StreamReader($csvfile)
$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)
if ($FirstRowColumnNames -eq $true) { $null = $reader.readLine() }
foreach ($column in $columns) {
$null = $datatable.Columns.Add()
}
# Read in the data, line by line, not column by column
while (($line = $reader.ReadLine()) -ne $null) {
$null = $datatable.Rows.Add($line.Split($csvdelimiter))
# Import and empty the datatable before it starts taking up too much RAM, but
# after it has enough rows to make the import efficient.
$i++; if (($i % $batchsize) -eq 0) {
$bulkcopy.WriteToServer($datatable)
Write-Host "$i rows have been inserted in $($elapsed.Elapsed.ToString())."
$datatable.Clear()
}
}
# Add in all the remaining rows since the last clear
if($datatable.Rows.Count -gt 0) {
$bulkcopy.WriteToServer($datatable)
$datatable.Clear()
}
# Clean Up
$reader.Close(); $reader.Dispose()
$bulkcopy.Close(); $bulkcopy.Dispose()
$datatable.Dispose()
Write-Host "Script complete. $i rows have been inserted into the database."
Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())"
# Sometimes the Garbage Collector takes too long to clear the huge datatable.
[System.GC]::Collect()
Echo "***********************Starting Import******************************************************************************"
$A = Get-Date
$A.ToUniversalTime()
echo $A
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=database_serv.domain.com;Database=YARIS;Integrated Security=False;uid=Awaw00dbs0001Yaris;pwd=p@sswd2!"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT * from GSE_DANE"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] | foreach {
$suplogin = 0
$arr = @($_.LOGIN.split('\/'))
if ($arr[0]) {
if ($arr[0] -notlike "domain") {}
else {
$guid = (Get-ADUser $arr[1]).objectGuid
#echo $_.EP15
#$arr2 = @($_.EP3.split('\/'))
$UserNameToFind=$_.SUPERIOR_ID
$connectionstring2="Server=database_serv.mednet.world;Database=YARIS;Integrated Security=False;uid=Awaw00dbs0001Yaris;pwd=p@sswd2!"
$SQLCommandText2="SELECT LOGIN FROM GSE_DANE WHERE USER_ID='$($UserNameToFind)'"
#$SQLCommandText2="SELECT LOGIN FROM GSE_DANE WHERE login like 'mednet\wojciecp'"
$SQLConnection2 = new-object system.data.sqlclient.SqlConnection($connectionstring)
$SQLConnection2.open()
$sqlCommand2 = New-Object system.Data.sqlclient.SqlCommand($SQLCommandText2,$SQLConnection2)
$suplogin = $sqlCommand2.ExecuteScalar()
$SQLConnection2.close()
if ($suplogin) {
$arr2 = @($suplogin.split('\/'))
if ($arr2[0]) {
if ($arr2[0] -notlike "domain") {$dnsup=$null}
else {
$dnsup =(Get-AdUser $arr2[1] | select DistinguishedName)
}
echo $_.LOGIN
echo $suplogin
echo $guid
echo $dnsup.DistinguishedName
if ($dnsup.DistinguishedName) {
Set-ADObject $guid -Replace @{
'physicalDeliveryOfficeName'=$_.COMPANY;
#'telephoneNumber'=$telephoneNumber;
'department'=$_.DEPARTMENT;
'description'=$_.STANOWISKO;
'title'=$_.JOB_POSITION;
'streetAddress'=$_.OFFICE_ADDRESS;
'c'="EN";
'co'=$_.COUNTRY;
'company'=$_.DIVISION;
'manager'=$dnsup.DistinguishedName;
'ipPhone'=$_.USER_ID;
'pager'=$_.CC;
} -Partition "DC=mednet,DC=world"
}
#echo $_.IP1,$_.IP3,$dnsup
}
}
else {
echo $_.LOGIN
#echo $suplogin
#echo $guid
echo $dnsup.DistinguishedName
Set-ADObject $guid -Replace @{
'physicalDeliveryOfficeName'=$_.COMPANY;
#'telephoneNumber'=$telephoneNumber;
'department'=$_.DEPARTMENT;
'description'=$_.STANOWISKO;
'title'=$_.JOB_POSITION;
'streetAddress'=$_.OFFICE_ADDRESS;
'c'="EN";
'co'=$_.COUNTRY;
'company'=$_.DIVISION;
#'manager'=$dnsup.DistinguishedName;
'ipPhone'=$_.USER_ID;
'pager'=$_.CC;
} -Partition "DC=domain,DC=com"
}
}
}
}
