Thanks ... Do I need to be P/S v7 to make this work? Also in the mssql side I need to run a query to gather info how do I get that in a variable to push to invoke-sqlbulkcopy ? This query could contain 100,000 records so will memory and performance be an issue..or how do youavoid this...

Many thanks again for your quick reply

The module is supported on both PS5.1 and PS7+
did you try running Get-Help Invoke-SqlBulkCopy ? I try to have as much documentation included in the module as possibly... there is a parameterset specifically for running a query to get data on the source side. As for performance... total amount of records doesn't matter, only the batch size.. so if each record is large (multiple text/binary columns with sizing around 1MB each), then you may want a smaller batch size if memory consumption becomes an issue. The whole point of bulkcopying is to handle large volumes of data -- try it out and see what works for your scenario.

@ajh60 SimplySql removes the need to manually create .NET objects -- so all of that can be replaced with the sample I provided above.

One you have opened your two connections by the relevant Open-*Connection cmdlets, then you just use Invoke-SqlBulkCopy - its that simple.

# source connection
Open-SqlConnection -SqlServer "xxxxx" -SqlDabatase "Lobe" -ConnectionName "src"
# destination connection
Open-PostGreConnection -Server "fw-ts-lobearch" -Database "lobe_arch" -Credential (Get-Credential) -ConnectionName "dst"
# Load data from MSSQL to PostGre
Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceTable "Image_Classification_Master" -DestinationConnectionName "dst" -DestinationTable "myTable"
# close all connections
Show-SqlConnection -all | Close-SqlConnection

The -Verbose won't show what you expect (which is, I believe, to see progress). If you want to see progress indicators, use the -Notify switch -- that will cause a progressbar to generate with information on how many records have processed so far. the output of Invoke-SqlBulkCopy will be the number of records inserted.
The -BatchSize parameter indicates how many records will be sent at a time, but all the data will be eventually sent. What makes bulk-loading effecient, is that data is not processed record by record, but rather batch by batch. Depending on the shape/size of an individual records, different batchsizes might be more performant, hence why this is a variable that you can control.

@ajh60 -- start with something very simple. try bulk copying just one text column (this way you can get confident with using the module, then try the more complex scenarios and you won't worry about whether the problem is your usage of the module or something else...). Working with byte (Binary) data can be complex -- so that error you are getting is from the destination server telling you that the data is invalid.

once you identify exactly what is wrong -- you will have to figure out how to transform that data before copying. It could be your source and destination tables do not have the same schema (name/order of columns and interchangeable data types). Also database vendors sometimes handle data types (especially the complex ones) differently. So from the error its hard to know exactly what the issue is, but it might be a data type mismatch.

Ok -- well, its saying that it can't find the column. I know that column names are not case-sensitive in MSSQL, but they might be in Postgre, so confirm that.

At this point the best way I could assist would be to be able to recreate the situation -- that would require:

1. A sql statement that creates your table in MSSQL
2. a sql statement that creates your table in Postgres
3. a sql statement that loads data into MSSQL
4. the PowerShell script you are using to do the bulkcopy

basically, I need enough information to fully and completely duplicate what you are trying to do.

Ideally -- if this were all wrapped up into a single PS script -- that would be ideal -- then I can run it on my side and see what is happening.

@ajh60 -- yes, bulk inserts are always just that inserts and thus they will append to the table.

to use a query with Invoke-SqlBulkyCopy you will use -SourceQuery instead of -SourceTable

Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceQuery "Select TOP 10 * FROM Image_Classification_Master" -DestinationConnectionName "dst" -DestinationTable "myTable"

I would assume so.

@ajh60
Two things, (1) you can just "reply" to previous discussion on this page instead of generating a new "comment" everytime (this may help others in following the issue more easily).

(2) -- copying data from one database to another does require paying attention to how each database stores data. I would do some research on how MSSQL stores data in an Image column and how PostGre stores data in a ByteA column -- in particular making sure that they are equivalent columns. I would also do a simple test where you copy a single row from MSSQL to PostGre (and probably a single column) so you can be very clear on which set of data is giving an issue.

This is all I have offhand.

Yes, the SourceQuery parameter is just that.. a SQL statement and so it can include where clauses, etc.

# filter values in query
Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceQuery "Select * FROM Image_Classification_Master WHERE ImageName LIKE '20240618%'" -DestinationConnectionName "dst" -DestinationTable "myTable"
# Filter value from variable
$DateAsString = "20240618%"
Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceQuery "Select * FROM Image_Classification_Master WHERE ImageName LIKE @search" -DestinationConnectionName "dst" -DestinationTable "myTable" -SourceParameters @{search = $DateAsString}

Glad this is helpful!

@ajh60 -- yes that should work -- -SourceQuery will take any valid SQL query.

@ajh60 you can use $error to get a look at the errors and explore details of the particular exception.

Its not about the try/catch. anytime an error is generated, its stored in the $error automatic variable. So, after you run your script and you get an error, you can explore $error on the command line. For instance, to see the last error generated: $error[0] | Select * will show you everything about that errorrecord. You can dig into the details, (for instance there is an exception property) and from there find out more of what is happening then what will show up in your console.

@ajh60 -- and you'll notice that the exception is wrapping a npgsqlexception -- so you need to continue interrogating the error record object and all its properties and sub-properties to get to the root issue (the error being returned by the database engine).

@ajh60 -- sorry for the long delay in replying...
Batchsize determines how much is in a transaction -- so increasing it (up to memory limits) should increase speed. Other than that, not that I'm aware of. The actual implementation (for PostGre is this: NPGSQL BulkCopy and see SimplySql PostGre Implementation for BulkLoad)