Written by 11:42 Business intelligence, Languages & Coding, PowerShell, SSIS

Execute Powershell in SSIS Using C#

Introduction

PowerShell is a Shell included in Windows to automate tasks in the operative system and other applications like SQL Server, SharePoint and Internet Information Services. You can create reports, start services, check the hard drive space, verify the RAM and more.

PowerShell can now be installed in Linux, Docker, and Mac.

You can use loops, comparisons, conditionals and more to create powerful scripts to automate tasks like creating Virtual Machines or SQL Servers in Azure.

In this new article, we will learn how to use the script task to invoke PowerShell in C#. The script task in SQL Server Integration Services (SSIS) allows using C# or VB code to extend the SSIS features.

Requirements

  1. SQL Server
  2. SSIS Installed (included with the SQL Server installer)
  3. SQL Server Data Tools (SSDT) installed
  4. PowerShell installed (usually installed by default in the operative system)

Get started

The article will include the following topics:

  1. Get the list of services using PowerShell in C#.
  2. How to send SSIS Parameters to PowerShell using the script task.
  3. How to use the PowerShell.addscript function.
  4. How to invoke a PowerShell script in C#

Get the list of services using PowerShell

In your operative system, open the PowerShell or the PowerShell ISE (Integrating Scripting Environment):

With PowerShell ISE, it is easier to create a script in PowerShell because it is easier to edit, save and debug the code. You can also have an intelligence which helps you to create your code faster with the syntax coloring and context sensitive help.

We are going to run the Get-Service cmdlet. This cmdlet allows listing all the Services in a local or remote computer. In this example, we will list the local services:

How can we run this PowerShell cmdlet in the SSIS script Task?

Open SSDT and create an Integration Services Project. In the SSIS project created, drag and drop the script task to the design pane and name it PowerShell command:

In the code, you will need to add the system management automation:

using System.Management.Automation;

The System.Management.Automation is used to invoke PowerShell.

In the Script, go to Solution Explorer and right-click on References and click on Add Reference…

Press the Browse button and add the system.management.automation. This library is used to invoke PowerShell. If you do not have this dll, you can download it from here:

Once the reference is added, in the script task add the following code:

public void Main()
        {
            try
            { 
            // TODO: Add your code here


            PowerShell pshell = PowerShell.Create();
System.IO.StreamWriter file = new System.IO.StreamWriter("c:\\sqlserver\\test.txt");
            file.WriteLine("Get-Service");


            pshell.AddCommand("Get-Service");

            file.WriteLine("Service Status          Service Name");


   
            foreach (PSObject result in pshell.Invoke())
                {
                        file.WriteLine("{0,-24}{1}",
                        result.Members["Status"].Value,
                        result.Members["Name"].Value);

                } 
                file.Close();
            }
            catch (Exception e)
            {
   Dts.Events.FireError(18, "The process failed", e.ToString(), "",    0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

The code includes a try and catches to handle errors if they are present. If the code in the try section fails, the catch will fire and error. You can see the error in the output pane. You can check the output pane using the menu and selection View>Output. The Dts.Events.FireError will fire the error message in the Output pane if an error occurs during the execution of the script.

The code will execute the Get-Service cmdlet from PowerShell and store the information in a txt file named test.txt stored in the c:\sqlserver folder.

To write in a text, we will use the System.IO.StreamWriter class.

We are also using a foreach to get all the services and we are using the result.Members attribute to get the different columns. In this example, the status and name columns.

If we run the code, if it works fine, it will create a file named test.txt with the services and the status:

Service Status shows if the service is stopped, paused or running and Service name shows the name of the services.

How to send SSIS Parameters to PowerShell using the script task

In the next example, we will see how to send parameters from SSIS to PowerShell using the script task in C#. We will send the service name to the Get-Service cmdlet in order to get the service information of the service specified. In this example, we will show the service status of the Browser Service in PowerShell:

To get this information with the script task in C#, we will go to the Parameters tab and create a new parameter named ServiceName. The Data type will be string and the value will be Browser. This parameter will be used to specify the service name and send this information to the script task.

We want to get the service status of the Browser service using C# and the script task:

We will create a new script by copying and pasting the old script task used in the first example. Open the new script task and add the parameter ServiceName in the ReadOnly Variables property:

In the script task add replace the existing code in the public void main section with this code:

public void Main()
	{
            try
            {



                PowerShell pshell = PowerShell.Create();
  System.IO.StreamWriter file = new   System.IO.StreamWriter("c:\\sqlserver\\test.txt");
                file.WriteLine("Get-Service");

string servicename = Dts.Variables["$Package::ServiceName"].Value.ToString();
                pshell.AddCommand("Get-Service");
                pshell.AddArgument(servicename);

                file.WriteLine("Service Status          Service Name");



                foreach (PSObject result in pshell.Invoke())
                {
                    file.WriteLine("{0,-24}{1}",
                            result.Members["Status"].Value,
                            result.Members["Name"].Value);

                } 
                file.Close();
            }
            catch (Exception e)
            {
                Dts.Events.FireError(18, "The process failed", 
                e.ToString(), "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

The code is like the first example, but in this case, we are getting the SSIS ServiceName parameter value and storing the information in the service name C# variable:

string servicename = Dts.Variables["$Package::ServiceName"].Value.ToString();

We are sending the argument to the cmdlet. The argument is the service name:

pshell.AddArgument(servicename);

If we run the code, it will show the service status of the browser service in the test.txt file:

How to use the powershell.addscript function

The method AddCommand invokes cmdlets and you can use AddArgument or AddParameter to add arguments and parameters. However, it is sometimes difficult to work with multiple parameters and commands. With the AddScript method is used to run PowerShell script directly.

In this example, we will run the following cmdlet in PowerShell:

get-service | select status,name -last 5 | ConvertTo-Html > c:\sqlserver\services.htm

The cmdlet shows the services, including the status of the service and name of the last 5 services and the results are stored in an HTML file named services.htm stored in the c:\sqlserver path.

To run this cmdlet using C#, the following code will be used:

Public void Main()
{
            try
            {

            	PowerShell ps = PowerShell.Create().AddScript("get-service | select               status,name -last 5 | 
            ConvertTo-Html > c:\\sqlserver\\services.htm");
                ps.Invoke();

          }
            catch (Exception e)
            {
                Dts.Events.FireError(18, "The process failed", 
                e.ToString(), "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
}

Note that in all the examples, you need to add the system.management.automation dll and namespace in the scripts.

In this new code, we are using the AddScript to invoke the cmdlet:

PowerShell ps = PowerShell.Create().AddScript("get-service | select               
status,name -last 5 | ConvertTo-Html > c:\\sqlserver\\services.htm");

And finally, we are invoking the script to execute it:

ps.Invoke();

If everything is OK and if you run the script task, an HTML file named services.htm will be created with the last 5 services including the name and status:

How to invoke a PowerShell script in C#

If we have a PowerShell script with the ps1 extension. How can we execute it in C#?

The next example will show how to do it. We will need a script first named psScript.ps1.

The script will contain the following cmdlets:

$servicename=”wisvc”

Get-Service $servicename | select status,name | ConvertTo-HTML | Out-File C:\sqlserver\Test2.htm

The code assigns to a variable named $servicename the value of one service named wisvc (Windows Insider Service). The Get-Service will show the status and name of this service and show the results in an HTML file named test2.htm.

To run these cmdlets in C#, we will use the following code:

public void Main()
{
            try
            {
                string text =  System.IO.File.ReadAllText(@"C:\sqlserver\psScript.ps1");
                PowerShell ps = PowerShell.Create().AddScript(text);
                ps.Invoke();

          }
            catch (Exception e)
            {
                Dts.Events.FireError(18, "The process failed", 
                e.ToString(), "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
}

We are using the System.IO.File.ReadAllText to get the text from the psScript.ps1 stored in the c:\sqlserver path.

If we run the script task, an HTML file named test2.htm will be created in the c:\sqlserver folder:

Conclusions

In this example, we learned how to invoke PowerShell cmdlets using C#. You can use Visual Studio or any tool of your preference. In this example, we used the script task. Remember that the system.management.automation.dll is required to run the functions to invoke PowerShell.

We also learned how to pass an SSIS parameter to PowerShell.

Finally, we can run the AddCommand method to invoke the PowerShell cmdlets combined with the AddParameter and AddArgument methods or you can use the AddScript method, which is easier to use in my opinion.

If you have questions or concerns, feel free to write your comments in this article.

References

For more information about PowerShell, SSIS, and the script task:

Useful tool:

Devart SSIS Data Flow Components – allow you to integrate database and cloud data via SQL Server Integration Services.

Tags: , , Last modified: September 23, 2021
Close