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 (
    [Parameter(Mandatory=$True)]
    [string]
    $ResourceGroupName,

    [Parameter(Mandatory=$True)]
    [string]
    $DataFactoryName,

    [Parameter(Mandatory=$True)]
    [string]
    $Find,

    [Parameter(Mandatory=$True)]
    [string]
    $Replace
)

$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"

Select views that don’t reference other views in SQL Server

 

 

— Select views that don’t reference other views.

select

        s.[name]

,       v.[name]

from    sys.views v

join    sys.schemas s on v.schema_id = s.schema_id

where   v.object_id not
in (

    select distinct

                r.referenced_id

    from        sys.dm_sql_referenced_entities (s.[name] + ‘.’ + v.[name], ‘OBJECT’) r

    join        sys.objects o on r.referenced_id = o.object_id

    where       o.[type] = ‘V’

)

order by    s.[name], v.[name]

 

 

Select views that reference other views in SQL Server

 

— Select views that reference other views.

select distinct

            s.[name]

,           v.[name]

,           r.referenced_schema_name

,           r.referenced_entity_name

from        sys.views v

join        sys.schemas s on v.schema_id = s.schema_id

cross
apply sys.dm_sql_referenced_entities (s.[name] + ‘.’ + v.[name], ‘OBJECT’) r

join        sys.objects o on r.referenced_id = o.object_id

where       o.[type] = ‘V’

order by    s.[name], v.[name]