Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

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

0
whorsfall
Asked:
whorsfall
1 Solution
 
QlemoC++ DeveloperCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now