Forum Discussion

powerbi_jenhen's avatar
powerbi_jenhen
Resolver II
2 years ago
Solved

Power BI Rest API via Powershell

Hi,

 

I have the below $Body variable to use within my API Invoke command, how can I exit the single quotes to reference my $server and $database variables? I currently receive the error: "DMTS_UnsupportedConnectionStringError". I've tried to add single quotes to exit but this triggers an unexpected token:

 

$Body = @{
        "datasourceType"="SQL"
        "connectionDetails"='{"server":$server,"database":$database}'
        "datasourceName"=$gatewaySourceName
    }
Need Help
  • powerbi_jenhen's avatar
    powerbi_jenhen
    2 years ago

    The issue was my server variable required "\\" not "\". Code that worked for me:

    $Body = ConvertTo-Json -Depth 4 -InputObject $(@{
            dataSourceType = "Sql"
            connectionDetails = '{"server":"'+$server.Replace("\","\\")+'","database":"'+$database+'"}'
            datasourceName = $sourceName
            credentialDetails = @{
                credentialType = $credentialDetails.credentialType
                credentials = $credentialDetails.credentials
                encryptedConnection = $credentialDetails.encryptedConnection
                encryptionAlgorithm = $credentialDetails.encryptionAlgorithm
                privacyLevel = $credentialDetails.privacyLevel
            }
        })

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

     

     

     

    $Body = @"
{
        "datasourceType":"SQL",
        "connectionDetails":'{"server":$server,"database":$database}',
        "datasourceName":$gatewaySourceName
}
"@@

     

     

    note that the last line should be "@ .  Can't get rid of the second @

    • powerbi_jenhen's avatar
      powerbi_jenhen
      Resolver II
      2 years ago

      Thanks! I actually have further details in my $Body so would the below be the correct syntax? I'm getting a "BadRequest" on my Invoke:

       

      $Body = @"
          {
              "datasourceType":"SQL",
              "connectionDetails":'{"server":$server,"database":$database}',
              "datasourceName":$sourceName,
              "credentialDetails":{
                  "credentialType":$credentialDetails.credentialType,
                  "credentials":$credentialDetails.credentials,
                  "encryptedConnection":$credentialDetails.encryptedConnection,
                  "encryptionAlgorithm":$credentialDetails.encryptionAlgorithm,
                  "privacyLevel":$credentialDetails.privacyLevel
              }
          }
      "@
  • powerbi_jenhen's avatar
    powerbi_jenhen
    Resolver II
    2 years ago

    The issue was my server variable required "\\" not "\". Code that worked for me:

    $Body = ConvertTo-Json -Depth 4 -InputObject $(@{
            dataSourceType = "Sql"
            connectionDetails = '{"server":"'+$server.Replace("\","\\")+'","database":"'+$database+'"}'
            datasourceName = $sourceName
            credentialDetails = @{
                credentialType = $credentialDetails.credentialType
                credentials = $credentialDetails.credentials
                encryptedConnection = $credentialDetails.encryptedConnection
                encryptionAlgorithm = $credentialDetails.encryptionAlgorithm
                privacyLevel = $credentialDetails.privacyLevel
            }
        })