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."
done

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)
{
$conn.connectioncontext.disconnect()
} else
{
$conn = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $server
$conn.connectioncontext.connectTimeout = $commandTimeout
}
$conn.connectioncontext.connect()
$conn
}

CLS

$server=‘server\instance’
$Database=‘db’
$destserver=‘server\instance’
$destDatabase=‘db’

$SavePath=‘C:\Stuff\db’
$Object='test’
$Schema=‘dbo’

$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”
$scriptr.Script($ScriptThis)

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)
$SrcConn.Open()
[System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()

Try
{
$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
$bulkCopy.WriteToServer($sqlReader)

}
Catch [System.Exception]
{
$ex = $
.Exception
Write-Host $ex.Message
}
Finally
{
Write-Host “done”
$SqlReader.close()
$SrcConn.Close()
$SrcConn.Dispose()
$bulkCopy.Close()
}

$ObjectsRemaining–

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.