deployment - Deploying SSIS (SQL Server 2012) Project Outside Network

Question

I have built an SSIS project on my PC, and am connecting to an SQL 2012 Server outside our network (via SQL Authentication) while executing the package. It is now time to deploy the project to that same server, but I have hit a wall. While going through the built-in deployment wizard, I enter the Server Name (just as I have entered it to connect to the database withing the connection managers) and get an “untrusted domain” error (it is requiring Window’s Authentication rather than SQL Authentication).

I think it may also be relevant that I am using parameters to set environment-specific values in the project.

How do I deploy an SSIS project to a SQL Server outside the network?

I should also add that in my searches, I did come across someone who was struggling to do this with a VPN connection (with no resolution posted), is that how this is typically done?

Answer 1

You've got 3 tools for deploying .ispac files into the SSISDB catalog.

1. ISDeploymentWizard.exe
2. ManagedObjectModel
3. TSQL

You are already using the wizard and it's not working due to auth issues. I think even with your MOM approach, you'll still run into auth issues. That leaves the TSQL approach and since you have a valid logon, hopefully this will work.

The following code will serialize your .ispac, create the deployment folder if it does not already exist, deploy the project and then assign a parameter value.

In SSMS, you will need to change your mode to SQLCMD mode which is available under the Query menu. After doing that, hit Ctrl-Shift-M to bring up the macro-thing and it will allow you to specify where the .ispac file can be found.

USE SSISDB
GO

IF ('$(isPacPath)' = '$' + '(isPacPath)')
BEGIN
    THROW 50000, N'This script must be run in SQLCMD mode.', 1;
END
GO

-- You must be in SQLCMD mode
-- setvar isPacPath "C:sandboxSSDTDeployTSQLDeployinDevelopmentTSQLDeploy.ispac"
:setvar isPacPath "<isPacFilePath, nvarchar(4000), C:sandboxSSDTDeployTSQLDeployinDevelopmentTSQLDeploy.ispac>"



DECLARE
    @folder_name nvarchar(128) = 'TSQLDeploy'
,   @folder_id bigint = NULL
    -- this must match the ispac
,   @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;

-- Check to see if something went awry
SELECT
    OM.* 
FROM
    catalog.operation_messages AS OM;

-- Use this to set parameters
-- http://msdn.microsoft.com/en-us/library/ff878162.aspx
EXECUTE catalog.set_object_parameter_value 
    -- Use the value 20 to indicate a project parameter 
    -- or the value 30 to indicate a package parameter
    @object_type = 20 
,   @folder_name = @folder_name
,   @project_name = @project_name
,   @parameter_name = N'' -- nvarchar(128)
,   @parameter_value = NULL -- sql_variant
,   @object_name = N'' -- nvarchar(260)
,   @value_type = '' -- char(1)
--  Use the character V to indicate that parameter_value is a literal value 
-- that will be used by default if no other values are assigned prior 
-- to execution. 
-- Use the character R to indicate that parameter_value is a referenced value 
-- and has been set to the name of an environment variable. 
-- This argument is optional, the character V is used by default

If you attended the SQL Pass Summit 2012 conference, I demoed this in my talk on the 2012 Deployment Model but I didn't cover the parameter part. I believe that last call to be correct but I have not verified it. I have linked to the documentation for the procedure call so that you can tailor it to your specific needs.

Sample PowerShell implementation

Update August 2013

I've learned something at my current client. We have our laptops which are joined to the home domain. We have accounts on the client's AD network. Whenever I need to "do" something in the client's world, I need to launch my process and instruct it to present my "foreign" credentials. What makes this possible is RunAs. Alternate reference

I created a suite of batch files that launch every process I need. They take the form of

runas /netonly:ForeignDomainDoppelganger "C:windowssystem32cmd.exe"

I have one for a command prompt (above), Visual Studio, SSMS, PowerShell, PowerShell ISE and some other specialty apps that need to work with their domain.

Using the runas approach, I've been able to deploy packages using all of the above methods (as well as deploying directly from a Visual Studio instance being run with foreign credentials).