Enhancing SSIS ETL Tools with SolarWinds Task Factory
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?
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.
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.
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.
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.
Image 4 – Task Factory Dimension Merge SCD Configuration
- Request Demo
- Download Free Trial
- For a complete video library on Task Manager click here
- Contact SolarWinds with your questions for support and pricing inquiries (+1-866-530-8100 | [email protected])
MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of Task Factory.
About the author
View all my tips
Article Last Updated: 2023-03-26