Problem

What tool is available to assist with the development and design of SSIS packages
in the most efficient and effective way?

Solution

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.

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.

Dataflow Components

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 KingswaySoftcomponent 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.

Cloud Storage

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.

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.