VERIFIED SOLUTION i

Resolve Portrait Shared Server issues with the Populate DW SSIS package

Issue

There is an issue with the Populate DW SSIS package in Interaction Optimizer (IO) 5.6/Portrait Dialogue (PD) 6.1. The package was installed from the PD 6.1 media and configured for use with PD and IO.

The package appears to be confused as to whether a previous instance of the package is still running.  The problem is in the ‘Check if Package is Already Running’ script within Controller.dtsx.

We tried removing the check in the SSIS process flow and the job now runs properly, but we really should have this check in place.
There is a risk to the integrity of the data within the DW if we have more than one instance running at a time.

Getting following error when executing the Portrait SSIS Data Warehouse controller job on SQL Server 2014.  Have tried to run the SSISUpgrade.exe to refresh the packages. 

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

10/28/2015 11:45:21,PopulateDW,Error,,[ServerName],PopulateDW,

    ,,The job failed.  
        The Job was invoked by User [ServerName]\[user].  
            The last step to run was step 1 (Update Portrait DataWarehouse).,00:00:00,0,0,,,,0

10/28/2015 11:45:21,PopulateDW,Error,1,[ServerName],PopulateDW,
    Update Portrait DataWarehouse,,    
        Executed as user: [ServerName]\[systemUser]. 
            Microsoft (R) SQL Server Execute Package Utility  Version 12.0.2000.8 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    
            Started:  11:45:21 AM  
            Error: 2015-10-28 11:45:21.17     
                Code: 0x00000006     
                Source: Check if package is already running      
                Description: The script returned a failure result.  
            End Error  
            Error: 2015-10-28 11:45:21.17     
                Code: 0x00000001     
                Source: Check if package is already running      
                Description: Exception has been thrown by the target of an invocation.  
            End Error  
                DTExec: The package execution returned DTSER_FAILURE (1).  
                Started:  11:45:21 AM  
                Finished: 11:45:21 AM  
                Elapsed:  0.141 seconds.  The package execution failed.  The step failed.,00:00:00,0,0,,,,0
 

Cause

There is a Microsoft Knowledge Article describing this issue: https://support.microsoft.com/en-us/kb/2000474

It seems that changes to SQL Server (Service Packs, Cumulative Updates, Hotfixes, etc.) may cause this issue. This is a known Microsoft issue.

From the Microsoft article:
Installation of the service packs, hotfixes, and cumulative updates resets DCOM permissions for launching and accessing Integration Services service.
The permissions are reset to default permissions. If you have customized DCOM permissions, you will need to reapply the customization.
 

Resolution

UPDATED: August 1, 2017
  • Open Component Services (dcomcnfg.exe)
  • Navigate to Component Services/Computers/My Computer/DCOM Config and find either “Microsoft SQL Server Integration Services 12.0” or MsDtsServerNNN in the list (depending on what version of SQL Server you have).
  • Right-click and choose Properties
  • Open the Security tab.
  • Click the Edit... button under the Launch and Activate Permissions section.
  • Add the UserID of the credentials under which the SQL Agent job runs, for example “NT Service\SQLSERVERAGENT”.  It will be changed to SQLSERVERAGENT once it is added.
  • For this new user, ensure that all four of the Allow Permissions checkboxes are selected (see image below).
  • Hit OK on the two dialogs to accept the changes.

User-added image