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

Open in new window

LVL 1
whorsfallAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
As in http://www.experts-exchange.com/Q_28490795.html, the time consuming part is the processing by fnRun_SQL_Dataset, not the export of data. In particular, you don't do
$x = a_function_doing_a_lot_of_things()
$x | export-csv ...

Open in new window

in PowerShell. Rather you write
a_function_doing_a_lot_of_things() | export-csv

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.