Powershell - Progress of CSV Export from SQL.

Hi,

I have the following code below.

How can I change the last line so I can use the SQL - Write-Progress cmdlet to update the display as CSV records
are being exported.

Something like a percentage complete etc.

Thanks,

Ward

<#
USE [TestDB]
GO

/****** Object:  Table [dbo].[Computers]    Script Date: 30/07/2014 8:45:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Computers](
	[IndexNumber] [int] NULL,
	[ComputerName] [nvarchar](50) NULL,
	[InvDate] [datetime] NULL
) ON [PRIMARY]

GO
#>


function fnInsert_Test($number_of_records)
{

    $connection_string = "Data Source=localhost;Initial Catalog=TestDB;Integrated Security=SSPI;" 
    $sqlconnection = new-object system.data.SqlClient.SQLConnection($connection_string);
    $sqlcmd = new-object system.data.sqlclient.sqlcommand
    $sqlconnection.Open()
    $sqlcmd.Connection = $sqlconnection

    $sql = "Insert into dbo.Computers (IndexNumber,ComputerName,InvDate) Values (@IndexNumber,@ComputerName,@InvDate);"

    $sqlcmd.CommandText = $sql    

    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@IndexNumber",[Data.SQLDBType]::Int))) | Out-Null
    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ComputerName",[Data.SQLDBType]::NVarChar, 50))) | Out-Null
    $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@InvDate",[Data.SQLDBType]::DateTime))) | Out-Null
    

    for ($i = 1; $i -le $number_of_records ; $i++)
    {
        $sqlcmd.Parameters[0].Value = $i
        $sqlcmd.Parameters[1].Value = "PC" + $i
        $sqlcmd.Parameters[2].Value = [DateTime]::Now
        $sqlcmd.ExecuteNonQuery() | Out-Null
    }

    $sqlconnection.Close();

}

function Get-ScriptDirectory
{
  $Invocation = (Get-Variable MyInvocation -Scope 1).Value
  Split-Path $Invocation.MyCommand.Path
}


# -------------------------------------------------------------------------------------------------
# fnConnect_To_SQL: Connect to SQL database
# -------------------------------------------------------------------------------------------------
function fnConnect_To_SQL($server,$db)
{
    $ServerInstance = $server
    $Database = $db
    $ConnectionTimeout = 500

    $QueryTimeout = 500
    $QueryTimeout = 50000

    $conn=new-object System.Data.SqlClient.SQLConnection
    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
    $conn.ConnectionString=$ConnectionString
    $conn.Open()

    return $conn
}

# -------------------------------------------------------------------------------------------------
# fnRun_SQL_Dataset: Return SQL dataset.
# -------------------------------------------------------------------------------------------------
function fnRun_SQL_Dataset($conn,$sql)
{
    $cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
    $cmd.CommandTimeout = 500
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $data = $ds.Tables 

    return $data
}

$script_directory = Get-ScriptDirectory 
$csv_file = $script_directory + "\test.csv"

fnInsert_Test 10000


$localdb = fnConnect_To_SQL "localhost" "TestDB"
$sql_data = fnRun_SQL_Dataset $localdb "select * from Computers"

$sql_data | Export-Csv -NoTypeInformation -Force -Path $csv_file -Encoding Unicode

Select allOpen in new window