Write-SqlTableData: Timeout expired

PowerShell Help
1

Hello All,

I am experiencing a error when writing csv data to an sql table with PowerShell. The data will start to write and display an increasing percentage but it will stop abruptly and return:

Write-SqlTableData:
Line |
21 | Write-SqlTableData -InputData $b -DatabaseName DATABASE -TableName …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The csv is just under 1,000,000 rows.

I am using the connection below:

$ServerName = "SERVER"
$DatabaseName = "DATABASE"
$Query = ""
$QueryTimeout = 0
$ConnectionTimeout = 0
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
Clear-Host;
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()

Both $QueryTimeout = 0 and $ConnectionTimeout = 0 are set to zero. Also, SSMS Execution time out is set to zero.

Here is my environment data.

Name                           Value
----                           -----
PSVersion                      7.2.1
PSEdition                      Core
GitCommitId                    7.2.1
OS                             Microsoft Windows 10.0.18363
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0
PSVersion                      7.2.1
PSEdition                      Core
GitCommitId                    7.2.1
OS                             Microsoft Windows 10.0.18363
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Any ideas why this timeout is happening?

Thanks,

2

Hi, welcome to the forum :wave:

That’s a big CSV.

Try the Import-DbaCsv command from the dbatools module.

3

matt-bloomfield thank you so much!! Your suggestion was just what I need. :ok_hand: