KingswaySoft's Premium Data Flow Components are designed to enrich and extend the features and functionalities in standard SSIS components, providing users with powerful tools to achieve more with increased efficiency. In essence, these Premium Data Flow Components boost the capabilities of native SSIS components, empowering SSIS developers with an extensive toolkit for seamless integration, advanced transformations, and streamlined data management.
Currently, KingswaySoft offers fourteen Premium SSIS Data Flow Components, including two connection managers.
Premium ADO.NET Components
The enhanced ADO.NET Components within the SSIS Productivity Pack provide advanced features for diverse relational database engines, including PostgreSQL, Oracle, SQLite, and MySQL. KingswaySoft offers the following three ADO.NET components:
The Premium ADO.NET Source component streamlines data retrieval from databases, leveraging the ADO.NET Connection Manager. Users can customize settings, such as connection manager, supported data providers, and transaction parameters, optimizing the extraction process. For instance, in a retail scenario, this component could efficiently extract product sales data from a MySQL database for further analysis.
The Premium ADO.NET Destination component stands as a robust counterpart, offering advanced capabilities to write to 11 data providers. With actions like Insert, Update, Delete, or Upsert, it efficiently manages data destinations. Configuration involves specifying connection details, actions, and mapping columns to destination fields. In a financial use case, this component could be employed to update a SQL Server database with daily transaction records, ensuring real-time data accuracy.
Finally, the Premium ADO.NET Command Task enhances control flow, executing commands directly within SSIS workflows. Whether encrypting, decrypting, or verifying signatures, this task provides flexibility in database interactions. Configurable settings include source and destination file paths, key locations, and error handling. In a healthcare context, this task might be utilized to encrypt sensitive patient information before transferring it to a secure SQL Server database, ensuring compliance with privacy regulations.
Premium Aggregate Component
The Premium Aggregate Component, featured in the SSIS Productivity Pack, serves as a versatile data flow transformation tool. This component allows users to aggregate input columns, grouping them into a cohesive dataset.
Configurable through the Premium Aggregate Editor, users can choose input columns, define output column names, and select from various aggregate operations, including Count, Count Distinct, First, Group By, Last, Maximum, Maximum Length, Minimum, and Minimum Length.
Within the Premium Aggregate Editor, the Input Column option enables users to specify the column on which the aggregate operation is applied, while the Output Column Name determines the name of the resulting aggregate output column for downstream use. The Operation field offers a range of aggregate operations to choose from, tailoring the transformation to specific requirements. The Properties Grid on the right side of the component provides a comprehensive interface for adjusting input and output column properties, including datatype modifications.
Additionally, the Premium Aggregate Component supports multiple outputs, allowing users to create and manage distinct outputs efficiently. Users can rename and remove outputs as needed, enhancing flexibility in designing the data flow transformation. In practical scenarios, this component could be employed in a sales analytics pipeline to aggregate total sales by product category, providing valuable insights for business decision-making.
Premium Conversion Component
The Premium Conversion Component, an integral part of the SSIS Data Flow, empowers users to dynamically alter or convert the data type of input columns. Configurable through two main pages, namely "Columns" and "Error Handling," this component offers extensive control over the data type conversion process.
On the "Columns" page, users can precisely specify which input columns undergo conversion, with the flexibility to toggle the selection of all relevant columns or apply productive filters based on names, input types, or output types.
The grid on this page provides comprehensive information, including the input column, original data type, new output data type, and a conversion status indicator. Further customization is facilitated through the Output Properties grid, allowing users to define specifics such as output column name, data type, length, precision, scale, and code page.
The "Error Handling" page, another crucial configuration aspect, empowers users to dictate how errors during the conversion process should be managed. With three options—Fail on error, Redirect rows to error output, and Ignore error—users can tailor the error-handling approach based on the specific needs of their data flow. Opting for the "Redirect rows to error output" feature efficiently segregates successfully converted rows from erroneous ones, enhancing visibility and facilitating targeted troubleshooting.
The Premium Conversion Component could be employed in a data integration pipeline where source data from various systems arrives with inconsistent data types. By leveraging this component, users can seamlessly standardize data types to ensure compatibility and coherence, thus optimizing the efficiency of downstream processing and analysis.
Premium Derived Column Component
The Premium Derived Column Component, a sophisticated extension of the native Derived Column Component in SSIS, offers users an intuitive interface akin to its built-in counterpart. While sharing the fundamental ability to add and manipulate derived columns through expressions, it distinguishes itself with over 294 functions, an advanced Expression Editor, expression validation with test values, and enhanced data type detection.
The Columns page enables users to specify and customize derived columns along with their expressions. With a user-friendly grid layout, users can seamlessly add, remove, and edit columns, defining output column names, input columns for derivation, and constructing expressions using a variety of elements like columns, variables, functions, casts, and operators. The automatically detected data type ensures accuracy, while additional properties like Length, Precision, Scale, and Code Page provide comprehensive control over column specifications.
Error Handling capabilities on the designated page allow users to tailor how errors are managed during the derivation process, offering options like Fail on error, Redirect rows to error output, and Ignore error. The Redirect rows to error output option proves particularly valuable, segregating successfully derived rows via the blue output connection, while the red 'Error Output' connection captures erroneous rows. The associated Expression Editor provides a dynamic multi-line textbox for crafting intricate expressions, facilitating testing with test values, and validating expressions before deployment. The diverse range of functions, neatly categorized, further enriches the Premium Derived Column's functionality, catering to intricate data transformation requirements within SSIS workflows.
For example, the Premium Derived Column Component can be instrumental in calculating key financial ratios, such as the debt-to-equity ratio, during the ETL (Extract, Transform, Load) process. By leveraging its extensive library of functions and a user-friendly interface, developers can create expressions to derive this ratio from existing financial data. The component's ability to handle errors ensures data accuracy, and its intuitive design streamlines the integration of these calculated metrics into downstream systems for comprehensive financial analysis.
Premium Excel Components
Tailored to meet developers' needs, the Premium Excel Components go beyond the capabilities of standard counterparts. Offering enhanced features such as runtime support, flexibility in file setup, and a variety of writing options, these components empower developers with greater control and efficiency in handling Excel data within the SSIS environment.
The Premium Excel Connection Manager establishes connections to local Excel files and supports password-encrypted files. Offering enhanced security and flexibility, this connection manager ensures seamless integration with Excel data sources in SSIS projects.
The SSIS Premium Excel Source Component offers extensive configuration options for efficient extraction of data from Excel files. It streamlines the process of reading data from Excel files, offering flexibility and control over extraction parameters and data attributes. Users can configure the source details, including worksheet names, indices, named ranges, and parameters such as start row and number of rows. Additionally, the Columns page provides a comprehensive view of available attributes, allowing users to selectively choose fields for extraction. The grid on this page enables users to customize attributes, including column names, data types, and properties like length, precision, and scale.
The SSIS Premium Excel Destination Component streamlines the process of writing data to Excel files with a user-friendly configuration spread across three pages: General, Columns, and Error Handling. The General page facilitates overall settings, including connection management, destination file path, and writing settings. The Columns page provides an intuitive grid for mapping upstream components to Excel fields, with detailed attribute customization options. The Error Handling page allows users to specify error-handling preferences, offering options to fail on error, redirect rows to error output, or ignore errors, enhancing the robustness of the data writing process.
Consider a scenario where financial data is stored in password-encrypted Excel files for a quarterly analytics project. The Premium Excel Connection Manager ensures secure access, and the Premium Excel Source Component efficiently extracts relevant data using configuration options for precise control. The SSIS Premium Excel Destination Component facilitates smooth data writing back to Excel, offering a user-friendly configuration with detailed mapping and robust error-handling options. This enhances the overall ETL process for managing financial analytics efficiently.
Premium Lookup Components
The Premium Lookup Component, a vital element in SSIS data flow, empowers users to efficiently search and match rows within a lookup table based on fields from a primary input. Its adaptability is evident in supporting both exact and fuzzy matches, providing a comprehensive solution for diverse data lookup scenarios. The component uses two inputs: the Lookup Table Input, containing data for the search, and the Primary Input, supplying rows for the lookup. The outputs are categorized into Unmatched Rows, representing primary input rows with no match, and Matched Rows, showcasing columns from both the primary input and the lookup table.
The configuration of the Premium Lookup Component involves four key pages. The General page serves as the hub for specifying fields to compare, selecting lookup types, and configuring advanced settings. The Comparison Settings page delves into the nuances of match types, including exact match and various fuzzy match options, allowing users to fine-tune similarity thresholds and other advanced comparison settings. The Columns page offers flexibility in defining the output structure, allowing users to toggle columns for Matched Rows and Unmatched Rows, and introducing calculated columns for metrics such as average similarity score, richness score, richness rank, and confidence score.
Additionally, the Error Handling page equips users with options to dictate how errors should be managed during the lookup process. To enhance performance, users can leverage exact match filters for efficiency, strategically set similarity thresholds for optimal matches, and adjust the MatchingPerformance setting to strike a balance between quality and speed, especially in scenarios involving fuzzy matching.
Consider a multinational HR data consolidation project, the Premium Lookup Component in SSIS efficiently matches employee records across subsidiaries using unique identifiers like employee IDs. It supports fuzzy matching on names and job titles, ensuring data accuracy. The Matched Rows output consolidates enriched employee data, while Unmatched Rows highlight discrepancies or introduce new records, creating a unified and accurate representation of the workforce.
Premium OData Component
The OData Connection Manager in SSIS facilitates the integration of OData services into your Visual Studio project. To initiate this connection, right-click the Connection Manager area, opt for "New Connection..." in the context menu, and select "OData (KingswaySoft)" in the ensuing "Add SSIS Connection Manager" window. The OData Connection Manager, essentially an HTTP connection with OData-specific features, streamlines data querying over the HTTP protocol. This proves advantageous when dealing with intricate APIs, offering a standardized approach to data retrieval.
The Premium OData Source Component offers flexibility and precision in retrieving OData service data, enhancing integration capabilities within SSIS workflows and simplifing data extraction from OData endpoints. It offers diverse options, including a Query Builder, Order By Builder, Batch Size setting, and expand queries. It also supports invoking unbound and bound functions, navigating related entities, and provides dynamic displays of Target Entity, Binding Path, and Target URL. The Preview button allows users to inspect HTTP requests based on configurations. The Columns page streamlines metadata presentation, facilitating efficient column selection and mapping.
The Premium OData Destination Component in SSIS facilitates writing to OData endpoints through its configurable General, Columns, and Error Handling pages. The General page enables users to set up connection managers, choose binding entities, specify actions, and configure batch size, among other options. Additional bindings, navigation properties, and multipart headers can be managed, offering flexibility in data processing. The Columns page streamlines metadata presentation for efficient field mapping, while the Error Handling page provides options for managing errors, including redirection to error outputs and detailed error information. This component enhances the integration process by supporting various OData actions, providing dynamic configuration options, and offering detailed error handling capabilities.
For example, a retail business can use the Premium OData Components for streamlined inventory management. The Premium OData Source extracts specific product data, ensuring real-time accuracy, while the Premium OData Destination seamlessly updates inventory details. This integration enhances operational efficiency and maintains data accuracy in the OData-enabled system.
Premium PDF Source Component
The Premium PDF Component serves as a source for extracting data from tables within PDF files. It provides two configurable pages: General and Columns. On the General page, users can set source file settings, including specifying the connection manager (with various supported types such as local file, FTPS, SFTP, etc.), defining the source file path, and providing a password if the PDF file is encrypted. Additionally, users can configure table detection settings, such as the strategy for combining tables across multiple pages, handling empty rows, and aligning misaligned cells. The page also allows users to specify the table to extract data from and set parameters like column header row index, data start row index, and maximum number of rows.
The Columns page displays attributes from the specified source on the General page. Users can select or deselect fields using checkboxes and customize attributes such as column name, data type, length, and header name. The page provides options to add or remove fields, as well as rearrange their positions within the file. The Preview button at the bottom allows users to preview the detected table based on the configured settings. The Preview page includes options to further configure table combinations, skip empty rows, handle misaligned cells, and select multi-line date columns.
For example, a financial services firm significantly enhances efficiency by utilizing the Premium PDF Component to automate the extraction of vital information from monthly client statements in PDF format. With the ability to configure the connection, file paths, and encryption settings seamlessly through the General page, and the flexibility to select and customize essential financial metrics on the Columns page, the firm streamlines the extraction process. This automation not only saves time but also ensures accuracy, allowing the company to swiftly analyze client data for reporting purposes and make informed financial decisions.
Premium Recordset Source Component
The Premium Recordset Source Component facilitates data extraction from record sets within object variables. Configuration involves two key pages: General and Columns. On the General page, users specify the object variable containing the record set and choose to get values by either Name or Index. The Expression fx button enables dynamic updates of properties at runtime. The Columns page displays available attributes from the specified source, allowing users to select fields for retrieval. The grid includes details such as Column Name, Data Type, and a Properties window for additional configurations and fine-tuning of individual field attributes, including CodePage, Data type, Length, Precision, and Scale. Users can efficiently manage field selection, addition, removal, and reordering within the Recordset Source variable.
The Premium Recordset Source Component could be used to dynamically extract and integrate customer feedback data stored in object variables. The General page is configured to specify the source variable containing the feedback record set and retrieve values by Name for efficient referencing. On the Columns page, the SSIS developer selectively chooses attributes such as feedback text, rating, and date for extraction, fine-tuning the integration process. This setup enables the company to flexibly manage diverse feedback data structures and formats, streamlining the extraction and integration of valuable insights from customer interactions across various channels.
For instance, imagine a dynamic system where customer feedback data is stored in object variables with varying structures. The Premium Recordset Source Component allows you to selectively extract relevant attributes like feedback text, rating, and date, offering flexibility and adaptability in integrating insights from diverse customer interactions across different channels.
Premium Service Lookup Component
The Premium Service Lookup Component is a versatile data flow pipeline tool designed for searching rows in a lookup object based on fields from the primary input. It supports various lookup connections, including Dynamics 365 CDS/CRM, Dynamics 365 BC/NAV, Hubspot, Netsuite, ADO.NET connections, OData connections, and REST connections. In this discussion, we'll focus on the REST service lookup, but other supported services can be explored in their respective Help Manual pages.
The configuration of the Premium Service Lookup Component involves four key pages: General, Lookup Conditions, Output Columns, and Error Handling. The General page allows users to set up the connection manager, choose a target object, and define cache mode and advanced text matching options. For REST services, additional settings such as REST Query Language, Endpoint selection, Batch Size, and Output Time Zone are available. The Lookup Conditions page provides a query builder for specifying conditions, either through the Condition Builder Mode or Custom Query Mode. The Output Columns page allows users to configure the output columns, including adding all columns, removing them, and enabling default values. Finally, the Error Handling page provides options to handle errors, such as failing on error, redirecting rows to error output, or ignoring errors.
The Premium Service Lookup Component streamlines the process of searching and retrieving data from various sources, offering a comprehensive set of configuration options to tailor the lookup process according to specific needs, whether dealing with Dynamics 365, Hubspot, Netsuite, or other supported services. The component's flexibility extends to error handling, providing users with choices on how to manage errors during the data flow process.
Premium Slowly Changing Dimension Component
The Premium Slowly Changing Dimension Component is a powerful tool designed for monitoring and managing changes in dimension fields that evolve slowly. It addresses three main types of slowly changing dimensions: Fixed Attributes, Changing Attributes, and Historical Attributes. To utilize this component, users provide two inputs - a Current Dimension Input containing data to be monitored for changes and a New Source Input with potentially modified data. The General page allows for the configuration of essential settings, including business keys, column types, and comparisons, providing a foundation for the subsequent processing stages.
The Change Type Settings page builds upon the General configuration by enabling users to specify settings based on the selected change types, while the Advanced Settings page provides additional controls for fine-tuning the behavior of the Premium Slowly Changing Dimension Component. For instance, users can set the culture identifier for string comparisons, manage surrogate key settings, and control string comparison rules. Additionally, the Writing page, introduced in version 7.1, offers the option to specify an ADO.NET connection manager for direct writing to the destination table, streamlining the ETL process. The final touch is the Error Handling page, which allows users to define how errors should be handled during the execution of the component.
For example, imagine a scenario where a retail company receives updates from various suppliers regarding changes to product details, such as price, description, and availability. By employing the Premium Slowly Changing Dimension component, the company can efficiently process these changes, ensuring the dimension table is updated accurately while keeping a historical record for auditing purposes. The component's advanced features, such as surrogate key management and direct writing to the destination table, enhance its versatility in handling scenarios where maintaining accurate and up-to-date information is crucial.
More Productivity and Connectivity ETL Tools
KingswaySoft provides powerful and sophisticated SQL-server-based data integration solutions and productivity tools capable of handling the most complex and demanding integration challenges. Whether you're dealing with data spread across databases, cloud data warehouses, file servers, or various other sources, KingswaySoft's flexible and feature-rich tools empower organizations of all sizes to seamlessly and efficiently unite their diverse datasets. In addition to these Premium Data Flow Components, developers can leverage a wide array of SSIS components with advanced capabilities such as data transformation, data cleansing, encryption, automation, value mapping, big data integration, and much more, making it easy to transform and normalize data as it is being integrated.
Summary
In conclusion, KingswaySoft's Data Flow Components serve as indispensable assets within SSIS, offering advanced features and enhanced capabilities for efficient data integration. From the versatile Premium Aggregate, Conversion, and Derived Column components providing extensive transformation functionalities and error-handling options to the powerful ADO.NET Source and Destination components facilitating seamless interaction with various data providers, these tools empower users with flexibility and control in crafting sophisticated data workflows. Whether handling aggregations, data type conversions, or intricate column derivations, KingswaySoft's Premium Data Flow Components contribute to an elevated and streamlined experience in managing complex integration scenarios within SQL Server Integration Services.
Thousands of enterprise clients from over 100 countries rely on our no-code SSIS Integration solutions to integrate data with various application systems to drive business efficiency and leverage information assets.
To read more about our SSIS data integration solutions click here.
To return to the Industry Analysis Index Page, click here. To return to the Resources Index Page, click here.
About KingswaySoft
KingswaySoft is a leading integration solution provider that offers sophisticated software solutions that make data integration simple and affordable. We have an extreme passion for our software quality and an intense commitment to our client's success. Our development process has always been customer-focused, we have been working very closely with our customers to deliver what benefits them the most. We have also made sure that our support services are always highly responsive so that our customers receive maximum benefit from the use of our products.
Learn more at www.kingswaysoft.com