How to Handle sql_Variant sql datatype using Powershell

Question

I am using the power shell to execute one of the stored procedure which has one of the parameter with the datatype sql_Variant and resided in the SSISDB (System stored procedure)

As sql_variant supports multi datatype I am passing int,string & boolean data types.

stored procedure executing fine when I pass the string data type. but not the int and boolean.

Procedure Name is

Catalog.Set_Object_Parameter_Value

It has one Parameter with the datatype SQL_Variant. Below is screen shot.

While Inputting value based of some condition I will get int/string/bool datatype.

How Can I Handle/Convert those value to Sql_Variant data type before input using Power-shell.

Currently my power-shell command something like below.

$procSignature = "EXEC [catalog].[set_object_parameter_value] @object_type,@folder_name,@project_name,@parameter_name,@parameter_value,@object_name";
                            $cmd = New-Object System.Data.SqlClient.SqlCommand;
                            $cmd.Connection = $catalogConnection;
                            $cmd.CommandText = $procSignature;
                            $cmd.Parameters.AddWithValue("@object_type", $Scope ) | Out-Null;
                            $cmd.Parameters.AddWithValue("@folder_name", $FolderName ) | Out-Null;
                            $cmd.Parameters.AddWithValue("@project_name", $ProjectName ) | Out-Null;
                            $cmd.Parameters.AddWithValue("@parameter_name", $ParmeterName ) | Out-Null;
                            $cmd.Parameters.AddWithValue("@parameter_value", $ParmeterValue ) | Out-Null;
                            $cmd.Parameters.AddWithValue("@object_name", $object ) | Out-Null;

Answer 1

I had exactly the same issue with exactly the same SP. Try this:

$cmd.Parameters.AddWithValue("@parameter_value", $ParameterValue -As $ParameterValue.GetType().Name) | Out-Null;