SSIS PowerPack 5.3.0 released

Posted on by
Contents hide
1 Introduction
2 New Features/Improvements
3 Bug fixes

Introduction

This release brings tons of new features and bug fixes. We have added new connectors for CosmosDB, Azure DevOps (TFS), ServiceNow, MailChimp, HubSpot, JIRA, Zoho and REST API Task. It fixes important issues with connectors for Youtube, SharePoint and XML / CSV Source. To see full release notes click here

New Features/Improvements

  • NEW: API Connector – CosmosDB – New connector to Read / Write / Query documents and other objects (e.g. Database, Containers)
  • NEW: API Connector – Azure DevOps (TFS) – New connector to Read / Write / Query items (e.g. Tasks, Bugs, User Story)
  • NEW: API Connector – ServiceNow – New connector to Read / Write / Query tables (e.g. Incidents, Tasks, Users)
  • NEW: API Connector – MailChimp – New connector to Read / Write / Query MailChimp data (e.g. Campaigns, Lists, Subscribes, Reports)
  • NEW: All Components – Sort Used columns on [A-Z] or [Z-A] button click
  • NEW: Amazon Redshift Data Transfer Task – Add support for Redshift Serverless
  • NEW: Amazon Storage Connection – Add logic to handle Retry on IllegalLocationConstraintException (UseRegionSpecificEndPoint Advanced option checked but wrong region selected)
  • NEW: Amazon Storage Task – Add new action to read Tags for S3 Files (GetAmazonFileTags)
  • NEW: Amazon Storage Task – Add option to set Tags for S3 File Upload
  • NEW: Amazon Storage Task, Azure Storage Task – Allow to use Variable Placeholders in Metadata
  • NEW: API Connection Manager – Automatically Set TrustedDomains on REST Connection UI when user loads Custom Connector File from local path first time (Prompt User)
  • NEW: API Connector – Google Drive – Always show Query parameter (Search Criteria) and show common examples in the dropdown
  • NEW: API Connector – HubSpot – Add Upload File endpoint
  • NEW: API Connector – JIRA – Add OAuth support for authentication
  • NEW: API Connector – JIRA – Add support for custom fields with Paragraph datatype
  • NEW: API Connector – JIRA – Add support for selecting which custom_fileds you like to output on connection UI or on endpoint level
  • NEW: API Connector – JIRA – Improve speed of fetching data by 2x (also reduce number of requests by enhancing last page detection logic)
  • NEW: API Connector – SharePoint – Add ‘Filter’ parameter to ‘get_list_items’ and ‘get_list_items_dynamic’ endpoints
  • NEW: API Connector – Zoho – Updates to DELETE, SEARCH endpoints
  • NEW: API Connector Framework – Add OptionsEndPointSortBy to allow sort by expression for Select Dropdown (e.g. Column1 ASC, Column2 DESC)
  • NEW: API Connector Framework – Add Support for DefaultValue in Param / Column (so when Value is blank it will return DefaultValue and ignore ValueTemplate / Functions property)
  • NEW: API Connector Framework – Allow <Table Order=”-1″ Expand=”True”… > So you can sort dynamically expanded list by name
  • NEW: API Connector Framework – Allow ColumnInfoMap in <Column> Tag to use DataType lookup by field value (equal, contains, regex match)
  • NEW: API Connector Framework – Allow Multiple columns in OptionEndPointLabel using comma separated (e.g. ProjectId,FieldName)
  • NEW: API Connector Framework – Allow Multiple Filters to join result from multiple arrays e.g. Filter=”$new_rows[*]” ExtraFilters=”$updated_rows[*] :: $error_rows[*]” and ExtraFilterColumns=”Op=New::Op=Updated::Op=Error”
  • NEW: API Connector Framework – Allow Parameters in Filter Property (e.g. Filter=”$.[$param$][*]”)
  • NEW: API Connector Framework – Allow to disable Table Expand (DoNotValidateTableName)
  • NEW: API Connector Framework – For Write Operations (INSERT, UPDATE) – Allow to use Direct Placeholder Functions in Body / URL (right now it only works when Parameter usage found in body, it doesnt work if you do Body={ data : “<<{$rows$},FUN_BASE64ENC>>” } )
  • NEW: API Connector Framework – Use EndPoint Parameters with Default when Columns are expanded and no overrides found with parameter name used in DataEndPointParameters
  • NEW: Azure Storage Task – Add new action to read Tags for Blob (GetAzureBlobTags)
  • NEW: Azure Storage Task – Add option to set Tags for Blob Upload
  • NEW: Azure Storage Task – need to improve UI in Metadata tab to show user can use variables in Value column
  • NEW: Azure Storage Task – Provide an Action to Read Tags (just like GetMetaData option)
  • NEW: General – Add FUN_XML_TO_TEXT to extract data from XML using XPath
  • NEW: General – FUN_REGEX_EXTRACT allow to use group name in extract pattern (e.g. \w+@(?<domain>\w+).com{{0,domain}} )
  • NEW: General – New Function – FUN_IF – Compare two values and return match or non-match string (e.g. <<Abc|~|Eq|~|Abc|~|Both $1 and $2 same|~|Not same,FUN_IF>> )
  • NEW: General – New Function – FUN_IF_REGEX_MATCH – Return If Else Value on Regular Expression match or mismatch (e.g. <<Abc (123)|~|\d+|~|Found $2|~|No found|~|true,FUN_IF_REGEX_MATCH>>
  • NEW: General – Update AWS SDK to v3.7 (Add support for IMDSv2 in IAM Role and many more)
  • NEW: HTTP Connection Manager – Add [$url-part-regex-(some-regex-here)$] placeholder in HashSignatureFormat
  • NEW: HTTP Connection Manager – When you enable Retain Cookie Option with Dynamic Token – You may get error about Cookie format
  • NEW: HTTP Connection Manager, JSON / XML / CSV Source, REST API Task, Web API Destination – Allow Cookie Container Feature for SSL Version set other than Default
  • NEW: HTTP, OAuth Connection Manager – Throw meaningful error when certificate is null (Failed to load)
  • NEW: JSON / XML / CSV Source – Allow to continue on Error for multiple status code (e.g. 404|405) – New Property ErrorStatusCodeToMatchRegex
  • NEW: JSON / XML / CSV Source – Add support for reading *.tar.gzip file (multiple files in one tar.gzip)
  • NEW: JSON / XML / CSV Source, REST API Task – ClientCertificate not supported if you change SslVersion settings other than Default
  • NEW: JSON / XML Generator Transform, Export Task – Add option to output nested JSON /XML as encoded string – Set datatype=”String” for Unbound <map> node (Usecase: MailChimp Bulk API / Azure DevOps Update/Insert)
  • NEW: JSON Generator Transform, Export JSON Task – Allow Placeholder Function usage for <map> tag (e.g. <map src=”notes” function=”FUN_BASE64ENC” /> or function=”sometext <<{$value$},FUN_BASE64>> sometext” in Template mode
  • NEW: JSON Source – Treat “$” as blank filter
  • NEW: JSON Source, XML Source, CSV Source – Allow API Connector Wizard to use Column Metadata for new EndPoint in Connector File
  • NEW: JSON Source, XML Source, CSV Source – Include Properties in EndPoint as Default Props when cannot be detected by Wizard as Valid Properties
  • NEW: OAuth / HTTP Connection Manager – Add MetaUrl, MetaHeaders, MetaMethod, MetaBody, MetaFilter to get API base URL (dynamic URL case like MailChimp, JIRA OAuth)
  • NEW: Regular Expression Parser Task – Add –regex-cs (suffix flag in pattern) for case-sensitive pattern search (Right now searches are not case-sensitive)
  • NEW: REST API Task – Allow to continue on Error for multiple status code (e.g. 404|405) – New Property ErrorStatusCodeToMatchRegex
  • NEW: REST API Task – Give Options (DisableAutoConvertMultiPartStream and TreatResponseAsMultiPart) to enable / disable multipart detection for stream (Right now we auto detect / convert based on Response Content-Type but no way to enable/disable)
  • NEW: REST API Task, JSON / XML / CSV Source – Allow custom content-type in header for MutiPart Upload (e.g. myfilename=@c:\somefile.txt)

Bug fixes

  • FIX: All Components – Dataflow components loses the locked column names when sorts Available Columns (i.e. Click [A-Z] –or– [Z-A] toolbar button)
  • FIX: All Components – Renaming Output Column Name should not break the flow
  • FIX: Amazon Redshift Data Transfer Task – Throw meaningful error when Source SQL Produce blank column name (no alias defined for calculated / static data column)
  • FIX: API Connection Manager – Preview works but Test Connection Fails – Embedded API Config Text is empty (Only when you switch to Embedded Mode)
  • FIX: API Connector – SharePoint – Refresh Columns might throw error in some cases if you Do not supply SiteId at the EndPoint level (Connection Level value is ignored)
  • FIX: API Connector – Youtube – Embedded Provider selection throws error
  • FIX: API Connector – YouTube – Improve speed by reducing number of API calls for get_videos (MyVideos Table) – Change Rows Per Page from 2 to 50
  • FIX: API Connector Framework – Allow {$rows$} in Functions for Query Type Parameters for Bulk Lookup
  • FIX: API Connector Framework – Column Name is used instead of Label is used to generate {$rows$} (when LayoutMap not supplied)
  • FIX: API Connector Framework – Columns with same Lable but Different Name cause issue (Last column with same Label takes precedence)
  • FIX: API Connector Framework – Common Properties (e.g. Label, Desc, HelpLink) always takes precedence from Template rather than node override
  • FIX: API Connector Framework – Duplicate Column Name in Dynamic Template cause issue (Static Column added at EndPoint level and same found in Dynamically Expanded)
  • FIX: API Connector Framework – generic_request endpoint is not working when you use Partial URL starts with slash (e.g. /customers) or Full URL (http://abc.com/customers)
  • FIX: API Connector Framework – If Service URL contains variable e.g. (https://[$Domain$].zendesk.com) then some bulk endpoints (e.g. BULK Delete / Update / Insert – Using Full Job Style URL from Child EndPoint) might fail
  • FIX: API Connector Framework – MaxRows Settings not working for ChildEndPoint
  • FIX: API Connector Framework – Paginated request might fail if you use {$rows$} placeholder in URL (it wont replace after first request)
  • FIX: API Connector Framework – Query Builder on UI might send blank rather than NULL value for Dependency Parameter (OptionEndPointParameters)
  • FIX: Google Analytics Source – Connection Manager set GA4 in Connection Manager + adding filter in GA Source – getting error on PREVIEW
  • FIX: Google Analytics Source – ERR_NO_DATA_OR_INVALID_EXPR: No records found in specified table. Manually enter output columns.
  • FIX: Google Data Connection Manager – You might get JavaScript Error on Generate OAuth Token
  • FIX: HTTP Connection Manager – HashSignature Method sometimes throws error about Position if you use [$url-part-zzz-x-y$] placeholder in HashSignatureFormat
  • FIX: Http Connection Manager – JTW token Claims not loaded on UI after save
  • FIX: JSON / XML / CSV Source – API Connector File Wizard doesnt add PagingByUrlCurrentPage Property if PagingMode set to URLPath Mode or ByPostData mode
  • FIX: JSON / XML / CSV Source – API Connector Wizard Saves Headers as XML rather than Raw format in final connector file
  • FIX: JSON / XML / CSV Source – Pagination Mode – ByResponseHeaderRfc5988 – Fails in some cases (e.g. ServiceNow API)
  • FIX: JSON Generator Transform – Output as array node not persisted on UI for unbound node
  • FIX: JSON Source – API Connector Wizard should set PageStartVariable automatically when Variable is set
  • FIX: JSON Source, XML Source – Pagination doesnt stop (goes infinite loop) when you have Error Output attached
  • FIX: JSON Source, XML Source – When Suffix used with question mark it doesnt wipe out original parameters from the URL as indicated in the help
  • FIX: OAuth – Vsts Provider – response_type sent twice
  • FIX: OAuth Connection – If Password / ClientCredentials Grant used and RefreshToken is returned in response then sometimes it may fail request with Unauthorized (due to blank AccessToken)
  • FIX: OAuth Connection Manager, API Connection – Use SaveAs dialog rather than Open for Refreshtoken file path
  • FIX: REST API Task, JSON / XML Source / Web API Destination – Multi Part Content Response may throw error – startIndex cannot be larger than length of string. Parameter name: startIndex (This only happens if two new lines found before first boundary)
  • FIX: Upsert Destination – Cannot select Custom Order By dropdown when Duplicate Handling is changed (its always disabled) – You can use Properties window to change it but not via UI
  • FIX: XML / CSV Source – API Connector Wizard doesnt set EndPoint ResponseFormat=Xml or Csv
  • FIX: XML Source – You might get error about Could not find my.dtd file when <!DOCTYPE my.dtd> tag found in the XML file (Preview works fine but runtime fails)
Posted in Announcements.