4

I'm extremely frustrated. I'm using SQL Server Data Tools 2015. I have an Integration Services project and I want to deploy a single package of this solution to SQL Server 2012. I've set the solution to Deployment Target Server Version to SQL 2012. I right click on the package and select Deploy, however at the Select Destination part of the wizard, it only allows me to select a server name (and not enter any credentials). However, I need to enter a specific username and password as my domain account is not a valid login.

I understand that that wizard will eventually run isdeploymentwizard.exe and I looked at the parameters for it as well and do not see a way to enter any connection information outside of a server name.

How can I deploy this package to the server using a username and password instead of a trusted account? I feel like I must be missing something incredibly stupid, because I was unable to find someone with a similar issue even after searching Google for an hour. Apparently everyone deploying to SQL Server from SSDT2015 is using a trusted account.

Edit: In older versions of BIDS, you could open the project and select File > Save Copy of .dtsx as then save it to the SQL server and use credentials. However in SSDT, the Save Copy of option only allows you to save the package locally.

3

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.

  • I assume the path specified in the tsql script for the .ispac file is local to the server and not the client machine if they're different, correct? – John Eisbrener Aug 21 '17 at 20:05
  • Correct @JohnEisbrener the OPENROWSET call will be pathed from the server's perspective – billinkc Aug 23 '17 at 14:33
1

According to this post on the MSDN forums, it's simply not possible. You must use Windows Authentication to deploy the package. However, I found that if I click 'Convert to Package Deployment Model', the Save Copy As function allows me to save it to the SQL Server. However, attempting to do so gives me the following message:

Storing or modifying packages in SQL Server requires the SSIS runtime and database to be the same version. Storing packages in earlier versions is not supported.

It appears that SSDT 2015 (though you can change the Deployment Target Server from SQL 2016 to either SQL 2012 or SQL 2014), will not allow you to save the package to the SQL Server of SQL 2012 or SQL 2014 without installing the SSIS runtimes.

A similar error is thrown by dtutil which appears to have the 110, 120, and 130 versions installed (in the C:\Program Files (x86)\Microsoft SQL Server\xxx\DTS\Binn directories) during the SSDT2015 install.

C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn>dtutil /?

Microsoft (R) SQL Server SSIS Package Utilities Version 11.0.6020.0 for 32-bit Copyright (C) Microsoft Corporation 2012. All rights reserved.

This application requires one of the components: Integration Services, Business Intelligence Studio, Management Tools - Basic or Database Engine to be installed by SQL Server 2012 Standard, Enterprise, Developer, Business Intelligence, or Evaluation Edition. To install a component, run SQL Server Setup and select the component name.

Installing Integration Services Shared Feature from the SQL 2012 setup did not change the error message when trying to use the 'Save Copy As' feature inside SSDT, however it did allow me to use dtutil to successfully copy the package to the server.

TLDR;

  • Convert your Integration Services project to use Package Deployment Model.
  • Make sure Integration Services Shared Feature is installed locally for the version of SQL you are deploying the package to.
  • Use dtutil to copy the package to the server, i.e. C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn>dtutil /FILE "C:\path\to\mypackage.dtsx" /DestServer SERVER\INSTANCE /DestUser MyUserName /DestPassword MyPassword /Encrypt SQL;PackageNameOnServer;2;PackagePassword (you can use a different encryption level or instead of using /Encrypt use /Copy, see documentation for dtutil)
0

If you close Visual Studio then open it from a command prompt with the following command it will prompt you for your password needed to connect to the other server.

 runas.exe /netonly /user:OTHERDOMAIN\OtherUser "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devenv.exe"

/netonly tells it to just use those alternate credentials when talking over the network. Change the OTHERDOMAIN\OtherUser to match the username needed to connect to the remote system. And then the last part is the path to Visual Studio 2015.

Once that's done you should be able to deploy without supplying alternate credentials again.

By the way, I would highly recommend you install the free BIDS Helper in order to leverage the Deploy SSIS Packages feature which makes it easier to deploy single packages. You still need runas.exe though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.