Powershell output into a table using variable

• SQL-Learner

  SSCommitted

  Points: 1537

  More actions

  March 1, 2017 at 11:32 am

  #320818

  Hi,

  I'm saving powershell command get-process in 'service.ps1'. Using SSIS in 'Executive Process Task given executable = "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" and  
  argument = "C:\Users\XYz\MyDocument\service.ps1".
  create a user variable 'Result' where Datatype is object.
  Given standard output variable = User::Result. 

  used precedent constraint and attached to Data flow task.

  In data flow task used source as script component.
  In custom properties > Readonly variables selected User::Result.

  In input and output > i have created a output and all the output column names
  Handles
  NPM(K)  
  PM (K)  
  WS(K)  
  CPU(s)    
  Id 
  SI
  ProcessName.

  In the Edit script I used this piece of code.

  #region Help: Introduction to the Script Component
  /* The Script Component allows you to perform virtually any operation that can be accomplished in
  * a .Net application within the context of an Integration Services data flow.
  *
  * Expand the other regions which have "Help" prefixes for examples of specific ways to use
  * Integration Services features within this script component. */
  #endregion

  #region Namespaces
  using System;
  using System.Data;
  using System.Data.OleDb;
  using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
  using Microsoft.SqlServer.Dts.Runtime.Wrapper;
  #endregion

  [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
  public class ScriptMain : UserComponent
  {
    public override void CreateNewOutputRows()
    {
     // Set up the DataAdapter to extract the data, and the DataTable object to capture those results
     OleDbDataAdapter da = new OleDbDataAdapter();
     DataTable dt = new DataTable();
    
     // Extract the data from the object variable into the table
     da.Fill(dt, Variables.Result);

     // Since we know the column metadata at design time, we simply need to iterate over each row in
     // the DataTable, creating a new row in our Data Flow buffer for each
     foreach (DataRow dr in dt.Rows)
     {
      // Create a new, empty row in the output buffer
      AzureBuffer.AddRow();

      // Now populate the columns
      AzureBuffer.Handles = dr["Handles"].ToString();
      AzureBuffer.PMK = dr["PMK"].ToString();
      AzureBuffer.CPUs = dr["CPUs"].ToString();
      AzureBuffer.NPMK = dr["NPMK"].ToString();
      AzureBuffer.ID = dr["ID"].ToString();
      AzureBuffer.ProcessName = dr["ProcessName"].ToString();
      AzureBuffer.SI = dr["SI"].ToString();
      AzureBuffer.WSK = dr["WSK"].ToString();
     }
    }
  }

  used OLE DB as destination. created a table in destination. all the mappings are connected everything is fine.

  When I run the package it is showing this error. Please help me out.

  [Script Component [35]] Error: System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record.
  Parameter name: adodb
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)

  Script component run time error:
  at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String srcTable, Boolean multipleResults)
   at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet)
   at ScriptMain.CreateNewOutputRows()
   at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers, OutputNameMap OutputMap)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)

  Thanks in advance