Powershell Remote – How to un Invoke-Sqlcmd for SQL Query to initiate Jenkins De

Welcome Forums General PowerShell Q&A Powershell Remote – How to un Invoke-Sqlcmd for SQL Query to initiate Jenkins De

Viewing 2 reply threads
  • Author
    Posts
    • #206439
      Participant
      Topics: 2
      Replies: 2
      Points: 28
      Rank: Member

      Hello everybody,

      I am trying to set $parameters for my auto-deployment job of Jenkins to execute the powershell script via jenkins from a application server on a external database server.

      I already installed the powershell plugin and have created a script that is working so far (without all necassary parameters)

      When entering the following script in powershell on the external application server the job is working.

      #############################################################################

      $Username = ‘AMUSERNAME’
      $Password = ‘ADMPASSWORD’
      $pass = ConvertTo-SecureString -AsPlainText $Password -Force
      $Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass

      Invoke-Sqlcmd -Query “DECLARE @ProjectBinary AS varbinary(max)
      DECLARE @operation_id AS bigint SET @ProjectBinary = (SELECT * FROM OPENROWSET(BULK ‘\\deployfrom-database\C$\Jenkins_workspace\SSIS\#### DB-New1234\Bin\Development\#### DB-New1234.ispac’, SINGLE_BLOB) AS BinaryData)
      USE SSISDB
      EXEC catalog.deploy_project @folder_name = ‘foldername_of_project’, @project_name = ‘#### DB-New1234’, @Project_Stream = @ProjectBinary, @operation_id = @operation_id out
      ;” -ServerInstance “deployto-database”

      ################################################################################

      Now I want to set the “right parameters”, so that I can copy the script into Jenkins for each project.

      The following parameter is working so far.

      $DestinationServer = ‘deployto-database’

      The new working script looks like:

      ############################################################################
      $Username = ‘AMUSERNAME’
      $Password = ‘ADMPASSWORD’
      $pass = ConvertTo-SecureString -AsPlainText $Password -Force
      $Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass
      $DestinationServer = ‘deployto-database’

      Invoke-Sqlcmd -Query “DECLARE @ProjectBinary AS varbinary(max)
      DECLARE @operation_id AS bigint SET @ProjectBinary = (SELECT * FROM OPENROWSET(BULK ‘\\deployfrom-database\C$\Jenkins_workspace\SSIS\#### DB-New1234\Bin\Development\#### DB-New1234.ispac’, SINGLE_BLOB) AS BinaryData)
      USE SSISDB
      EXEC catalog.deploy_project @folder_name = ‘foldername_of_project’, @project_name = ‘#### DB-New1234’, @Project_Stream = @ProjectBinary, @operation_id = @operation_id out
      ;” -ServerInstance $DestinationServer
      #################################################################

      Furthermore in the configuration of the jenkins Jobs I have to set parameters for:

      $DestinationPath = ‘/SSISDB/foldername_of_project’

      With this parameter I don´t know how to implement in my script.

      I have tried several things but I don´t get it to work.

      Anyone has an idea how to set the right parameters in the script &/ Jenkins?

      Grateful for every kind of help!

      Looking forward to hearing from you,

      Best regards
      Manfred

    • #206523
      Participant
      Topics: 12
      Replies: 1622
      Points: 2,560
      Helping Hand
      Rank: Community Hero

      Please use the PRE tags around your code. It appears you are passing the SQL code with no variables. There are many ways to glue things together, but first you should start with is a here string that will make it easier to read the command. The variables can be injected into a DECLARE or directly:

      $Username = ‘AMUSERNAME’
      $Password = ‘ADMPASSWORD’
      $pass = ConvertTo-SecureString -AsPlainText $Password -Force
      $Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass
      $DestinationServer = ‘MyDatabasee’
      $ProjectBinary = 'MyBinaryVal'
      $OperationId= '1234567'
      $Folder = 'MyFolderName'
      $Project = 'MyProjectName'
      
      # Here String
      $sqlQry = @“
      DECLARE @ProjectBinary AS varbinary(max) = '$($ProjectBinary)'
      DECLARE @operation_id AS bigint = '$($OperationId)'
      DECLARE @folder AS nvarchar(50) = '$($Folder)'
      DECLARE @project AS nvarchar(50) = '$($Project)'
      
      
      SET @ProjectBinary = (SELECT * FROM OPENROWSET(BULK ‘\\$($DestinationServer)\C$\Jenkins_workspace\SSIS\' + @project + '\Bin\Development\' + @project +'.ispac’, SINGLE_BLOB) AS BinaryData)
      
      USE SSISDB
      EXEC catalog.deploy_project @folder_name = @folder, @project_name = @project , @Project_Stream = @ProjectBinary, @operation_id = @operation_id out;
      ”@
      
      $sqlQry
      #Invoke-Sqlcmd -Query $sqlQry -ServerInstance $DestinationServer
      

      If you look at this, the first bolded item is direct injection and the second is using the DECLARE and string concantenation in SQL:

      SELECT * FROM OPENROWSET(BULK ‘\\$($DestinationServer)\C$\Jenkins_workspace\SSIS\’ + @project + + ‘\Bin\Development\’ + @project +’.ispac’

      Regardless, this will just dump out the SQL command. Next you need to grab that and paste it in a SQL editor like SQL Management Studio to ensure that the SQL doesn’t have any syntax issues. You should be able to just execute the code. If you cannot, then you need to fix the issues in the here string.

    • #206742
      Participant
      Topics: 2
      Replies: 2
      Points: 28
      Rank: Member

      Hello Rob,

      thank you for your help. It seems to almost work out.

      But still I get the following error:

      “Invoke-Sqlcmd : Falsche Syntax near to  ”’.
      At line:1 char:1
      + Invoke-Sqlcmd -Query ”
      + ~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
      + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

       

      The affected line is:

      SET @ProjectBinary = (SELECT * FROM OPENROWSET(BULK ‘\\$($DestinationServer)\C$\Jenkins_workspace\SSIS\’ + @project + ‘\Bin\Development\’ + @project +’.ispac’, SINGLE_BLOB) AS BinaryData)

      And I recognized that there is one mistake:

      SET @ProjectBinary = (SELECT * FROM OPENROWSET(BULK ‘\\$($DeployFromServer)\C$\Jenkins_workspace\SSIS\’ + @project + ‘\Bin\Development\’ + @project +’.ispac’, SINGLE_BLOB) AS BinaryData)

       

      But also when setting the $DeployFromServer before and announce the parameter at the beginnen, I get the same error message

      Do you have any idea, how I can fix this issue?

       

      kind regards

      Manfred

Viewing 2 reply threads
  • The topic ‘Powershell Remote – How to un Invoke-Sqlcmd for SQL Query to initiate Jenkins De’ is closed to new replies.