Change linked service / dataset connection string in an Azure Data Factory with PowerShell after deployment

When you connect an Azure Data Factory to GIT it will create json files per linked service, but the json file will contain some part of the connection string. If you want to change the connection string after deployment, you can use the following script.

# This script assumes the linked service json files can be found in the folder .\linkedService, relative to the location of this script.

param (




$root = $PSScriptRoot
$scriptName = $MyInvocation.MyCommand.Name
Write-Host "$scriptName - start"

Write-Host "Get all linked service json file names"
$linkedServicesPath = "$root\linkedService"
$names = Get-ChildItem -Path "$linkedServicesPath" -Name

Write-Host "Loop file names"
foreach ($name in $names)
    Write-Host "Find the text [$Find] and replace with [$Replace] in the file [$path]."
    $path = [System.IO.Path]::Combine($linkedServicesPath, $name)
    (Get-Content -Path "$path" -Raw).Replace("$Find", "$Replace") | Set-Content -Path "$path"

    $linkedServiceName = [System.IO.Path]::GetFileNameWithoutExtension($name)
    Write-Host "Update the linked service [$linkedServiceName] on ResourceGroupName [$ResourceGroupName] in DataFactoryName [$DataFactoryName] by using File [$path] on Azure.."
    Set-AzureRmDataFactoryV2LinkedService -ResourceGroupName "$ResourceGroupName" -DataFactoryName "$DataFactoryName" -Name "$linkedServiceName" -File "$path"

Write-Host "$scriptName - end"

Example linked service json file:
Note: The sql server database connections, that I use, use MSI for authentication.

	"name": "MySqlDb1LinkedService",
	"type": "Microsoft.DataFactory/factories/linkedservices",
	"properties": {
		"type": "AzureSqlDatabase",
		"typeProperties": {
			"connectionString": "Integrated Security=False;Encrypt=True;Connection Timeout=30;Data;Initial Catalog=MySqlDb1"

How to create an IIS application pool and web site on Windows 10 with PowerShell and Visual Studio Code

Tasks to create an IIS application pool and web site on Windows 10 with PowerShell and Visual Studio Code:

  • Install the Visual Studio Code plugin PowerShell 0.6.2
  • Create a file iis.configuration.ps1 in Visual Studio Code:

    # Note: When run with the Visual Studio Code PowerShell debugger, make sure you use the "x64" debugger,
    #       else you will get an error: New-WebAppPool : Cannot retrieve the dynamic parameters for the cmdlet.

    Import-Module WebAdministration

    # Create application pools
    New-WebAppPool -Name "myapp.localhost" -Force

    # Create websites
    New-Website -Name "myapp.localhost" -Port 80 -HostHeader "myapp.localhost" -ApplicationPool "myapp.localhost" -PhysicalPath "c:\projects\myapp\web" -Force

        • Create a launch.json in the same folder as the iis.configuration.ps1:

              "version": "0.2.0",
               "configurations": [
                      "name": "PowerShell",
                      "type": "PowerShell",
                      "request": "launch",
                      "program": "${workspaceRoot}/iis.configuration.ps1",
                      "args": [],
                      "cwd": "${workspaceRoot}/iis.configuration.ps1"
                      "name": "PowerShell x86",
                      "type": "PowerShell x86",
                      "request": "launch",
                      "program": "${workspaceRoot}/iis.configuration.ps1",
                      "args": [],
                      "cwd": "${workspaceRoot}/iis.configuration.ps1"

          Now you can debug / run the iis.configuration.ps1 file, by hitting F5, make sure you selected “PowerShell” and not “PowerShell x86” on the debug tab:


          Fix: Visual Studio 2013 / 2014–SQL Server 2014 – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet



          When you want to run / edit / debug PowerShell scripts inside Visual Studio 2013 / 2015 you will have to install the “PowerShell Tools for Visual Studio” Visual Studio extension found at:


          I wanted to run and debug a PowerShell script inside Visual Studio 2013 / 2015, on a machine that only had SQL Server 2014 installed as database server. This threw an exception: The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet


          Turns out you have to update the PowerShell “PSModulePath”, so it can find the PowerShell modules containing the SQL Server 2014 cmdlets.


          Temp fix:

          Existing Powershell session isn’t aware about Sql’s modules that were just installed. Take a look at environment variable $env:PSModulePath. Compare new shell’s variable to existing and you should see a missing path like …\Microsoft SQL Server\110\Tools\PowerShell\Modules\.
          For a work-around, modify the path to include module dir. Like so,

          $env:PSModulePath += ";C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules"


          If you want to persist this extra “Module load path”, you have to change the registry:




          Now I can run and debug the following PowerShell script inside Visual Studio 2013 / 2015:

          # This sccript requires the system to allow running powershell scripts, when not allowed the following code can be used to allow running powershell scripts.
          # Set-ExecutionPolicy RemoteSigned
          # The following import statement enables the SQL Server PowerShell cmdlets, like "Invoke-Sqlcmd".
          Import-Module SqlPs
          $serverInstance = "(localdb)\v11.0"
          $database = "App"
          function ExecuteSqlFile {
              [string]$file = $args[0]
              Invoke-Sqlcmd -ServerInstance $serverInstance -Database $database -InputFile $file

          drop and create SQL Server tables based on an array with table names by using PowerShell


          # Add table names to the "$tables" array in the order they should be created.
          $tables = @(
          # Create a table if it does not exist.
          # It expects the "create scripts" to be located in a subfolder "Tables".
          function CreateTable
              [string]$table = $args[0]
              [string]$file = "{0}\Tables\{1}.sql" -f $PSScriptRoot, $table
              Echo("create table {0}" -f $table) 
              Invoke-Sqlcmd -ServerInstance "(localdb)\v11.0" -Database "MyDatabase" -InputFile $file
          # Drop a table if it exists.
          function DropTable
              [string]$table = $args[0]
              [string]$query = "if object_id('{0}') is not null begin drop table {0} end" -f $table
              Echo("drop table {0}" -f $table) 
              Invoke-Sqlcmd -ServerInstance "(localdb)\v11.0" -Database "MyDatabase" -Query $query
          function DropTables
              $reverseTable = $tables.Clone()
              foreach ($table in $reverseTable) {
                 DropTable $table
          function CreateTables {
              foreach ($table in $tables) {
                 CreateTable $table

          Will drop all tables in $tables if they exist and then will create all tables in $tables if they do not exist.

          Use formatting–f, when writing to the output console with PowerShell

          If you want to format a string when echoing to the screen, use parentheses.

          Echo("This is {0} " -f "a test.")


          Will output:

          This is a test.



          echo is an alias for Write-Output, which writes to the Success output stream. This allows output to be processed through pipelines or redirected into files. Write-Host writes directly to the console, so the output can’t be redirected/processed any further.

          ASP .NET MVC4 / Web Api – Create users and roles for SimpleMembershipProvider with PowerShell and C#

          If you are using the SimpleMembershipProvider for FormAuthentication in a ASP .NET MVC4 / Web Api project, the following PowerShell / C# code can be used to create and delete users and roles.

          • Create an empty XML App.config file "C:\Temp\App.config".
          • Paste the XML below in the file and save it.
          • Create an empty PowerShell file "C:\Temp\Manage_MVC_users_and_roles.ps1".
          • Paste the PowerShell code below in the file and save it.
          • Create an empty C# file "C:\Temp\Manage_MVC_users_and_roles.cs".
          • Paste the C# code below in the file and save it.
          • Execute the file "C:\Temp\Manage_MVC_users_and_roles.ps1" with PowerShell.


          This will create an user "test2" with password "test2" and a role "Administrators".



          • Database tables will automatically be created if they don’t exist.
          • De folder "C:\Temp" should contain the assemblies "System.Web.WebPages.dll" and "WebMatrix.Data.dll" and "WebMatrix.WebData.dll", this assembly can be downloaded using NuGet.


          <?xml version="1.0" encoding="utf-8"?>
              <profile defaultProvider="SimpleProfileProvider">
                  <add name="SimpleProfileProvider" type="WebMatrix.WebData.SimpleMembershipProvider, WebMatrix.WebData"
                      connectionStringName="DefaultConnection" applicationName="/" />
              <membership defaultProvider="SimpleMembershipProvider">
                  <add name="SimpleMembershipProvider" type="WebMatrix.WebData.SimpleMembershipProvider, WebMatrix.WebData" />
              <roleManager enabled="true" defaultProvider="SimpleRoleProvider">
                  <add name="SimpleRoleProvider" type="WebMatrix.WebData.SimpleRoleProvider, WebMatrix.WebData"/>
              <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

          PowerShell code (Manage_MVC_users_and_roles.ps1)

          # Get folder containing this script.
          $scriptFolder = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent
          # Load App.config file from scriptfolder.
          $appConfigPath = "$scriptFolder\App.config"
          [System.AppDomain]::CurrentDomain.SetData("APP_CONFIG_FILE", $appConfigPath)
          # Compile C# code to dll.
          $Assem = ( 
              'System.Web.ApplicationServices, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35',
              'System.Configuration, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
          $codePath = "$scriptFolder\Manage_MVC_users_and_roles.cs"
          $codeAssemblyName = "Manage_MVC_users_and_roles.dll"
          Add-Type -OutputType Library –ReferencedAssemblies $Assem -OutputAssembly $codeAssemblyName -Path $codePath
          # Load dll.
          $codeAssemblyPath = "$scriptFolder\$codeAssemblyName"
          Add-Type -Path $codeAssemblyPath
          # Execute C# code.
          $wrapper = New-Object Research.Rli.WebSecurityExecuter

          C# code

          Run PowerShell or batch file remotely


          To configure Windows PowerShell to receive remote commands in DEV

          1. Start Windows PowerShell with the "Run as administrator" option.
          2. On server: enable-psremoting
          3. On server: Set-ExecutionPolicy Unrestricted
          4. On server: Set-Item wsman:\localhost\client\trustedhosts * (allows any computer to connect)
          5. On server: Restart-Service WinRM
          6. On client: Set-ExecutionPolicy Unrestricted
          7. On client: Set-Item wsman:\localhost\client\trustedhosts * (allows connections to any server)
          8. On client: Restart-Service WinRM

          To run single command

          On client: Invoke-Command -ComputerName -ScriptBlock { C:\ |dir }


          To run multiple commands

          On client: Enter-PSSession -ComputerName


          On client: Exit-PSSession


          To run a PowerShell script remotely from a client batch file

          On client: create a "C:\Local.bat" file

          On client: create a "C:\Remote.ps1" file

          On client in the Remote.ps1 file: enter your powershell commands that will be executed remotely, like dir C:\

          On client in the Local.bat file: @powershell -command "Invoke-Command -ComputerName -FilePath ""C:\Remote.ps1"""


          Using C# in PowerShell to move files


          Screedump form Windows PowerShell ISE





          $source = @"

          using System;
          using System.Collections.Generic;
          using System.Text;
          using System.IO;

          namespace Rvl.Demo.Common
              public class MoveFiles
                  public static void Move()
                      string sourceFolder = @"C:\BDATA\Test\Source"; // Source folder
                      string destinationFolder = @"C:\BDATA\Test\Destination"; // Destination folder
                      DateTime checkDateTime = new DateTime(2010, 12, 29, 13, 0, 0); // 27-dec-2010 13:00:00
                      // Loop all files in source folder
                      foreach (string file in Directory.GetFiles(sourceFolder))
                          // Calculate difference between checkDateTime and file last modified datetime in days
                          DateTime fileLastModifiedDate = File.GetLastWriteTime(file);

                          // Move files if difference in days == 0
                          if (checkDateTime.Year == fileLastModifiedDate.Year && checkDateTime.Month == fileLastModifiedDate.Month && checkDateTime.Day == fileLastModifiedDate.Day)
                              string destinationFilePath = Path.Combine(destinationFolder, Path.GetFileName(file));
                              Console.WriteLine(string.Format("Moving file [{0}] to [{1}]", file, destinationFilePath));

                              File.Move(file, destinationFilePath);

          Add-Type -TypeDefinition $source