Troubleshooting SSIS PowerPack installation issues

[ZappySys]

Problem

Many times you get different errors which may be related to installation. This post we will review few most common errors and possible solutions.

If you have issue with toolbox items not appearing then please check this post. For Execution related errors refer below.

The connection type "ZS-MONGODB" specified for connection manager "MongoDB 1" is not recognized as a valid connection manager
— OR —
Code: 0xC0048021 Source: Data Flow Task Data Flow Task (01 [105]) Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "XML Source; ZappySys LLC (c) All Rights Reserved; SSIS PowerPack; http://www.zappysys.com/support"
— OR —
Exception deserializing the package "The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails."

–OR–

Cannot create task from XML for Task "SomeTaskName". Type SSIS.ReplacementTask {zzz-zzz-zzz-zzzz-zzz} due to error 0x08xxxxxxxxx the parameter is incorrect

Possible Cause

• SSIS PowerPack is not installed on the machine
• SSIS Service needs restart to flush out old version of dlls
• SSIS PowerPack installation is corrupted or missing runtime dlls for one or more SSIS version
• SQL Server is installed on other than C:\ and SSIS PowerPack didn’t copy files to correct location (If this is the case try latest installer Released on Sep-22-2016 or later)

Proposed Solutions

You can try few things to resolve such errors.

• Many customers got this error resolved by simply restarting Integration services and running job again
• If restart service doesn’t help then Uninstall existing SSIS PowerPack and re-install with default options (or use FULL Install option). To uninstall SSIS PowerPack goto Control Panel > Add/Remove Programs.
• Make sure SSIS PowerPack is installed on the machine where you executing SSIS Package. If you running SSIS Package via SQL Agent JOB or via SSIS Catalog then correct version of SSIS PowerPack must be installed on remote machine where SSIS Package is hosted or SQL Agent Job is created. If SSIS PowerPack not installed then install with default options (or select Full Install). After installation run package which contains SSIS PowerPack Tasks/Components see error is resolved.
• Sometimes SSIS PowerPack with default installation settings failed to detect correct location of SQL Server installation directory (Specially when SQL Server is installed on other than C:\). Perform the following checks to make sure installation files are correctly installed. Depending what version of SSIS is installed you can check following locations. If files are missing then possibly you missing SSIS PowerPack for that version of SSIS. To fix this issue you can run SSIS PowerPack installation and select Custom option when prompted (Don’t select Typical). On Feature selection page make sure correct version selected. After installation try to run package which contains SSIS PowerPack Tasks/Components see error is resolved
• File to check for SSIS/SQL Server 2017
  {drive:}\Program Files\Microsoft SQL Server\140\DTS\Tasks\ZappySys.PowerPack140.dll
  {drive:}\Program Files (x86)\Microsoft SQL Server\140\DTS\Tasks\ZappySys.PowerPack140.dll
• File to check for SSIS/SQL Server 2016
  {drive:}\Program Files\Microsoft SQL Server\130\DTS\Tasks\ZappySys.PowerPack130.dll
  {drive:}\Program Files (x86)\Microsoft SQL Server\130\DTS\Tasks\ZappySys.PowerPack130.dll
• File to check for SSIS/SQL Server 2014
  {drive:}\Program Files\Microsoft SQL Server\120\DTS\Tasks\ZappySys.PowerPack120.dll
  {drive:}\Program Files (x86)\Microsoft SQL Server\120\DTS\Tasks\ZappySys.PowerPack120.dll
• File to check for SSIS/SQL Server 2012
  {drive:}\Program Files\Microsoft SQL Server\110\DTS\Tasks\ZappySys.PowerPack110.dll
  {drive:}\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks\ZappySys.PowerPack110.dll
• File to check for SSIS/SQL Server 2008 / R2
  {drive:}\Program Files\Microsoft SQL Server\100\DTS\Tasks\ZappySys.PowerPack100.dll
  {drive:}\Program Files (x86)\Microsoft SQL Server\100\DTS\Tasks\ZappySys.PowerPack100.dll
• If none of above is your issue then you can try to restart SQL Server Integration Services (Start > All Programs > Microsoft SQL Server XYZ > Configuration Tools > SQL Server Configuration Manager > SQL Server Services > Restart “SQL Server Integration Services XYZ” ). After service restart try to run package which contains SSIS PowerPack Tasks/Components see error is resolved

If none of above helps then contact zappysys support (email support(at)zappysys.com or submit ticket here https://zappysys.com/support )

---

 

Error 2

License type [SSIS_PP_TRIAL] not found

Possible Cause

Above error may occur due to any of the following reasons

• Trial license is expired
• You don’t have valid license for the tasks/components you running inside your package (e.g. You bought JSON Source License but trying to run REST API Task)
• Account under which you trying to run SSIS Package (e.g. Run via SQL Agent Service Account) doesn’t have read/write permission to C:\ProgramData\ZappySys folder

Proposed Solutions

• Open license manager and make sure you have at least one valid license (e.g. Standard Edition, Trial License)
• If you already have valid license then make sure task throwing above error is allowed to run under your purchased license type. For example If you purchased JSON Source License but tying to run REST API Task somewhere in that package then system will try to search license types in following order…
  1. Search Standard Edition License … if not found
  2. Search Web API Integration Pack License… if not found
  3. Search for REST API Task License… if not found
  4. Search for TRIAL License… if not found
  5. In none of above found then throw error
• If you suspect you don’t have correct license then purchase valid license or request Trial extension from us so you can run SSIS packages using SQL Agent or Command line
• If you have valid license then make sure you assign full permission to SQL Agent Service Account or any other account under which Package is running so it can read/write to C:\ProgramData\ZappySys folder and all sub folders
• To detect if its permission related issue or not, try to run sample SSIS Package with at least one paid task (e.g. REST API Task). run following command on command line

  dtexec /F "c:\somefolder\yourpackage.dtsx"

• If above command works fine on command line under your user account but when executed via JOB (under service account) it fails then its most likely permission issue with Service account used by SQL Agent.
• If you suspect above issue with SQL Agent Job then try to run JOB Step using Proxy account. Here is the tutorial shows how to setup SQL Proxy Account to Run SSIS Job
• If proxy account also doesn’t help then contact ZappySys Support

 

[Author]

Posts