CODEWITHSUNDEEP
sql-serversqlbulkcopypowershell-5.0
Vikas J
Vikas J

Reputation: 887

Powershell - How to add Bulk Copy command in Transaction block

I am using PowerShell version 5, I want to Add the BulkCopy command in the Transaction block, so that if at all there are any issues while copying data from 1 server to another, then nothing should be committed.

Below is my code, where Transaction works for other multiple SQL Commands (Create, Insert) so if anything fails while inserting table is also not created. But same is not working for the BulkCopy command.

Script

$SrcconnString = "Data Source=<Server-Name>;Database=<DB-Name>;User ID=<Login>;Password=<Pass>"
$TrgconnString = "Data Source=<Server-Name>;Database=<DB-Name>;User ID=<Login>;Password=<Pass>"

$error.clear()



$TrgTblName = 'dbo.test'
$BulkCopyTimeout = '1000'
$BulkCopyBatchSize = '20000'

Import-Module -Name SQLServer
write-host 'module loaded'

 #Create a SQL connection object
 
$Srcconn = New-Object System.Data.SqlClient.SqlConnection $SrcconnString
$Trgconn = New-Object System.Data.SqlClient.SqlConnection $TrgconnString


 #Attempt to open the connection
    $Srcconn.Open()
    $Trgconn.Open()
    
$tran = $Trgconn.BeginTransaction()
    
 try
{   
    
    if($Srcconn.State -eq "Open" -And $Trgconn.State -eq "Open" )
{
       Write-Host "connection successful"

  <# Below Commented block works for Transaction  #>
      
     <#
    $script = @"
            DROP Table IF EXISTS dbo.test_bulk;
            Create table dbo.test_bulk (Id int, Name varchar(2));
            INSERT INTO dbo.test_bulk(Id,name) Values (1,'vj'),(2,'jgjeguegjeg');
            
"@
     
      
       $cmd = New-Object System.Data.SqlClient.SqlCommand($script, $Trgconn)
        $cmd.Transaction = $tran
        [void]$cmd.ExecuteNonQuery()
      
      $tran.Commit()
    #> 

    
      

         $sql = "SELECT top 100 * FROM dbo.test(NOLOCK)";
        
           $sqlCommand = New-Object system.Data.SqlClient.SqlCommand($sql, $Srcconn)
          [System.Data.SqlClient.SqlDataReader] $sqlReader = $sqlCommand.ExecuteReader()                        
          $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($TrgconnString, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity,$tran)
          $bulkCopy.DestinationTableName = $TrgTblName
          $bulkCopy.BulkCopyTimeOut = $BulkCopyTimeout
          $bulkCopy.BatchSize = $BulkCopyBatchSize
          $bulkCopy.WriteToServer($sqlReader)
         

          $tran.Commit()
          $sqlReader.Close()
         $bulkCopy.Close()



      
      }
       
    }  
      catch
      {
      $tran.Rollback()
        #Write-Host "Operation failed"
        #$error
       $_.exception.message
      }
    
    finally
    {
    $Srcconn.Close()
    $Trgconn.Close()
    $error.clear()
    }

Update: After @DanGuzman comment, I Specified the transaction object ($tran) as the third parameter for the SqlBulkCopy command,

 $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($TrgconnString, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity,$tran)

but now it fails with an error

Exception calling "WriteToServer" with "1" argument(s): "WriteToServer requires an open and available connection. The Connection's current state is closed."

Upvotes: 0

Views: 1964

Answers (2)

Joonas Nick
Joonas Nick

Reputation: 1

I got similar setup working when passing the actual connection instead of connection string as the first parameter.

In your example it would be:

    $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($Trgconn, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity,$tran)

Upvotes: 0

Himanshuk
Himanshuk

Reputation: 185

Search for bcp utility and try to use it

Upvotes: 0

Related Questions