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

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

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.

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: (:slight_smile: [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