It works with Numeric (5,3) and NUMERIC(11,0)
Exception calling "WriteToServer" with "1" argument(s): "The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."
How to reproduce.
1. On SQLite execute this statements.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .00000);
2. On MS SQL Server 2014 execute this statement
CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);
3. Use System.Data.SqlClient.SqlBulkCopy to copy data from SQLite to MS SQL Server
Code: Select all
[string]$ToTableName = "SERVER.Test.dbo.ToNum"
[string]$FromFileName = 'N:\SqLite\outStream.db'
# get sql info
[string[]]$ttname = $ToTableName.Split(".")
[string]$ServerName = $ttname[0]
[string]$dbName = $ttname[1]
[string]$schema = $ttname[2]
[string]$TableName = $ttname[3]
$sqlString = "SELECT * FROM FromNum"
$DeleteContentsOfTableBeforeCopy = $true; #if table exists, do we clear out existing data?
$DestinationTable = $TableName #the name of the table to put it in
$Destinationinstance = $ServerName #the name of the server or instance
$Destinationdatabase = $dbName #the name of the database where you want to put the data
$DestinationWindowsSecurity = $true #or $False if you aren't using Windows security
$DestinationUserID = '' #the name of the SQL Server user if not integrated security
$DeleteContentsOfTableBeforeCopy = $false
#I just create a connection to my existing database
$con = New-Object -TypeName System.Data.Odbc.OdbcConnection
# I then give it a simple connection string
$con.ConnectionString = "DSN=SqLite;Database=N:\SqLite\outStream.db;Direct=True"
#and open the connection
$con.Open()
#We'll just start by creating a SQL statement in a command
$SourceSQLCommand = $con.CreateCommand()
$SourceSQLCommand.CommandText = $sqlString
$DestinationConnectionString = "Data Source=$Destinationinstance;Initial Catalog=$Destinationdatabase;$(
if ($DestinationWindowsSecurity) { 'integrated security=true' }
else
{
"User id=$DestinationUserID; Password=$(((Get-Credential $DestinationUserID).GetNetworkCredential()).Password); integrated security=false"
})"
try
{
#now squirt the data in using the bulk copy library.
$bulkCopy = New-Object ("System.Data.SqlClient.SqlBulkCopy") $DestinationConnectionString, ([System.Data.SqlClient.SqlBulkCopyOptions]::TableLock -bor [System.Data.SqlClient.SqlBulkCopyOptions]::UseInternalTransaction)
$bulkCopy.DestinationTableName = $DestinationTable
$bulkcopy.EnableStreaming = $true;
$bulkCopy.BatchSize = 50000 #The number of rows in each batch sent to the server
$bulkCopy.BulkCopyTimeout = 0 #the number of seconds before a time-out
$bulkCopy.WriteToServer($SourceSQLCommand.ExecuteReader()) #copy all rows to the server
}
catch
{
$ex = $_.Exception
Write-Error "Error: $($ex.Message)"
}
finally
{
$con.Close()
}