By: Scott Murray | Last Updated: 2017-05-03 | Comments (1) | Integration Services Development
What tool is available to assist with the development and design of SSIS packages in the most efficient and effective way?
SSIS is a wonderful ETL tool that provides fairly wide, but basic functionality for moving data from one place to another. However, when you start looking into several specific and detailed tasks, SSIS and SSIS developers can use some help. KingswaySoft comes to the rescue by providing its SSIS Productivity Pack. This product definitely packs a huge efficiency punch by making the process of setting up and maintaining your work and data flows much easier work through. Since the SSIS Productivity Pack runs seamless within SQL Server Development Tools (SSDT), the learning curve is short and usability is extensive.
Using the KingswaySoft SSIS Productivity Pack
The KingswaySoft SSIS Productivity Pack is broken down into several component areas:
- Dataflow Components
- Cloud Storage
- Data Generation and Anonymization
- Data Quality and Comparison
- Encryption and Compression Tasks
- Mail Components
- Message Queue
- Template Merge
- Premium File Pack
- HTTP/Web Service Components
- Miscellaneous Components
To help you understand how effective these tools can be for your day-to-day SSIS develop tasks, let us go through several of the main components to show off their value.
The Dataflow Components are most effective in dealing with, ADO source & destinations, slowly changing dimensions, and derived columns. After you complete the installation, you will find all KingswaySoft SSIS Productivity Pack components already in your SSIS toolbox.
Let us start out by adding the Premium ADO.Net Source; with this component, you can exercise additional settings that are not available in regular the SSIS toolset and connection manager. For instance, the ADO.NET source component supports several data providers including OLEDB, ODBC, Oracle, ODP.Net and MySQL.
Switching over to the ADO Premium Destination, this component not only allows for inserts, but also provides, natively, the ability to Update, Delete, and Upsert into your selected destination. The latter three items certainly create an efficiency by not requiring you to hand code the related SQL to complete those tasks. These two components, along with the rest of the SSIS Productivity Pack Components, all provide easy to follow GUI.
Next up is the Premium Deriver Column component where you will quickly find over 200 extended functions are available. Of course, to get to this point, you need to add a dataflow to a SSIS package.
The Premium Derived Column tool actually contains a validate and test feature within the Expression Editor to verify the syntax and requirements for the requested function. Validate checks that you have the syntax and proper arguments and Test actually allows you to see the results of the derivation. The sheer number of functions available in the Premium Derived Column along with their ease of use will assist about any developer during SSIS package design.
Rounding out the dataflow components is Slowly Changing Dimension (SCD) component; using the native SCD component requires a significant effort to setup and maintain. The SCD KingswaySoft component eases that burden, by providing an intuitive GUI interface, which delivers the ability to map each item to a new source field. Furthermore you have the flexibility to easy mark or set the slowly changing dimension type (how new and updated lookup values are handled by the component). Each lookup value can be handled in different ways, and the SCD component is able to guide your selection for each of type SCD. Finally, just like all the other components, an Error Handling option is available which instructs the SCD task on how to handle the results ( for instance stop processing or send to a text file ) when a particular error occurs. These options include failing the package or redirecting the rows to an alternate row.
As more and more documents and data sources are housed in the cloud, it is now increasingly and critically important to be able to directly connect and source from cloud based resources. For these sources of data and destinations for data, the cloud storage options provide a streamlined method for connecting to a Cloud based file share such as OneDrive, DropBox, or Azure. Having the Cloud Storage connections provides the SSIS developer with the ability to link to a file in cloud without having to develop an API or Script to manually download the file(s) in question.
Data Generation and Anonymization
Moving on to the Anonymizer component, this function provides the quick ability to mask personally identifiable or sensitive data within a SSIS package. The package provides a simple way to select the columns to be masked and the specific columns to be ignored. Of course, this functionality makes creating masked test tables, based on production tables, quick and easy.
Furthermore, the Anonymizer component allows, in the advanced editor, to adjust the seed value. The seed value dictates if the same value should be used to generate the same data every time the Anonymizer component is employed.
Data Quality and Comparison
Often as you deal with various data sources, problems arise with the integrity and cleanliness of the data; you will frequently need to remove duplicates from a data set that is being loaded. Furthermore, you may need to compare your existing tables and data with a newly loaded data set. The SSIS Productivity Pack contains two components to address both of these issues.
First, the Duplicate Detector does just what its title says; it identifies rows within a single table that are duplicates. Furthermore, the detection can be either an exact match, a fuzzy match, or even specific address or name type matches.
To further enhance the process, you can select to ignore white space, case, or punctuation; all these properties are handy for cleaning your data quickly and with ease. Finally, the output from this component allows either the unique rows or the duplicate rows to flow to your destination task.
In a similar fashion, the Diff Detector allows you to make similar comparisons, but this time between two tables. The component allows you to select which columns to check for matching values while also giving you the ability to set similarity scores and whether to ignore white spaces and upper / lower case differences. Finally, the component allows you to decide if the added, changed, deleted, or unchanged rows should be included in the data flow to the destination.
Data Pack and Unpack
Data sources today come in variety of formats, but XML and JSON formats are very common especially export type data files are available on the web. Certainly, importing and exporting to these file types can get tedious and tricky. However, the SSIS Productivity Pack provides a simple and intuitive interface for both exporting and importing from / to XML and JSON. Both the XML and JSON components work in a similar fashion when using the merge and the extract process. For the Merge, you can merely select the source tables (data sets) you would like to merge into a single XML file.
The next step in the process is to define the relationship between the tables.
Then the XML Column structure is defined including selection of which columns to be included in the merge.
Finally, the XML properties are selected for the file and then you are ready to select your destination XML file.
An XML Extract works in a similar methodology, but in the opposite direction. An XML source is set as the input for the XML Merge component.
The component handles the initial parsing of the XML into the appropriate table break outs based on the XML path.
Next, the component allows you to define the exact tables and columns to be extracted from the XML source. You will notice from the initial Extract screen that the tables defined on this properties page are the data flows, which will be generated from the extract. So for instance, if five tables are defined from the extract, then five output data flows, one for each table, will be made available for the data destination.
As you can see, all of XML processing is available out of the box and is easily implemented for both out flow extracts and in flow merging of data. Furthermore, the JSON process works in a very similar manner yet adheres to the formatting and tagging syntax used by JSON.
Web Services / HTTP
Similar to reading and parsing an XML, the Web Service and HTTP components supply streamlined methods for either reading or writing to HTTP based on SOAP based sites as well as support REST. The HTTP components consist of a connection manager, a requestor, and a task while the Web Service components include a source, a destination, and a task component. Using the HTTP components streamlines the process of making requests to a HTTP page in order to request data.
With the Web Services components, SOAP based end points can be read and used as source for a data flow, while data can also be easily written to a SOAP endpoint from the Web Service Destination component. Additionally, a data flow task is available to process data all within a control flow. Using this component certainly provides a way to complete Web based data requests without having to write multiple scripts to get or put the data from a site.
Encryption and Compression Tasks
Compressing and encrypting data in a SSIS can be achieved all within the Compression and PGP Encryption tasks. Of course, you could use a third party tool, but completing these tasks as part of your package allows the entire process to be contained within a package and without the need for outside scripts or tools. With just a few clicks you can effortlessly un-compress, compress, decrypt, or encrypt a file all within a package.
SFTP / FTPS Connection Manager
There are certainly times when you need to retrieve data from behind a secure FTP site. The SFTP and FTPS components both deliver a connection method to retrieve files on a secure FTP site. Once the connection is setup connection manager, this connection can be effortlessly used as the source connection within a flat file data flow.
Within the KingswaySoft SSIS Productivity Pack, many other components exist which can be used to assist use in your everyday SSIS package development. The tools described throughout this tip not only add efficiency to your package development, but they also ease the burden of having to manually script or use external tools and / or command tasks to complete a particular SSIS development requirement.
How do I get started with the KingswaySoft SSIS Productivity Pack?
- Check out the free resources available for the SSIS Productivity Pack.
- Download the SSIS Productivity Pack to see how it can help you.
- Think about all of the challenges you face with SSIS and how the KingswaySoft SSIS Productivity Pack can solve these problems.
- Communicate with your team and management about how you think the SSIS Productivity
Pack will help.
- Fully integrated into the Visual Studio, SQL Server Data Tools and SQL Server Management Studio.
- Time savings with numerous components that can be easily added into new and existing projects.
- Significant functionality beyond the native SSIS toolset minimizing unnecessary coding.
- Easily access cloud based resources without having to write to an API or custom code.
- Seamless security to properly protect your data.
- Put the KingswaySoft SSIS Productivity Pack through its paces in your environment, share the results with your team and determine your next steps.
- Download the KingswaySoft SSIS Productivity Pack from: http://www.kingswaysoft.com/products/ssis-productivity-pack and try it out for yourself.
Last Updated: 2017-05-03
About the author
View all my tips