Running SSIS package from PowerShell without project deployment-CodePudding

This gives a description of running an SSIS package using PowerShell. However, it appears to require 'project deployment'.

Is it possible to run an SSIS package that is used in 'package deployment' without resorting to `DTExec.exe'?

Update:

I need to invoke the SSIS package from a client machine. I do not have permission to run on the server having SQL Server installed. Is there something I can install on the client machine that will enable running a package on an SQL Server machine?

CodePudding user response：

Yes, you can run an SSIS Package without using dtexec.exe. But no, you cannot execute SSIS packages on a machine that does not have the assemblies and a SQL Server License.

Assumptions

The machine you are going to do this on is licensed for SQL Server and has the correct SQL Server Integration Services bits installed.

You'll need to load the correct assembly (Microsoft.SqlServer.Dts.Runtime) to match the version of SSIS packages that were built. If you're targeting SQL Server 2016, then you'll need to make sure you load the corresponding assembly.

This answer covers how to do it in C# How to execute an SSIS package from .NET?

Cobbling bits and pieces from my blog post which loads up said assembly and deploys but we'll just Execute https://billfellows.blogspot.com/2011/08/powershell-ssis-deployment-and.html

Yields this untested, but smells correct, code

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") | out-null
$PathToDtsx = "C:\src\path\package.dtsx"
try
{
    # Create an instance of the application which is needed to load the package
    $app = New-Object Microsoft.SqlServer.Dts.Runtime.Application
    # Create an instance of our package as objects from source file
    $package = $app.LoadPackage($PathToDtsx, $null)
    # Run the package as-is, no run-time values, etc
    $pkgResults = $package.Execute($null, $null, $null, $null, $null);
}
catch 
{
    Write-Error $_ | fl * -Force
}

Running remote packages

Given the update to the question, you need to be able to get the package running on the machine that is licensed and has the SSIS bits installed. Delivering a package to a client computer and running it there is a non-starter because that's a violation of licensing or a ridiculously expensive implementation of SQL Server. Legacy note: The precursor technology to SSIS, DTS, allowed you to build your package and then convert to an .exe which was then able to do precisely what you want.

Running an SSIS package is constrained to dtexe.exe, using the ManagedDTS assemblies, running it from the SSISDB, running a SQL Agent job (which is really just using dtexec.exe).

What's in our toolkit that we can leverage to make this work?

Working backwards, you could create a SQL Agent job for each user (that way you don't have to worry about coordinating user executions/errors trying to run a job that's already running) that might need to run a package and then your solution is a PowerShell script that just starts the agent job and polls until it's complete.

SSISDB is out as you're using the package deployment model

You could create a Windows Service that listens for requests from client computers and then runs SSIS packages. That's a lot of coding and I don't have enough details (or energy) to sketch out the basics.

We're left with running dtexec.exe While I've never tried it, you could look at PowerShell Remoting Once you have all the security bits set up, that should allow your clients to launch dtexec through the various PS options

Non-PowerShell options would be to use PsExec or WMI as outlined in this answer https://stackoverflow.com/a/2343745/181965