Function to create SQL TABLE does not works well using Powershell

Question

I need to get the values from a Excel Sheet (in a shared folder) to export it and then create a SQL Table in my instance. After of this I will execute a Stored Procedure (I already created) in order to create extended properties for a specific schema on tables,columns and rows.

By the moment I was able to get the data from excel sheet (using powershell) and saved in a variable but the problem is that I needed to add one column called ‘SchemaName’ because the Stored Procedure need to finds only values with the same schema name. (In the table will exist many values with differences schema names )
So in order to move forward this.
I added a column with the schema name required but even if the output of the variable is correct when I try to create the table pop up an error or just import data without the column SchemaName

Here is my code:

$DataSource= '\\servername\temp\test.xlsx'    -sharedfolder path
$cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';Data Source=c:\temp\test.xlsx;Mode=Share Deny Write;Jet OLEDB:Engine Type=37;";
$cnStr
$cn = New-Object System.Data.OleDb.OleDbConnection $cnStr;
$cn.Open()

$cmd = $cn.CreateCommand()

$cmd.CommandText = "SELECT * FROM [Sheet1$]"
$rdr = $cmd.ExecuteReader();

$dt = new-object System.Data.DataTable

$dt.Load($rdr)


$Database_Name = 'DBTest'
$Table_Name = 'Tbltest'
$Schema_Name = 'Schematest'


$dt | add-member -membertype noteproperty "Schema_Name" -Value $Schema_Name

$dt | write-dbadbtabledata -sqlinstance 'servername' -databasename $Database_Name -table $table_name -autocreatetable

Example of how looks the sheet

Rol	Member	Description
Admin	name + email	Description of the rol
Data Lead	name + email	Description of the rol
Arquitect	name + email	Description of the rol
DBA	name + email	Description of the rol
QA	name + email	Description of the rol

Also I tried with other functions / methods to create the table but the main reason that I believe is the columns seems that is added to the variable but not in reality.

asked 2021-09-02> by SakZepelin

---

Answer

Let’s unpack at this line:

$dt | add-member -membertype noteproperty "Schema_Name" -Value $Schema_Name

$dt by itself just outputs the object. In this case, we’re sending it down the pipeline to the next command.

add-member adds the member to the “thing” going down the pipeline, and passes that along. But in this case, you don’t give it anywhere to go… The pipeline ends there and there’s to assignment back into a new object. When you run it at the PowerShell prompt, you’ll get output to your window, but not saved anywhere.

If you want to modify the $dt object you’ll have to explicitly send it there, just like you do with setting other variable/object values:

$dt = $dt | add-member -membertype noteproperty "Schema_Name" -Value $Schema_Name

You could also just skip that assignment and send it right on down the pipeline:

$dt | add-member -membertype noteproperty "Schema_Name" -Value $Schema_Name -PassThru | `
          write-dbadbtabledata -sqlinstance 'servername' -databasename $Database_Name -table $table_name -autocreatetable

answered 2021-09-02 by Andy Mallon