Background
The 2012 SSIS Project Deployment model in Visual Studio contains a file for project parameters, project level connection managers, packages and anything else you've added to the project.
In the following picture, you can see that I have a Solution named Lifecycle. That solution has a project named Lifecycle. The Lifecycle project has a Project Level Connection Manager ERIADOR defined and two SSIS packages: Package00.dtsx and Package01.dtsx.
When you run a package, behind the scenes Visual Studio will first build/compile all the required project elements into a deployable quantum called an ispac (pronounced eye-ess-pack, not ice-pack). This will be found in the binDevelopment subfolder for your project.
Lifecycle.ispac is a zip filed with the following contents.
What's all this mean? The biggest difference is that instead of just deploying an updated package, you'll need to deploy the whole .ispac. Yes, you really have to redeploy everything even though you only changed one package. Such is life.
How do I deploy packages using the SSIS Project Deployment model?
You have a host options available to you but at the 3 things you will need to know are
- where is my ispac
- what server am I deploying to
- what folder does this project to
SSDT
This will probably be your most common option in the beginning. Within SQL Server Data Tools, SSDT, you have the ability to define at the Configuration Manager level what server and what folder things are deployed to. At my client, I have 3 configurations: Dev, Stage, Production. Once you define those values, they get saved into the .dtproj file and you can then right click and deploy to your heart's content from visual studio.
ISDeploymentWizard - GUI flavor
SSDT is really just building the call to the ISDeploymentWizard.exe which comes in 32 and 64 bit flavors for some reason.
- C:Program FilesMicrosoft SQL Server110DTSBinnISDeploymentWizard.exe
- C:Program Files (x86)Microsoft SQL Server110DTSBinnISDeploymentWizard.exe
An .ispac extension is associated to the ISDeploymentWizard so double click and away you go. The first screen is new compared to using the SSDT interface but after that, it will be the same set of clicks to deploy.
ISDeploymentWizard - command line flavor
What they got right with the 2012 release that sucked with the package deployment model was that the manifest file can be deployed in an automated fashion. I had a workaround but it should have been a standard "thing".
So look carefully at the Review tab from either the SSDT or GUI deploy. Isn't that a beauty?
Using the same executable, ISDeploymentWizard, we can have both an attended and unattended installer for our .ispac(s). Highlight the second line there, copy paste and now you can have continuous integration!
C:Program FilesMicrosoft SQL Server110DTSBinnISDeploymentWizard.exe
/Silent
/SourcePath:"C:DropboxpresentationsSSISDB LifecycleLifecycleLifecycleinDevelopmentLifecycle.ispac"
/DestinationServer:"localhostdev2012"
/DestinationPath:"/SSISDB/Folder/Lifecycle"
TSQL
You can deploy an ispac to SQL Server through SQL Server Management Studio, SSMS, or through the command line, sqlcmd.exe. While SQLCMD is not strictly required, it simplifies the script.
You must use a windows account to perform this operation though otherwise you'll receive the following error message.
The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
Furthermore, you'll need the ability to perform bulk operations (to serialize the .ispac) and ssis_admin/sa rights to the SSISDB database.
Here we use the OPENROWSET with the BULK option to read the ispac into a varbinary variable. We create a folder via catalog.create_folder if it doesn't already exist and then actually deploy the project with catalog.deploy_project. Once done, I like to check the operations messages table to verify things went as expected.
USE SSISDB
GO
-- You must be in SQLCMD mode
-- setvar isPacPath "C:DropboxpresentationsSSISDB LifecycleLifecycleLifecycleinDevelopmentLifecycle.ispac"
:setvar isPacPath "<isPacFilePath, nvarchar(4000), C:DropboxpresentationsSSISDB LifecycleLifecycleLifecycleinDevelopmentLifecycle.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;
-- Check to see if something went awry
SELECT
OM.*
FROM
catalog.operation_messages AS OM
WHERE
OM.operation_message_id = @operation_id;
Your MOM
As in, your Managed Object Model provides a .NET interface for deploying packages. This is a PowerShell approach for deploying an ispac along with creating the folder as that is an option the ISDeploymentWizard does not support.
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null
#this allows the debug messages to be shown
$DebugPreference = "Continue"
# Retrieves a 2012 Integration Services CatalogFolder object
# Creates one if not found
Function Get-CatalogFolder
{
param
(
[string] $folderName
, [string] $folderDescription
, [string] $serverName = "localhostdev2012"
)
$connectionString = [String]::Format("Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;", $serverName)
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$integrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices($connection)
# The one, the only SSISDB catalog
$catalog = $integrationServices.Catalogs["SSISDB"]
$catalogFolder = $catalog.Folders[$folderName]
if (-not $catalogFolder)
{
Write-Debug([System.string]::Format("Creating folder {0}", $folderName))
$catalogFolder = New-Object Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder($catalog, $folderName, $folderDescription)
$catalogFolder.Create()
}
return $catalogFolder
}
# Deploy an ispac file into the SSISDB catalog
Function Deploy-Project
{
param
(
[string] $projectPath
, [string] $projectName
, $catalogFolder
)
# test to ensure file exists
if (-not $projectPath -or -not (Test-Path $projectPath))
{
Write-Debug("File not found $projectPath")
return
}
Write-Debug($catalogFolder.Name)
Write-Debug("Deploying $projectPath")
# read the data into a byte array
[byte[]] $projectStream = [System.IO.File]::ReadAllBytes($projectPath)
# $ProjectName MUST match the value in the .ispac file
# else you will see
# Failed to deploy the project. Fix the problems and try again later.:The specified project name, test, does not match the project name in the deployment file.
$projectName = "Lifecycle"
$project = $catalogFolder.DeployProject($projectName, $projectStream)
}
$isPac = "C:DropboxpresentationsSSISDB LifecycleLifecycleLifecycleinDevelopmentLifecycle.ispac"
$folderName = "Folder"
$folderName = "SSIS2012"
$folderDescription = "I am a description"
$serverName = "localhostdev2012"
$catalogFolder = Get-CatalogFolder $folderName $folderDescription $serverName
Deploy-Project $isPac $projectName $catalogFolder
