Could not find object: linkedService.SupportDatabase on DeployProcFwkComponents.ps1

[MiKeZZa]

I'm trying to setup the procfwk. Untill this moment everything works fine, but now I receive this message while executing DeployProcFwkComponents.ps1.

`Update-PropertiesFromFile : Could not find object: linkedService.SupportDatabase At C:\Program Files\WindowsPowerShell\Modules\azure.datafactory.tools\0.17.0\public\Publish-AdfV2FromJson.ps1:160 char:9 Update-PropertiesFromFile -adf $adf -stage $Stage -option $op ...

     CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
     FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Update-PropertiesFromFile

The property 'Body' cannot be found on this object. Verify that the property exists.
At C:\Program Files\WindowsPowerShell\Modules\azure.datafactory.tools\0.17.0\private\Update-PropertiesFromFile.ps1:68 char:9
         $json = $o.Body

 CategoryInfo          : NotSpecified: (:) [], PropertyNotFoundException
 FullyQualifiedErrorId : PropertyNotFoundStrict

The variable '$json' cannot be retrieved because it has not been set. At C:\Program Files\WindowsPowerShell\Modules\azure.datafactory.tools\0.17.0\private\Update-PropertiesFromFile.ps1:69 char:23 if ($null -eq $json) {

     CategoryInfo          : InvalidOperation: (json:String) [], RuntimeException
     FullyQualifiedErrorId : VariableIsUndefined

Update-PropertiesFromFile : Wrong path defined in config for object(path): linkedService.SupportDatabase(properties.typeProperties.connectionString.secretName)
At C:\Program Files\WindowsPowerShell\Modules\azure.datafactory.tools\0.17.0\public\Publish-AdfV2FromJson.ps1:160 char:9
         Update-PropertiesFromFile -adf $adf -stage $Stage -option $op ...

 CategoryInfo          : NotSpecified: (:) [Write-Error], DataException
FullyQualifiedErrorId : System.Data.DataException,Update-PropertiesFromFile

The variable '$json' cannot be retrieved because it has not been set. At C:\Program Files\WindowsPowerShell\Modules\azure.datafactory.tools\0.17.0\private\Update-PropertiesFromFile.ps1:88 char:44 Update-ObjectProperty -obj $json -path "properties.$p ...

     CategoryInfo          : InvalidOperation: (json:String) [], RuntimeException
     FullyQualifiedErrorId : VariableIsUndefined

Save-AdfObjectAsFile : Cannot bind argument to parameter 'obj' because it is null.
At C:\Program Files\WindowsPowerShell\Modules\azure.datafactory.tools\0.17.0\private\Update-PropertiesFromFile.ps1:104 char:41
         $f = (Save-AdfObjectAsFile -obj $o)
                                         ~~
     CategoryInfo          : InvalidData: (:) [Save-AdfObjectAsFile], ParameterBindingValidationException
     FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Save-AdfObjectAsFile

The variable '$f' cannot be retrieved because it has not been set.
At C:\Program Files\WindowsPowerShell\Modules\azure.datafactory.tools\0.17.0\private\Update-PropertiesFromFile.ps1:105 char:23
         $o.FileName = $f
                       ~~
     CategoryInfo          : InvalidOperation: (f:String) [], RuntimeException
     FullyQualifiedErrorId : VariableIsUndefined
 `

I tried many things. What I've changed now in the PS1 script is remove the first 14 lines and added this:

$tenantId = "mytenantid"
$subscriptionId = "myscubscriptionid"
$spId = "azureclientid"
$spKey = "azureclientsecret"
$resourceGroupName = "myresourcename"
$dataFactoryName = "myadfname"
$region = "West Europe" (but also tried "WestEurope" or "West-Europe")

And later I found:
 $Env:SQLDatabase = "<secretKeyToDbConnectionString>"
There I added this;
 Server=tcp:adfframework.database.windows.net,1433;Initial Catalog=frameworkmetadatadb;Persist Security Info=False;User ID=MYUSERNAME;Password=MYPASSWORD;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

I'm pretty sure that I am making a mistake. But I don't know which one and how to fix my problem. Can I get support here?

[mrpaulandrew]

Hey @MiKeZZa

Firstly, its probably best to check you have the linked service available in your repo to allow ADF to connect to your metadata database. The json file for this is called 'SupportDatabase' and should be in the repo directory ...\DataFactory\LinkedServices...

Or here in my repo for reference: https://github.com/mrpaulandrew/procfwk/blob/master/DataFactory/linkedService/SupportDatabase.json

Assuming this exists. What the PowerShell deploy tool is trying to do is replace Azure Key Vault secret name with the name of your environment variable name.

Also, to be clear, the secret in Key Vault should contain the connection string to your database. Not added to the linked service directly. Unless you want it to, then you'll need to modify the Linked Service content directly.

Hope this helps.

Let me know if this still isn't working.

Thanks

[MiKeZZa]

Hi Paul, thank you for assisting me. It's not really clear for me right now (I'm sure this is my bad); I now have returned to:

$Env:SQLDatabase = ""

And the SupportDatabase.json is in the solution, just like he was when I downloaded your solution, except 1 line:

"secretName": "SQLDBConnectionString"

In my KeyVault I have a secret called SQLDBConnectionString, with this content (I've manipulated it a little for security reasons)

Server=tcp:frameworkadfprocfwk.database.windows.net,1433;Initial Catalog=frameworkmetadatadatabasename;Persist Security Info=False;User ID=admin_username;Password=a!Oc6F0oGeQPd;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

But the error is still the same. I must be doing something wrong, but can't find it right now. You have an idea left?

[htwashere]

Hi @MiKeZZa , I imported this repo several months ago, in my case, it looked like I modified it slightly to make it work, I think it may have to do with the way I structured my local repo. Anyhow, here is my code for the DeployProcFwkComponents.ps1. I'm not sure if this will help with your case, good luck.

Param( [Parameter(Mandatory)] [string]$resourceGroupName, [Parameter(Mandatory)] [string]$dataFactoryName, [Parameter(Mandatory)] [string]$region )

$VerbosePreference = "SilentlyContinue"

SPN for deploying ADF:

$tenantId = "XXX" $subscriptionId = "XXX" $spId = "XXX" $spKey = "XXX"

Modules

Import-Module -Name "Az"

Update-Module -Name "Az"

Import-Module -Name "Az.DataFactory"

Update-Module -Name "Az.DataFactory"

Import-Module -Name "azure.datafactory.tools"

Update-Module -Name "azure.datafactory.tools"

Get-Module -Name "DataFactory"

$VerbosePreference = "Continue"

Login as a Service Principal

if ($spId) { $passwd = ConvertTo-SecureString $spKey -AsPlainText -Force $pscredential = New-Object System.Management.Automation.PSCredential($spId, $passwd) Connect-AzAccount -ServicePrincipal -Credential $pscredential -TenantId $tenantId | Out-Null } Get-AzContext

$VerbosePreference = 'Continue' $ErrorActionPreference = 'Stop'

Get Deployment Objects and Params files

$scriptPath = Join-Path -Path (Get-Location) -ChildPath "\DeploymentTools\DataFactory"

$scriptPath = Get-Location $deploymentFilePath = Join-Path -Path $scriptPath -ChildPath "ProcFwkComponents.json" $configFilePath = Join-Path -Path $scriptPath -ChildPath "config-all.csv" $Env:SQLDatabase = "Server=tcp:XXX.database.windows.net,1433;Initial Catalog=XXX-db;Persist Security Info=False;User ID=admin;Password=XXX;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

$opt = New-AdfPublishOption $deploymentObject = (Get-Content $deploymentFilePath) | ConvertFrom-Json $objectsToInclude = $deploymentObject.datasets + $deploymentObject.linkedServices + $deploymentObject.pipelines + $deploymentObject.triggers $objectsToInclude | ForEach-Object { $objName = $_.substring(1).Replace('.json', '').Replace('/', '.') $opt.Includes.Add($objName, "") }

Deployment of ADF

$AdfPath = Join-Path -Path (Get-Location) -ChildPath "DataFactory"

$AdfPath = Get-Location $opt.CreateNewInstance = $true $opt.DeleteNotInSource = $false $opt.StopStartTriggers = $true

Publish-AdfV2FromJson -RootFolder $AdfPath -ResourceGroupName $resourceGroupName -DataFactoryName $dataFactoryName -Location $region -Option $opt

-Stage $configFilePath

[MiKeZZa]

Thank you @htwashere. Great that you are contributing to a solution too....

When I pick some stuff from your code and fill it with my credentials and some adjustments I had to do (like setting the $deploymentFilePath to a hard value) the script runs without errors. The output is:

======================================================================================

azure.datafactory.tools Version 0.17.000

====================================================================================== Invoking Publish-AdfV2FromJson (https://github.com/SQLPlayer/azure.datafactory.tools) with the following parameters:

RootFolder: C:\WINDOWS\system32 ResourceGroupName: MyResourceGroupName DataFactoryName: MyDFName Location: West Europe Stage:
Options provided: True Publishing method: AzResource

Publish options are provided. STEP: Verifying whether ADF exists... Azure Data Factory exists.

STEP: Reading Azure Data Factory from JSON files... IntegrationRuntimes: 0 object(s) loaded. LinkedServices: 0 object(s) loaded. Pipelines: 0 object(s) loaded. DataSets: 0 object(s) loaded. DataFlows: 0 object(s) loaded. Triggers: 0 object(s) loaded. Factories: 0 object(s) loaded.

Number of objects marked as to be deployed: 0/0

=================================================================================== STEP: Replacing all properties environment-related... Stage parameter was not provided - action skipped.

STEP: Stopping triggers... Getting triggers...

STEP: Deployment of all ADF objects...

STEP: Deleting objects not in source ... Operation skipped as publish option 'DeleteNotInSource' = false

STEP: Starting all triggers... The number of triggers to start: 0

Azure Data Factory files have been deployed successfully.

Data Factory name: DFName Region (Location): West Europe Elapsed time: 0:00:02.085

==============================================================================

Name : DF-name ResourceGroupName : MyResourceGroupName Region : West Europe Pipelines : {} LinkedServices : {} DataSets : {} DataFlows : {} Triggers : {} IntegrationRuntimes : {} Factories : {} Location : C:\WINDOWS\system32 GlobalFactory : AdfGlobalProp

So now I have to figure out if this is good and that it's logical that everything is empty now in my ADF. I was expecting some pipelines, datasets and/or linked services....

[MiKeZZa]

So the strange thing now is that I have this output:

======================================================================================

azure.datafactory.tools Version 0.17.000

====================================================================================== Invoking Publish-AdfV2FromJson (https://github.com/SQLPlayer/azure.datafactory.tools) with the following parameters:

RootFolder: C:\WINDOWS\system32 ResourceGroupName: procfwkADF DataFactoryName: MyFrameworkADF Location: West Europe Stage:
Options provided: True Publishing method: AzResource

Publish options are provided. STEP: Verifying whether ADF exists... Azure Data Factory exists.

STEP: Reading Azure Data Factory from JSON files... IntegrationRuntimes: 0 object(s) loaded. LinkedServices: 0 object(s) loaded. Pipelines: 0 object(s) loaded. DataSets: 0 object(s) loaded. DataFlows: 0 object(s) loaded. Triggers: 0 object(s) loaded. Factories: 0 object(s) loaded.

Number of objects marked as to be deployed: 0/0

=================================================================================== STEP: Replacing all properties environment-related... Stage parameter was not provided - action skipped.

STEP: Stopping triggers... Getting triggers...

STEP: Deployment of all ADF objects...

STEP: Deleting objects not in source ... Operation skipped as publish option 'DeleteNotInSource' = false

STEP: Starting all triggers... The number of triggers to start: 0

Azure Data Factory files have been deployed successfully.

Data Factory name: ADFFramework007 Region (Location): West Europe Elapsed time: 0:00:02.466

==============================================================================

Name : MyFrameworkADF ResourceGroupName : procfwkADF Region : West Europe Pipelines : {} LinkedServices : {} DataSets : {} DataFlows : {} Triggers : {} IntegrationRuntimes : {} Factories : {} Location : C:\WINDOWS\system32 GlobalFactory : AdfGlobalProp

So as you can see the script now executes without errors, but is not creating anything (that also what I see in ADF, still vanilla as Paul says in his movies...). I was expecting the items that are in my ProcFwkComponents.json (that's unchanged to the original version here on GitHub):

{ "linkedServices": [ "/linkedService/Keys.json", "/linkedService/FrameworkFunctions.json", "/linkedService/SupportDatabase.json" ], "datasets": [ "/dataset/GetSetMetadata.json" ], "pipelines": [ "/pipeline/04-Infant.json", "/pipeline/03-Child.json", "/pipeline/02-Parent.json", "/pipeline/01-Grandparent.json" ], "triggers": [ "/trigger/FunctionalTestingTrigger.json" ] }

So somebody has an idea what to do? @htwashere @mrpaulandrew maybe?

[htwashere]

@MiKeZZa this is just a wild guess of mine, I noticed your root folder displayed as: "RootFolder: C:\WINDOWS\system32". i have a feeling you're not within the correct directory location when you're executing your script? If you look at the documentation for Publish-AdfV2FromJson (link), it actually gives an example of what the RootFolder should be set as....Good Luck.

[mrpaulandrew]

Building on comments from @htwashere yes, be mindful that if using tools like VSCode the PowerShell runtime responsible for the execution isn't always aware of its own path given that VSCode can be used on different operating system.

[MiKeZZa]

Thanks @htwashere and @mrpaulandrew. I feel really stupid, but how can I find the correct root folder?

I've tried:

$AdfPath = Join-Path -Path (Get-Location) -ChildPath "DataFactory" $AdfPath = "C:\Users\MyName\Project\procfwk-master\procfwk-master\DeploymentTools\DataFactory" $AdfPath = "C:\Program Files\WindowsPowerShell\Modules\azure.datafactory.tools\0.17.0\public" $AdfPath = Get-Location

Nothing works and it's really not clear to me what I must do to find the correct path. Which path must be used here? The PowerShell runtime? Or something in this project?

My PowerShell hyperlink points to: %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe

So are you really sure that C:\Windows\System32 is wrong?

[htwashere]

@MiKeZZa : Can you try this:

1. Open a DOS command prompt

2. cd to your directory: "C:\Users\MyName\Project\procfwk-master\procfwk-master\DeploymentTools\DataFactory"

3. Start Powershell

4. In your Powershell, type: get-location. Make sure you're still in the working directory as above

5. Type: .\deployprocfwkcomponents.ps1

6. It should ask you for all the necessary Azure parameters from here on...

PS: You may also want to check the version of powershell you are running on, based on where it is installed, it could be a very old one and may not support all the modules necessary for this. To check version, type get-host in your powershell window.

Cheers,

[MiKeZZa]

Hi @htwashere.

My Powershell version:

Major Minor Build Revision

---

5 1 19041 546

When I follow your steps get-location brings my command prompt to c:\windows\system32. When I type .\deployprocfwkcomponents.ps1 in CMD prompt (in Powershell it gives an error because of that Powershell has switched to c:\windows\system32) it opens this script in notepad instead of powershell. This while the default app for opening .ps1 files is set to Powershell.

What am I doing wrong (this time)?

[htwashere]

@MiKeZZa : try this:
1) open powershell first in a command DOS prompt. 2) inside powershell, type: cd C:\Users\MyName\Project\procfwk-master\procfwk-master\DeploymentTools\DataFactory
3) I will assume by this point, you are pointing to the correct folder where your repo is located and does contain the script deployprocfwkcomponents.ps1
4) type: .\deployprocfwkcomponents.ps1

[MiKeZZa]

Ok I now started DOS Prompt as admin, then I opened powershell_ise.exe in C:\Windows\System32\WindowsPowerShell\v1.0 and then in Powershell_ISE I cd't to the DF folder and I did run .\deployprocfwkcomponents.ps1. The output then is:

C:\Users\MyName\Project\procfwk-master\procfwk-master\DeploymentTools\DataFactory\DeployProcFwkComponents.ps1 : The term 'System.DefaultWorkingDirectory' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At line:1 char:1

• .\deployprocfwkcomponents.ps1

• 
  + CategoryInfo          : ObjectNotFound: (System.DefaultWorkingDirectory:String) [DeployProcFwkComponents.ps1], CommandNotFoundException
  + FullyQualifiedErrorId : CommandNotFoundException,DeployProcFwkComponents.ps1

I don't know what this is meaning, but the file really is in this location....

[htwashere]

@MiKeZZa I did the same steps you mentioned above and it is working fine for me.

[MiKeZZa]

Hmmm... That's not nice @htwashere. Thank you very much for the help.

I'll wait to see if @mrpaulandrew comes with something. If not I'm afraid I'll have to cancel my journey with this framework :(

[mrpaulandrew]

@MiKeZZa is it possible to clear things down and start again? Get the latest for all PowerShell modules and get latest for the repo.

[MiKeZZa]

@mrpaulandrew I've now redownloaded DeployProcFwkComponents.ps1 and updated my installed modules.

Result is the same :(

[mrpaulandrew]

@MiKeZZa as a work around, I suggest using the ARM template from the repo to deploy Data Factory instead if you can't get the PowerShell working.

[MiKeZZa]

Yes, I did now. So that solved the issue for now (or better; it didn't solve but it got me where I wanted to be). Thank you for your help!