Enhancing SSIS ETL Tools with SolarWinds Task Factory

By:   |   Updated: 2021-12-07   |   Comments (2)   |   Related: More > Integration Services Development


Problem

SQL Server Integration Services (SSIS) has been the de facto ETL tool for over 15 years for DBAs, Developers and Business Intelligence Professionals to extract, transform and load data (i.e. ETL tasks) for specific business processes, data-centric applications, data warehousing, reporting and data exchange between organizations. SSIS is a great product with high adoption across the globe, but has some limitations related to modern data sources, performance and streamlining tedious tasks. How can we overcome these limitations and have SSIS provide greater value?

Solution

One solution that I would like to introduce is Task Factory from SolarWinds which is a data integration tool to enhance native SSIS Packages. Task Factory has both on-premises and cloud-based architectures to deliver new levels of performance and reliability, and new real-time functionality to SSIS Packages to reduce development time and deliver more value to the organization. Many of the SSIS components require little to no programming, purely configuring the component for your specific needs.

Task Factory integration in SSIS

Image 1 – Task Factory integration in SSIS

Task Factory Connectivity

With Task Factory’s seamless integration with SSIS in Visual Studio, there is access to 70+ prebuilt tasks, components and component managers delivering access to:

  • Azure ML Source, Destination and Execution Tasks
  • Amazon S3 Storage
  • Salesforce and Microsoft Dynamics – Source and Destination Connectors
  • SharePoint and Excel
  • REST source and destination API components (XML and JSON) with OAuth1 and OAuth2 authentication
  • Facebook, Twitter and LinkedIn
  • Email Source Adapter and USPS
  • And more…

Check out the complete list here.

Task Factory Rest Source Component

Image 2 – Task Factory Rest Source Component

Task Factory Components for Productivity

Beyond the new data source and destination options, Task Factory has over 30 SSIS Productivity components to help automate processes and save time. Some components include:

  • Secure FTP Task to support SFTP connections
  • Data Cleansing Transformation with 16 built-in algorithms to ensure data is accurate
  • Data Validation Transformation (regular expressions, email, phone, SSN) to clean data prior to database insertion or move rows to an exception table
  • XML Generator Transformation to generate an XML document based on source data
  • Data Flow Nugget to encapsulate business logic for reuse across SSIS Packages
  • Advanced Email and SMS Task to send informative emails with variables in order for team members to be able to take action
  • Hash Transformations to understand if changes have been made to specific columns
  • Zip or UnZip files with up to 90% compression to save time and disk space
  • PGP Encryption Task

Check out the complete list here.

Task Factory Secure FTP Task

Image 3 – Task Factory Secure FTP Task

Task Factory Performance

Performance is always a factor. Time and CPU cycles always cost money, whether the platform is on-premises, in the cloud, the system is OLTP or Business Intelligence related. Task Factory can reduce processing time and enable more timely business decisions with the following:

  • Upsert Destination – Conditional UPDATE or INSERT command that SolarWinds reports is 700x faster into SQL Server or Oracle as compared to native SSIS row-by-row operations. Learn more.
  • Dimension Merge SCD which is a replacement for the native SSIS Slowly Changing Dimension Wizard in SSIS supporting Type 0, 1, and 2 changes in a data warehouse. Once again, SolarWinds touts the ability to load a dimension up to 700x faster than native SSIS row-by-row operations. Learn more.
  • Developer Performance – The reality with all of these tools is Task Factory helps Data Professionals work smarter and faster to meet business needs with ease.
Task Factory Dimension Merge SCD Configuration

Image 4 – Task Factory Dimension Merge SCD Configuration

Next Steps

MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of Task Factory.



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips


Article Last Updated: 2021-12-07

Comments For This Article




Thursday, February 10, 2022 - 10:36:01 AM - Jeremy Kadlec Back To Top (89775)
Ken,

Thank you for the question. Sentry One joined SolarWinds. This is the same product and my understanding is that some of the original team are still working on Task Factory.

Please let me know if you have any additional product related questions or are interested in a demo.

Thank you,
Jeremy Kadlec
Community Co-Leader

Thursday, February 10, 2022 - 8:08:31 AM - Ken Baker Back To Top (89772)
Is it Solarwinds or Sentry One? I know of Sentry One's Task Factory, which is produced by Pragmatic Works, which Sentry One owns. Are there two different products with the same name?


sponsor