You've got 3 tools for deploying .ispac files into the SSISDB catalog.
- ISDeploymentWizard.exe
- ManagedObjectModel
- 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:\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.ispac"
:setvar isPacPath "<isPacFilePath, nvarchar(4000), C:\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.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:ForeignDomain\Doppelganger "C:\windows\system32\cmd.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).
