Exporting content of a variable into a SQL table

PowerShell Help
1

Hello, I have tried various ways of trying to write the output into a SQL table. I have simplified the code again. Can anyone give me any idea where I might be going wrong please?

Login-PowerBIServiceAccount

#change the first number to the number of days you want to go back
1..1 |

foreach {

$Date = (((Get-Date).Date).AddDays(-$_))
$StartDate = (Get-Date -Date ($Date) -Format yyyy-MM-ddTHH:mm:ss)
$EndDate = (Get-Date -Date ((($Date).AddDays(1)).AddMilliseconds(-1)) -Format yyyy-MM-ddTHH:mm:ss)

$activities = Get-PowerBIActivityEvent -StartDateTime $StartDate -EndDateTime $EndDate

#Use to display output
Write-Output $activities
}
2

Simm David,
Welcome to the forum. :wave:t4:

Since your code is incomplete you may start with explaining what you actually want to do and what is not working as expected. :wink:

Do you get any error messages? If yes please post them as well (formatted as code as well)

Thanks in advance.

1 Like
3

Oops, I have added the closing bracket. It is complete now. I just need some general advice on how to put the variable $activities into a table. I got myself into a pickle so just stripped it back to Write_Output again.

And thank you for the welcome. I imagine that I will be a regular here!

4

I don’t have access to a Power BI tenant and I’ve never used this cmdlet. So I don’t know how your output looks like. If it’s about the output on the console you could pipe it to Format-Table.

Since we do work with objects and properties in PowerShell the output you see on the console automatically formatted by PowerShell does not matter for cmdlets as they take the objects and properties - and not the pixels you see on the console. :wink:

So the actual question should be: Why do you think you have to do something different? How do you want to proceed with the data you just collected?

1 Like
5

If you are just trying to take a PSObject and send it to SQL, there are multiple methods. Some require tools to be installed like SQL Management Studio that installs SQL modules and then there are .NET methods that do not pre-installed tools. It’s also going to matter if you are doing an insert\update, overwriting the table each time, only appending new records, , the number of records, etc. to build the code. Have your searched for anything on inserting via SQL?

PowerShell Invoke-SQLCmd outputs DataTables you can INSERT into SQL Server (mssqltips.com)

6 methods to write PowerShell output to a SQL Server table (sqlshack.com)

Insert data into a SQL Server Table using Powershell using Invoke-SQLc – SQLServerCentral