Sql-server – Unable to deploy SSIS package to remote sql server

If you are using SSISDB (which I think you are?), you can Execute a set of standard stored procedures that are in the SSISDB. These are outlined in the MSDN Article here.

This article also states the permissions that are required to run these procedures. It sounds like you need to set up a SQL Server login which has these permissions. How to describe this is located here. You will need to do this with your existing Windows Authenticated user if it has permission to allocate permissions (ALTER ANY LOGIN or ALTER LOGIN permissions).

You may also need to create a user who is mapped to this login as described here.

Your question is not very specific so without further information about a particular problem you are having, it is difficult to give a clear answer.

You have a lot going on here.

Package vs Project deployment model

Starting with the 2012 release, SSIS projects have the choice between a Package Deployment Model and a Project Deployment Model. Project allows you to create project level artifacts - shared connection managers, project (and package) parameters. The default deployment model for new projects is Project Deployment Model.

The deployable unit of code from a project deployment model is the .ispac file. Your SSIS packages + project parameters + shared connection managers + manifest file get zipped up into a file named MyProject.ispac. The ispac file is deployed to a SQL Server catalog called SSISDB.

The deployable unit of code from package deployment model is the SSIS package itself. You can deploy the .dtsx file to a File System, the SSIS Package Store (also just the file system) or a SQL Server instance. However, the SSIS package will be stored in the MSDB (sysdtspackages90/sysssispackages)

The 2016 release complicates the above matrix in that we can now deploy .dtsx package(s) into the SSISDB. This is to address the incremental deployment story. Behind the scenes, it will generate an ispac and that's what's deployed. But that's only for a 2016 server which won't help your 2012 story.

SSIS Versions

SSIS packages have versions that correspond to the version of SQL Server and tooling you're working with (2005/2008/2008 R2/2012/2014/2016/v.Next). Packages are only* forward compatible so if you make the mistake of opening a 2005 package with the 2014 tooling (or deploy with it) to a 2005 instance guess what, you've upgraded to the current version and deployed to a location that doesn't speak that dialect.

2016 confuses this issue because the tooling now supports targeting (Project -> properties, Configuration Properties -> General: TargetServerVersion)

Deployment

Ispacs are deployed via isdeploymentwizard.exe or you can do it in TSQL.

-- You must be in SQLCMD mode
-- Otherwise, modify the value of $(isPacPath) down below
:setvar isPacPath "C:\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.ispac"

DECLARE
    @folder_name nvarchar(128) = 'TSQLDeploy'
,   @folder_id bigint = NULL
,   @project_name nvarchar(128) = 'TSQLDeploy'
,   @project_stream varbinary(max)
,   @operation_id bigint = NULL;

-- Read the zip (ispac) data in from the source file
SELECT
    @project_stream = T.stream
FROM
(
    SELECT 
        *
    FROM 
        OPENROWSET(BULK N'$(isPacPath)', SINGLE_BLOB ) AS B
) AS T (stream);

-- Test for catalog existences
IF NOT EXISTS
(
    SELECT
        CF.name
    FROM
        catalog.folders AS CF
    WHERE
        CF.name = @folder_name
)
BEGIN
    -- Create the folder for our project
    EXECUTE [catalog].[create_folder] 
        @folder_name
    ,   @folder_id OUTPUT;
END

-- Actually deploy the project
EXECUTE [catalog].[deploy_project] 
    @folder_name
,   @project_name
,   @project_stream
,   @operation_id OUTPUT;

That sounds like what you want because you can be a local SQL User but if memory serves, the above functions do this impersonation jiggery that goes awry if you're not a Windows user.

This application requires one of the components..

With 2005/2008/2008 R2 you could only get SSDT, nee BIDS, with the installation media. This meant you needed Developer Edition at a minimum to build out your SSIS Packages. Starting with 2012, you could download the SSDT tooling directly from MS without having a license for SQL Server. However, you are only licensed for development purposes. The command line tools are not classified as development tooling so dtutil or dtexec will both fail a licensing check and report the above (or a similar error message).

The resolution, as you point out, is to install the Integration Services Service on the machine that requires "more" than just Integration Services development. Be aware that this does count as an installation of SQL Server from a licensing perspective so be sure the person in your organization that handles licensing is aware of machines where you have done this so you don't have an unpleasant surprise come audit time.