MsSql pull and load to Postgresql

[ajh60]

Can you give me a sample of how I could have 2 connections the first connection is mssql where I want to pull data and the 2nd connection is a backend PostgreSql DB where I want to load that info
THanks

[mithrandyr]

@ajh60 Sure, happy to provide an example.

#first connection -- the '-ConnectionName' parameter is key
Open-SqlConnection -SqlServer "a-sql-server" -SqlDatabase "a-database" -ConnectionName "src"

#second connection
Open-PostGreConnection -Server "a-postgre-server" -Database "some-db" -ConnectionName "dst"

#we can see that there are 2 connections
Show-SqlConnection -All  #this will list the connection names
Show-SqlConnection -All | Show-SqlConnection #piping the names into Show-SqlConnection to get details

#now for the main event, getting data from MSSQL into PostGre
# the following is simple and assumes that the structure of the data in MSSQL matches the structure of the data in Postgre (so columns are in the same order, same data types, etc...
Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceTable "source-table" -DestinationConnectionName "dst" -DestinationTable "destination-table"

#the output of the above command will tell you how many rows were bulk inserted into the destination connection.

There is alot more you can do.. you can use a sourcequery instead of source table, you can provide parameters to that query.. you can do columnMapping, you can determine how large the batch sizes are and whether to be notified with progress. Let me know if you have more questions.

[ajh60]

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

[mithrandyr]

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]

This is what I'm trying to do.... how do I fit it for SimplySql model and to have error handling and performance based... # Define connection strings for MS SQL and PostgreSQL $mssqlConnectionString = "Server=xxxxx;Database=Lobe;Integrated Security=True;" $pgsqlConnectionString = "Host=fw-ts-lobearch;Port=5432;Username=xxxx;Password=xxxxx;Database=lobe_arch;" # Create connection to MS SQL Server $mssqlConn = New-Object System.Data.SqlClient.SqlConnection $mssqlConn.ConnectionString = $mssqlConnectionString $mssqlConn.Open() # Execute a query to retrieve data $mssqlCmd = $mssqlConn.CreateCommand() $mssqlCmd.CommandText = "SELECT * FROM Image_Classification_Master" $mssqlReader = $mssqlCmd.ExecuteReader() # Create connection to PostgreSQL $pgsqlConn = New-Object Npgsql.NpgsqlConnection $pgsqlConn.ConnectionString = $pgsqlConnectionString $pgsqlConn.Open() # Prepare the insert command for PostgreSQL $pgsqlCmd = $pgsqlConn.CreateCommand() # Loop through the data retrieved from MS SQL Server while ($mssqlReader.Read()) { # Prepare the data for insertion into PostgreSQL $dataToInsert = @() for ($i = 0; $i -lt $mssqlReader.FieldCount; $i++) { $dataToInsert += $mssqlReader.GetValue($i) } # Define the INSERT query for PostgreSQL $pgsqlCmd.CommandText = "INSERT INTO myTable VALUES ($dataToInsert)" $pgsqlCmd.ExecuteNonQuery() } # Close the connections $mssqlConn.Close() $pgsqlConn.Close()

…

On Thu, Aug 29, 2024 at 10:25 AM Mithrandyr ***@***.***> wrote: 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. — Reply to this email directly, view it on GitHub <#166 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BBL64GY2A6WUDDG4ZTH2YWDZT4VLZAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANBYHAYTMMI> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[mithrandyr]

@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

[ajh60]

I've had a chance to get back to this and now running into this. # Load data from MSSQL to PostGre Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceTable "Image_Classification_Master" -DestinationConnectionName "dst" -Verbose -BatchSize 5000 Supply values for the following parameters: VERBOSE: Performing the operation "Execute bulkloading into ''" on target "dst". I never see it come back with any messages... I set it to a small number 5000 but nothing returns

…

On Mon, Sep 2, 2024 at 11:05 AM Mithrandyr ***@***.***> wrote: 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. — Reply to this email directly, view it on GitHub <#166 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BBL64G357WBBUWYUJUBTDPLZUR5CXAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANJSGIZDONI> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[mithrandyr]

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]

I see this error in Log: 2024-09-16 12:55:37.295 EDT [428] ERROR: invalid byte sequence for encoding "UTF8": 0x00 2024-09-16 12:55:37.295 EDT [428] CONTEXT: COPY image_classification_master, line 1, column spoolstarttime

…

On Mon, Sep 16, 2024 at 12:09 PM Mithrandyr ***@***.***> wrote: 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. — Reply to this email directly, view it on GitHub <#166 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BBL64G6KRADY4V6KX6FMMLTZW37EJAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANRWGE3DEMQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[mithrandyr]

@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.

[ajh60]

I can try that, but how will I be able to handle the invalid data?

…

On Mon, Sep 16, 2024 at 4:24 PM Mithrandyr ***@***.***> wrote: @ajh60 <https://github.com/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. — Reply to this email directly, view it on GitHub <#166 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BBL64GZOWCE3A42FHS6RZT3ZW45ANAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANRWGM3TSNY> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[mithrandyr]

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.

[ajh60]

okay I'm back to having a table in mssql and that same table defined in Postgre I'm using the invoke-sqlbulkcopy but I run into this error. I don't understand the message since both tables have this field. 14:02:42.610 EDT [20212] ERROR: column "itemid" of relation "qad_item_attribute" does not exist 2024-10-14 14:02:42.610 EDT [20212] STATEMENT: COPY qad_item_attribute (itemId, Item_attribute, Item_cvalue, item_modified, ItemType, ProdLine, Domain) FROM STDIN (FORMAT BINARY)

…

On Mon, Sep 16, 2024 at 4:51 PM Mithrandyr ***@***.***> wrote: 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. — Reply to this email directly, view it on GitHub <#166 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BBL64GZMXAH3WQ72RBKZ22TZW5ADRAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANRWGQYDAOI> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[mithrandyr]

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.