none
Does any one use Jenkins to deploy SSIS packages?

Answers

  • DTUTIL only copies the package. The config file you have to do it yourself.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Eileen Zhao Monday, November 26, 2012 7:16 AM
    Tuesday, November 20, 2012 9:03 PM

All replies

  • Icerni,

    Jenkins can execute bat files, right?

    Then you sure can

    Either Dtutil is necessary and there is no workaround or the manifest would be run in non-interactive mode

    Can I know why you are asking? Do you have any concerns?

    I would say just go experiment and share your findings here.


    Arthur My Blog

    Tuesday, November 20, 2012 6:27 PM
    Moderator
  • A new manager wants us to take this approach.  My only concern is the config file.  The last manager has us use the SSISDeploy.SSISDeploymentManifest, FileNameHere.dtsx, and FileNameHere.dtsConfig files to deploy to an instance.  I was reading about the DTUTIL.  It looks like this utility would move the dtsx file to the msdb database.  I am guessing I would have to create a bat file to move the config file to the proper directory.  I don't think the DTUTIL utility would move the config file.  Am I correct?  I am guessing the the SSISDeploymentManifest is sort of like the DTUTIL utility moving both the dtsx file and the config file.  But the DTUTIL utility only moves the dtsx file.

    lcerni

    Tuesday, November 20, 2012 7:39 PM
  • DTUTIL only copies the package. The config file you have to do it yourself.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Eileen Zhao Monday, November 26, 2012 7:16 AM
    Tuesday, November 20, 2012 9:03 PM
  • I know this is a pretty dated response, but I recently began deploying SSIS projects from Jenkins.  Our build workflow starts with a continuous integration build job that pulls down source from an SCM (git) and builds the SSIS projects using devenv.cmd.  I artifact the .ispac files and my powershell scripts to make it easier to reference the script from Jenkins.  I setup another job to deploy specific .ispac files using the Powershell plugin.  The deployment job gets artifacts from the most recent stable build. 

    Full Disclosure:  I am running Jenkins as a domain user in order to authenticate using Windows Auth.  The IntegrationServices classes will not deploy over connections established via SQL Server credentials.   

    Deploy.ps1:

    param
    (
    	[Parameter(Position=0)]
    	[string]$ServerName,
    	[Parameter(Position=1)]
    	[string]$ProjectName,
    	[Parameter(Position=2)]
    	[string]$ProjectFilePath,
    	[Parameter(Position=3)]
    	[string]$DeploymentFolder,
    	[Parameter(Position=4)]
    	[System.Collections.Hashtable]$Parameters
    )
    
    $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
    # Build ConnectionString from $ServerName
    $sqlConnectionString = $("Data Source=$ServerName;Initial Catalog=master;Integrated Security=True;")
    
    # Load the IntegrationServices Assembly
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;
    
    Write-Host "Connecting to server ..."
    
    # Create a connection to the server
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
    
    # Create the Integration Services object
    $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
    
    $catalog = $integrationServices.Catalogs["SSISDB"];
    $catalogFolder = $catalog.Folders[$DeploymentFolder];
    
    if (-not $catalogFolder) {
    	$catalogFolder = New-Object $ISNamespace".CatalogFolder" ($catalog, $DeploymentFolder, $DeploymentFolder);
    	$catalogFolder.Create();
    }
    
    Write-Host "Deploying " $ProjectName " project ..."
    # Read the project file, and deploy it to the folder
    [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
    $catalogFolder.DeployProject($ProjectName, $projectFile)
    	
    Write-Host "Configuring Parameters for Project ..."
    $project = $catalogFolder.Projects[$ProjectName];
    foreach($parameter in $Parameters.GetEnumerator())
    {
    	$project.Parameters[$parameter.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal, $parameter.Value);
    }
    $project.Alter();


    To invoke this from the Jenkins Powershell Build task...

    # The name or IP of the SQL Server
    $server = "My Server Name" 
    # The name of the SSIS Project
    $project = "SSIS Project Name"
    # The path to the .ispac file to deploy 
    $path = $env:WORKSPACE + "path to .ispac file"
    # The name of the folder (under the project) to deploy 
    $folder = "SSIS Folder Name" 
    # semi-colon delimited list of key/value pairs to configure project/package parameters and connection manager properties
    $arguments = @{"Key" = "Value"; "Key" = "Value"}
    . .\Path to Script\Deploy.ps1 $server $project $path $folder $arguments 


    Finding out the "Key" values for Connection Managers can be a little tricky.  All Connection Manager parameters begin with a "CM" prefix, followed by the name of the Connection Manager, and then the property name (separated by ".").  For example, the key for setting the ConnectionString of a Connection Manger named "MyCM" would be "CM.MyCM.ConnectionString".

    example:

    $arguments = @{"CM.MyOleDbCM.ConnectionString" = "Data Source=MyServer;Initial Catalog=MyDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;"; "CM.MyADONETCM.ConnectionString" = "Data Source=MyDB;Initial Catalog=MyDB;Integrated Security=True;"}

    I hope this helps someone.

    Mike

     
    Tuesday, April 07, 2015 6:35 PM