The given value of type String from the data source cannot be converted to type

Description:I am getting error while importing CSV file data into SQL table. I am trying to export data from a table into a CSV file and then import that CSV file into another table.The error is due to null values in non string data types columns in a table.

Error:System.InvalidOperationException: The given value of type String from the data source cannot be converted to type int of the specified target column. —> System.FormatException: Failed to convert parameter value from a String to a Int32.

SW stack:Poweshell 4.0,sql server 2012,table schema is same for both source and destination table.

Code: following is stripped code that i am using to import the CSV file.

  ####### Build the sqlbulkcopy connection, and set the timeout to infinite######### 
    $BulkCopy = New-Object Data.SqlClient.SqlBulkCopy ($ConnectionString,[System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
    $BulkCopy.DestinationTableName = $TableName
    $BulkCopy.bulkcopyTimeout = 0
    $BulkCopy.batchsize = $BatchSize

    # Create the datatable, and autogenerate the columns. 
    $DataTable = New-Object System.Data.DataTable

    # Open the text file from disk 
    $Reader = New-Object System.IO.StreamReader ($CSVFileFullName)
    $Columns = (Get-Content $CSVFileFullName -First 1).Split($CSVDelimiter)

    ##get Column Names from first line
    if ($FirstRowColumnNames -eq $true) {
      $null = $Reader.readLine()

    #create data table with cloumn names read from first line  
    foreach ($Column in $Columns) {
      $null = $DataTable.Columns.Add()

    # Read in the data, line by line 
    while (($Line = $Reader.readLine()) -ne $null) {

      $null = $DataTable.Rows.Add($Line.Split($CSVDelimiter))

      if (($RowCounter % $BatchSize) -eq 0) {
        Write-Output "$RowCounter rows have been inserted in $($elapsed.Elapsed.ToString())."

    # Add in all the remaining rows since the last clear 
    if ($DataTable.Rows.Count -gt 0) {

    Write-Output "Script complete. $RowCounter rows have been inserted into the database."
    Write-Output "Total Elapsed Time: $($elapsed.Elapsed.ToString())"


That’s really a SQL Server error, not a PowerShell thing. You’re trying to put string data into a SQL Server integer column. Without seeing your CSV data and the SQL table, I can’t tell you specifically what the problem is (and no need to post those things; again, this is really a SQL Server problem).

As a test, consider using SQL Server to manually import the CSV (e.g., via SSIS maybe). You’ll get a much more specific error message.

I tried importing below csv content using SSIS and it worked fine but when I try to import the same csv file using powershell then I am get above specified error.

SSIS converts the empty string to 0 (in this case).

why? sql does this.

INTO newtable
FROM table

Sorry Dan but I didn’t get your point.