A quick ETL PowerShell script to copy data between databases

I’ll start this post by announce that we Just moved our SQLHelper powershell module from CodePlex into GitHub, you can grab it here:

https://github.com/DevScope/sql-powershell-modules

We also made a new sample to demonstrate the simplicity of use of this module, the objective is to build a very simple ETL script that make these steps:

  1. Query a collection of tables from a source database (can be any type: SQL/Oracle/…)
  2. Insert into a SQLServer database using bulk loading
    • If the table does not exist  in the destination, it must be created automatically

The powershell code:

Import-Module ".\SQLHelper.psm1" -Force

$sourceConnStr = "sourceconnstr"

$destinationConnStr = "destinationconnstr"

$tables = @("[dbo].[DimProduct]", "[dbo].[FactInternetSales]")

$steps = $tables.Count
$i = 1;

$tables |% {

    $sourceTableName = $_
    $destinationTableName = $sourceTableName

    Write-Progress -activity "Tables Copy" -CurrentOperation "Executing source query over '$sourceTableName'" -PercentComplete (($i / $steps)  * 100) -Verbose

    $sourceTable = (Invoke-DBCommand -connectionString $sourceConnStr -commandText "select * from $sourceTableName").Tables[0]

    Write-Progress -activity "Tables Copy" -CurrentOperation "Creating destination table '$destinationTableName'" -PercentComplete (($i / $steps)  * 100) -Verbose

    Invoke-SQLCreateTable -connectionString $destinationConnStr -table $sourceTable -tableName $destinationTableName -force

    Write-Progress -activity "Tables Copy" -CurrentOperation "Loading destination table '$destinationTableName'" -PercentComplete (($i / $steps)  * 100) -Verbose

    Invoke-SQLBulkCopy -connectionString $destinationConnStr -data $sourceTable -tableName $destinationTableName                

    $i++;
}

Write-Progress -activity "Tables Copy" -Completed

ETLPowerShellScript

Advertisements

2 thoughts on “A quick ETL PowerShell script to copy data between databases

  1. Dear Rui, thank you for the module!
    I am looking for a powershell script to copy/replicate SQL tables between two not linked databases. Approach could be using csv as Export Import medium.
    Any ideas.
    Best regards
    Martin

    • Hi Martin,

      This approach would work very well if it is a manual operation. Basically you could connect with PBIDesktop to Database1, select the tables, do some transformation if needed and then use the script to insert data to Database2.

      But if you are looking into a more automated & periodic way to move data from DB1 to DB2, then I would recomend to build a PowerShell script and maybe you can find this module useful:

      https://www.powershellgallery.com/packages/SQLHelper/1.3.0.0

      It has cmdlets like “SqlBulkCopy” that automatically creates the destination table for you.

      Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s