SSIS PowerPack v2.9 released (Upsert Destination, Salesforce BULK API and more…)

What’s New

In this release we focused on mainly providing high quality and super fast Upsert Destination (Bulk Update, Insert and Delete / Merge) for SQL Server. We also added Salesforce BULK API support for Salesforce Destination.

Full Notes:

Version [Feb 08, 2019]

New Features/Improvements

  • NEW: Amazon S3 CSV File Source, FTP, Azure – Add UI validation for connection selection (Do not allow preview if selection is not valid)
  • NEW: CSV Destination for Azure, AWS, FTP – Validate for no input columns selected
  • NEW: Export CSV File Task – Change default delimiter as comma and add enable QuoteAroundValueIfNeeded option by default
  • NEW: General – License Manager command line registration doesnt log errors to file (Command line is useful in ADF – SSIS IR in Azure Data Factory – License Registration in setup script)
  • NEW: General – Reduce verbosity of connection not assigned validation error
  • NEW: HTML Email Task – Provide an option to prevent HTML Encoding when you insert Recordset Variable (add new placeholder {{User::varRecordset,table_no_encode}} )
  • NEW: HTTP Connection Manager – Add option to retain Cookies in Dynamic Token Method (e.g. SAP Odata / SOAP API use case)
  • NEW: JSON, XML Source – Provide an option to set CharacterSet / Encoding just like REST API Task (e.g. Support UTF-8 without BOM )
  • NEW: Salesforce Destination – Add support for Bulk API for very large dataset
  • NEW: Secure FTP Connection – Lower the default minimum Key Size to 512 bit to support old version keys (Some customers reported problem with default 1023 bit size key we forced in last version)
  • NEW: Secure FTP Task – Show file size in log
  • NEW: Upsert Destination – Bulk Update, Insert, Delete and Merge for SQL Server

Bug fixes

  • FIX: Amazon S3 CSV Source – When File name contains colon ( : ) it may not work when you hit preview (You get 404 Error)
  • FIX: CSV File Destination – Object reference not set error may be thrown on ReleaseConnection
  • FIX: Export JSON File Task, Export XML Task, Export CSV Task- Input string was not in a correct format.Couldn’t store <> in SomeIntegerColumn. Expected type is Int32
  • FIX: JSON Generator Transform – Hidden columns with Include in output option may be NULL if Multiple rows included in output document
  • FIX: PostgreSql Source, Amazon Redshift Source – License is not validated correctly
  • FIX: Salesforce Connection Manager – For long running salesforce JOB you may get session timeout error after 2 hours
  • FIX: Secure FTP Connection Manager – You cannot specify Host Key Exchange algorithms, if you set you get server and client has no common algorithm error
  • FIX: Secure FTP Connection Manager – You may get error – Negotiation failed. Server signature is not valid
  • FIX: Secure FTP Task – Progress event shows counter starting from zero rather than one
  • FIX: XML Source, XML Parser Transform – When 2D Transform Enabled , Cannot deal with XSI:NIL or self closing nodes correctly: Type cast exception


SSIS Upsert Destination

In this release we added very useful component for Upsert operation (in SQL Server Database for now). This component can perform High performance Bulk Upsert (Insert + Update) in a single operation. It uses Key based Lookup. You can also do Bulk Delete or Bulk Update.

SSIS Upsert Destination Example - Bulk Insert or Update in SQL Server (Merge Records)

SSIS Upsert Destination Example – Bulk Insert or Update in SQL Server (Merge Records)

Salesforce BULK Update Feature

We also added highly requested feature in Salesforce to use BULK API for very large dataset. By default Salesforce Destination uses SOAP API for Insert operation. Which is good for most part if you don’t have large dataset. For millions of rows it won’t perform good. This is why we added Bulk API mode.

We added two new properties. EnableBulkApiMode and MaxRowsPerJob. When EnableBulkApiMode is enabled BatchSize is ignored.

SSIS Salesforce Destination - Upload Data using BULK API mode

SSIS Salesforce Destination – Upload Data using BULK API mode

Posted in Announcements, SSIS Salesforce Destination, SSIS Upsert Destination and tagged .