The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column

Question

What I'm trying here is, to load the data from .xml file to the SQL server database using PowerShell Script.

My Script goes like:

$dataSource = 'dtsrc';                                       
$database = 'tempdb';   #DB

$connection = New-Object System.Data.SqlClient.SqlConnection;  #setting connection
$connection.ConnectionString = "Server=$dataSource;Database=$database;Integrated Security=True;";
$connection.Open();

$command = New-Object System.Data.SqlClient.SqlCommand;
$command.Connection = $connection;

$as = New-Object System.Data.SqlClient.SqlDataAdapter;
$as.SelectCommand = $command;
$filename = 'C:\DCT\XML\apc.xml';            #file to be loaded into sql server database table

$ds = New-Object System.Data.DataSet;
$ds.ReadXml($filename);    #reading from the file -- line gives output InferSchema

$dt = New-Object System.Data.DataTable;

$dt.Columns.Add("StartTime",[datetime]);
$dt.Columns.Add("EndTime",[datetime]);
$dt.Columns.Add("Status",[int]);
$dt.Columns.Add("ProcessedTime",[datetime]);
$dt.Columns.Add("ScheduleId",[guid]);
$dt.Columns.Add("Model",[string]);
$dt.Columns.Add("ModelVersion",[string]);
$dt.Columns.Add("ModelVersionState",[string]);
$dt.Columns.Add("ModifiedTime",[datetime]);
 $dt.Columns.Add("WriteBackLastRunTime",[datetime]);
 $dt.Columns.Add("ModifiedBy",[string]);
 $dt.Columns.Add("ModelType",[int]);
 $dt.Columns.Add("IsTimeRange",[int]);#b
 $dt.Columns.Add("WriteBackStatus",[int]);
 $dt.Columns.Add("RetryWriteBack",[int]);#b
 $dt.Columns.Add("NumOfRetry",[int]);
 $dt.Columns.Add("FrequencyUnits",[int]);
 $dt.Columns.Add("ScheduleType",[int]);
 $dt.Columns.Add("CustomType",[int]);
 $dt.Columns.Add("ShiftStartTime",[datetime]);
 $dt.Columns.Add("StartWeekDay",[int]);
 $dt.Columns.Add("EndWeekDay",[int]);
 $dt.Columns.Add("WritebackProcessedTime",[datetime]);
 $dt.Columns.Add("DiagStatus",[int]);
 $dt.Columns.Add("AccountingPeriodCase_PK_ID",[guid]);

 $dt = $ds.Tables[0];

$bcp = New-Object 'Data.SqlClient.SqlBulkCopy' $connection; #bulkcopy to the destination table.
$bcp.DestinationTableName = 'dbo.tempor';

#$bcp.ColumnMappings.Count;
$bcp.ColumnMappings.Clear();

$bcp.ColumnMappings.Add('StartTime','StartTime');
$bcp.ColumnMappings.Add('EndTime','EndTime');
$bcp.ColumnMappings.Add('Status','Status');
$bcp.ColumnMappings.Add('ProcessedTime','ProcessedTime');
$bcp.ColumnMappings.Add('ScheduleId','ScheduleId');
$bcp.ColumnMappings.Add('Model','Model');
$bcp.ColumnMappings.Add('ModelVersion','ModelVersion');
$bcp.ColumnMappings.Add('ModelVersionState','ModelVersionState');
$bcp.ColumnMappings.Add('ModifiedTime','ModifiedTime');
$bcp.ColumnMappings.Add('WriteBackLastRunTime','WriteBackLastRunTime');
$bcp.ColumnMappings.Add('ModifiedBy','ModifiedBy');
$bcp.ColumnMappings.Add('ModelType','ModelType');
$bcp.ColumnMappings.Add('IsTimeRange','IsTimeRange');
$bcp.ColumnMappings.Add('WriteBackStatus','WriteBackStatus');
$bcp.ColumnMappings.Add('RetryWriteBack','RetryWriteBack');
$bcp.ColumnMappings.Add('NumOfRetry','NumOfRetry');
$bcp.ColumnMappings.Add('FrequencyUnits','FrequencyUnits');
$bcp.ColumnMappings.Add('ScheduleType','ScheduleType');
#$bcp.ColumnMappings.Add('CustomType','CustomType');
#$bcp.ColumnMappings.Add('ShiftStartTime','ShiftStartTime');
#$bcp.ColumnMappings.Add('StartWeekDay','StartWeekDay');
#$bcp.ColumnMappings.Add('EndWeekDay','EndWeekDay');
$bcp.ColumnMappings.Add('WritebackProcessedTime','WritebackProcessedTime');
$bcp.ColumnMappings.Add('DiagStatus','DiagStatus');
$bcp.ColumnMappings.Add('AccountingPeriodCase_PK_ID','AccountingPeriodCase_PK_ID');    

if ($connection.State -ne [Data.ConnectionState]::Open) {

    'Connection to DB is not open.'

    Exit

}

$bcp.WriteToServer($dt);        #writing to server 
$connection.Close();

The error I'm facing is:

Exception calling "WriteToServer" with "1" argument(s): "The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column." At C:\Documents\DCT\SavedScripts\XMLtoDB\AccountingPeriodCases\sample.ps1:91 char:1
+ $bcp.WriteToServer($dt);        #writing to server
+ ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

and the table I created has the columns with datatypes:

The thing is, I guess i need to convert the String that is in the datatable under the column ScheduleId and AccountingPeriodCase_PK_ID as they are not getting converted to uniqueidentifier.

I tried using [System.Guid]::Parse($dt.Columns[4]); [System.Guid]::Parse($dt.Columns[24]);

but the error repeats.

The xml content to be loaded under thr ScheduleId and AccountingPeriodCase_PK_ID looks like:

<ScheduleId>db6f3178-4702-456c-904b-2cd833b54efa</ScheduleId>
<AccountingPeriodCase_PK_ID>9544940d-8d1a-e711-80d3-0050569c2fb6</AccountingPeriodCase_PK_ID>

Could anyone help me resolve this issue? Thank you

Answer 1

From a scenario, which I had faced earlier, it comes down to the source of your input.

The thing is if you are reading from an XML file, then you are bound to get an error, because while reading from a flat-file(txt, csv) or an XML, PowerShell will resolve the undefined types to String. The trick is to have some sort of control over the source. If your source is a PowerShell variable such as datatable or an array, then you can directly insert values from this variable to your destination table, since the original data types of the columns in a table are preserved.

This is the approach which I followed. In my case, the source was a result of the Invoke-Sqlcmd which preserved the original data types and hence made insertion error less.

Again as mentioned in the comment by OP, he was parsing values based on columns and not rows. This could also lead to an error. For example, there is one more way of inserting values in a table which is using the INSERT statement. Mind you that, the SQL Server INSERT statement inserts value on a row-level and not a column level. In such a scenario, parsing the values column wise may fail.