trying to reach ssis catalog on a different server using powershell

_{Tuesday, October 1, 2019 6:30 PM}

Hi this is my first attempt at using ps.   I'm trying to execute a pkg in the ssis catalog on sql server B by executing the ps script from development server A.  I cant rdp to sql server B , otherwise i'd probably be trying this there. 

I followed the example at /en-us/sql/integration-services/ssis-quickstart-run-powershell?view=sql-server-2017 in making my .ps1 ps script file.  I believe ssis is found on A because we use VS to develop pkgs there.  The path to ssis unfortunately is C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\CommonExtensions\Microsoft\SSIS\140\Binn\Microsoft.SQLServer.Management.IntegrationServices.   The error i'm getting is that  the specified path , file name or both are too long.  That the fully qualified must be less than 260 and the directory less than 248.

interestingly , I used loadfile under ps and reflection with the same directory etc to get the version and public token of the dll.

the .net command that is failing is $loadStatus = [System.Reflection.Assembly]::LoadFile("C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\CommonExtensions\Microsoft\SSIS\140\Binn\Microsoft.SQLServer.Management.IntegrationServices, "+
    "Version=14.100.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn, processorArchitecture=MSIL").  when I don't qualify the file with a path, unfortunately ps looks in the directory where the ps file is located for the .dll. 

I tried load instead of loadfile to no avail.  I'm using windows powershell.    I looked at dos paths on the dev server but see nothing that would take me to this .dll in this location.  I could probably get admins to add this path to the dos path but who knows what would break.  Does the community have any ideas?  I put a copy of the ps1 file on sql server B and tried running ps on A pointing to the file whose path started as server B with two back slashes but ps didn't like that.   I used net view and don't believe I have any shares I can work with that point at server b.  

Tomorrow i'll try the remote approach described here https://www.howtogeek.com/117192/how-to-run-powershell-commands-on-remote-computers/  .  Test-WsMan already shows me that Server B is running WinRM.  I cant see the c: drive on Server B but maybe unqualified the dll would be found since that server is a sql server.

_{Wednesday, October 2, 2019 11:25 AM}

I cut server A out of the picture temporarily after realizing I can run ps locally.  so I started a "ps session" locally trying to reach server B but to no avail as in this case too (just like attempting an rdp) remote access was denied. 

I'm going to focus on getting the script run right on server A trying to reach the catalog on server B.  Ideas popping into my head are 1) finding a way to reference (in the load(file)) the dll with less characters in the path, 2) looking into some of the more esoteric ps commands (I think they are called cmdlets like "start dba agent job" if i'm willing to stick an agent job in the middle) that I think eliminate the need to do reflection but are instead meant specifically for simpler interfaces to things like sql.       

_{Wednesday, October 2, 2019 1:03 PM}

I tried different variations of this. some with a slash after binn.  some without.  some with a slash after F:, some without.  some with load, some with loadfile and some with loadfrom.  Some with the dll extension, some without. All of the errors either say file not found, absolute path required or given assembly name or codebase was invalid .  The last error occurs with no slash after binn and Load("F:Microsoft...without dll extension...).  Running just the ls command in ISE gives me results that make me believe the dll file can be seen in drive F in this ps session..

$drive = "F"#getFreeDrive

subst "$drive`:" "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\CommonExtensions\Microsoft\SSIS\140\Binn"
#Get-PSDrive | Out-Null
#ls "$drive`:\"  # just a dummy command 
#subst "$drive`:" /D


# Load the IntegrationServices assembly
$loadStatus = [System.Reflection.Assembly]::LoadFrom("F:/Microsoft.SQLServer.Management.IntegrationServices.dll, "+
    "Version=14.100.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn, processorArchitecture=MSIL")

_{Wednesday, October 2, 2019 1:17 PM}

...and for whatever reason, this at least ran without an error...no version, culture, public key token, processor architecture.  I'll see what happens next.

$loadStatus = [System.Reflection.Assembly]::LoadFrom("F:/Microsoft.SQLServer.Management.IntegrationServices.dll")#, "+

_{Wednesday, October 2, 2019 2:43 PM}

the next 3 statements are shown below. object ssisnamespace is defined as

$SSISNamespace="Microsoft.SqlServer.Management.IntegrationServices" .

 The 3rd statement fails with an error new-object cannot find type Microsoft.sqlserver.management.integrationservices.integrationservices   verify that the assembly containing this type is loaded. 

# Create a connection to the server
$sqlConnectionString = `
    "Data Source=" + $TargetServerName + ";Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $SSISNamespace".IntegrationServices" $sqlConnection

_{Wednesday, October 2, 2019 3:30 PM}

from PS ISE (integrated scripting environment) I was able to get this (going thru sql agent rather than directly to pkg) working from Server A.   I didn't even have to install the sql server ps module which I think contains sql cmdlets.  If I did need to install the sql server ps  module, I believe I would have been asked to install NuGet first.  I'm not sure what NuGet is but it sounds like some sort of small footprint repository which contains the sql cmdlets. 

I'm assuming the sql server ps module was already installed but cant be 100% sure.  Evidence against that is that when I run this command

get-command-modulesqlserver

I get nothing even though invoke-sqlcmd is supposed to be a sql module cmdlet. 

It would have been nice to get the more .net ish code (reflection etc) to work but look how simple this is.  From what I can tell, there are other vendors out there like "dbatools" with even more succinct commands that could even execute the pkg directly from the catalog.  but i'm assuming that isn't mainstream so I shied away from that.    

I did get this PS SQLSERVER:\SSIS\xxxxxxxx\DEFAULT\Catalogs\SSISDB\Folders\yyyyyyyyyyyyyyy\Projects\zzzzzzzzzzzzzzz\Packages\wwtopowershelltest%2Edtsx> (Get-Item .).execute($false, $null)to work under sql in ssms (sql pops up a ps environment) but long term i'm looking for a way to get this all to work thru the file system where a ps1 file would do all this and i'm not sure that is possible with this format.  I learned how to do this at https://www.sqlservercentral.com/blogs/execute-ssis-package-from-powershell . I got an error trying to run this format in a non sql ps ise window.  ww represents the unix based job scheduling system we are going to try to plumb indirectly to ssis without using ww's sql add in feature.

$SQLServer = "xxxxxxxx"
$Database = "MSDB"
$ExecAgent = "exec dbo.sp_start_job N'test ww to ssis'"
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -Query $ExecAgent

_{Wednesday, October 2, 2019 4:05 PM}

I finally tried the invoke sqlcmd format from the last post in a ps1 file instead of from ise. I'm glad I tried it because I got an error executing it from the power shell command line environment as shown below. The error I got repeated a number of times but it looked mostly like this...

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on
'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with the following error: The RPC server is unavailable.
(Exception from HRESULT: 0x800706BA)

In spite of the word WARNING, it actually didn't work.  When I put the ps1 file extension on the file name, then it worked.

PS SQLSERVER:\> & "C:\Users\myuser\source\repos\powershell\wwtopowershelltestsqlbased"

_{Wednesday, October 2, 2019 4:26 PM}

a .net ish approach that did work immediately without path length or any path consideration issues is shown below. I learned this from https://powershell.org/forums/topic/start-a-sql-agent-job-step-via-powershell/  .  she does show a way to bake a start step in there too.

$ServerName="xxxxxxxx"
$JobName="test ww to ssis"
#$StepName =""
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server("$ServerName")
$job = $srv.jobserver.jobs["$JobName"] 
$job.Start()