Execute PowerShell script, via Execute Process Task, results into a Table...

• d_george

  SSCrazy

  Points: 2462

  More actions

  March 10, 2021 at 8:26 pm

  #3856183

  I'd like to Execute PowerShell script, via Execute Process Task and store the results into a SQL Table.

  Can this be done?

  If yes, could you point me to an example?

  Thank you

• TangoVictor

  SSChampion

  Points: 10976

  More actions

  March 10, 2021 at 10:58 pm

  #3856264

  Yeah you can do that. Picture attached for how I configured the exec process task.

  There are various methods for writing to SQL from PS, look at this link for more info:

  SQL Shack examples

  I don't do this a lot but did write some powershell that went through our SSAS server and found all the data sources used in all our cubes.

  In the PS I have a function for inserting records. I'm sure there are better ways to do this, but it worked for what I was doing.

  Here's an example of what I used but I would look at the SQL Shack examples as those are probably better. My method is susceptible to sql injection so I wouldn't recommend it.

  Function WriteToSQL
  {
  param($fld1, $fld2, $fld3)
  
  $InsertResults = @"
  INSERT INTO [dbo].[myTable](field1,field2,field3)
  VALUES ('$fld1','$fld2','$fld3')
  "@      
  Invoke-sqlcmd @params -Query $InsertResults
  }

  Attachments:

  You must be logged in to view attached files.

• d_george

  SSCrazy

  Points: 2462

  More actions

  March 12, 2021 at 3:06 pm

  #3856975

  Thank you! I will check it out.