Bulk Insert Via Data.SqlClient.SqlBulkCopy

by philE at 2013-01-24 15:21:08

Hi All

So big picture i’m trying to script out tables with underlying indexes, PKs etc etc and copy the data across maintaing any identities etc.

Below I have a working (but ugly) script that achieves all but the identity peice.

When i use - $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr) the whole script works but looses identitys…

If i switch to - $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity) i get this error -

Exception calling ".ctor" with "2" argument(s): "Format of the initialization string does not conform to specification starting at index 0."

Based on my reading it seems like this error is a vague error masking something else and every reference blog, script, post i can find indicates the format is correct…
A - Please confirm the format is correct
and B - any ideas on what else may be occuring…

The acct running this has sa rights on the instance and local admin on the box.

Full Script Below -

[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | Out-Null
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue

function New-SMOconnection ($server, $commandTimeout = 5)
Write-Debug "Function: New-SMOconnection $server $connectionname $commandtimeout"
if (test-path variable:\conn)
} else
$conn = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $server
$conn.connectioncontext.connectTimeout = $commandTimeout




$srv = New-SMOconnection $server
$db = $srv.databases[$Database]

$Objects = $db.Tables | Where-object {$
.schema -eq$Schema-and$.name -like$Object}
$ObjectsRemaining = $Objects.count

foreach ($ScriptThis in $Objects )
Write-Host "Scripting Object $ScriptThis"
$scriptr = new-object (‘Microsoft.SqlServer.Management.Smo.Scripter’) ($srv)
$scriptr.Options.AppendToFile = $True
$scriptr.Options.AllowSystemObjects = $False
$scriptr.Options.ClusteredIndexes = $True
$scriptr.Options.DriAll = $True
$scriptr.Options.ScriptDrops = $False
$scriptr.Options.IncludeHeaders = $True
$scriptr.Options.ToFileOnly = $True
$scriptr.Options.Indexes = $True
$scriptr.Options.Permissions = $True
$scriptr.Options.WithDependencies = $False
$ScriptFile = $ScriptThis -replace "[|]"
$scriptr.Options.FileName = "" + $($SavePath) + "" + $($ScriptFile) + ".SQL"

Invoke-Sqlcmd -ServerInstance $destserver -Database $destDatabase -InputFile $scriptr.Options.FileName

$SrcConn = New-Object System.Data.SqlClient.SQLConnection("Data Source=$server;Initial Catalog=$Database;Integrated Security=True;")
$CmdText = "SELECT * FROM " + $ScriptThis
$SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn)
[System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()

$DestConnStr = New-Object System.Data.SqlClient.SQLConnection("Data Source=$destserver;Initial Catalog=$destDatabase;Integrated Security=True;")
$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
# $bulkCopy = New-Object ("Data.SqlClient.SqlBulkCopy") $DestConnStr

$bulkCopy.BatchSize = 1000
$bulkCopy.BulkCopyTimeout = 0

$bulkCopy.DestinationTableName = $ScriptThis

Catch [System.Exception]
$ex = $
Write-Host $ex.Message
Write-Host "done"


Write-Host "Items Remaining $ObjectsRemaining"
by DonJ at 2013-01-28 12:46:52
As a note, consider using the CODE and POWERSHELL buttons to format your code. Makes it a lot easier to follow.

Given that you’re dealing with the underlying .NET Framework on this, you might find a better (and faster) answer on a developer forum like StackOverflow.com. This isn’t really PowerShell, at this point - you’ve more or less written a "C# script."

Actually, I’d personally be inclined to write this in Visual Studio Express using C#. Might work.