Microsoft Software and Solutions

Sql Server Integration Services (SSIS) 2016 Review

SSIS 2019 - the Good the Bad, and the Ugly

SSIS is a unique ETL tool for a number of reasons. Most shops already own it but may treat it as a free utility rather than as an enterprise ETL tool.  Which is ironic given it is superior to many of the supposed enterprise-class tools in a number of areas. The lack of respect in our industry is likely due to immature beginnings in its former "DTS" days and because it comes bundled "free" with Sql Server.  But don't let that fool you into thinking it can't compete with the expensive ETL tools. I've used many ETL tools over the years and I'll take SSIS over any of them. I've now used SSIS exclusively for close to a decade and have no regrets. 

SSIS is extremely flexible, extensible and integrated with many other Microsoft tools and a multitude of add-ons - both for purchase and for free. It's come a very long ways since its DTS days.  It's incredibly cost effective, easy to learn the basics quickly (although like all ETL tools requires the traditional learning curve to get good at) and has an immense user base. There are also endless bits of quality shareware available that seamlessly plug-in and a wide variety of low priced vendor supported add-on products to fill in any out-of-the-box gaps (see my other review of MellissaData Data Quality MatchUp for SSIS). And if you can't find something you need and you know how to code C#.net you can extend the tool yourself. So unlike any other tool on the market, there's always a way to make something work with SSIS.

The Good...very good

• How is free for a price? (for anyone running enterprise or BI editions of SqlServer)
• Limitless Extensibility

This comes from the fact that SSIS is merely Visual Studio code and comes with templates to add your own custom components. A large variety of pre-built shareware is available at the codeplex.com website and vendors such as MellisaData and Pragmatic Works provide sophisticated add-on components from advanced realtime monitoring to state-of-the-art data quality plug-ins. BIML shareware allows for automated ETL code generation based on XML templates. Some of the shareware available on Codeplex are very high value such as the MultipleHash component providing very sophisticated hashing to support CDC and SCD operations. If you don't like out of the box functionality (such as the SCD wizard which is largely worthless) then there's likely a worthwhile supported replacement by some vendor for a reasonable price. There's built in support for CDC from many database sources such as Sql Server and Oracle for no added charge which is unheard of among their competitors.

Adding plug-ins such as BiXpress, Task Factory and DocXpress from Pragmatic Works are relatively inexpensive tools that do some really amazing things. BiXpress is a MUST for providing real-time and historical monitoring of ETL including tracking package parameter and local variable value changes both in real-time and historical. I highly recommend MelissaData Matchup for SSIS - you'll never see any other data quality tool as easy to use as that one which seamlessly integrates with SSIS.

• Common Development Environment - Visual Studio

Unlike any other ETL tool - if you learn Visual Studio, you gain familiarity with an entire toolset. Its navigation and project organization is common to all other .net development. Along with Visual Studio you get all the  source control plug-ins inherent with the tool such as TFS (aka. TFVC in VSO which is cloud based and free!), and Git.

• Job/package Parameters

2012 Version introduced very flexible parameter capability superior to most all other ETL tools. Project and package parameters integrate seamlessly into Sql Agent to provide step-level dynamic change runtime values such as source/target connections. They of course can be used with many other job schedulers albeit a little less tightly integrated.

• Endless Add-ons

Either via shareware or purchased products. Github provides a huge amount of free shareware code - some of which is very high quality. Vendors such as KingswaySoft and PragmaticWorks and many others provide multitude of inexpensive add-ons from adapters to enhanced components to connect to just about everything.  I use SSIS to connect to Microsoft Dynamics CRM, Oracle Netsuite, on-prem Oracle, hosted source using SOAP calls, and Azure SqlServer as well as flat file loads and on-prem SqlServers.

• Logging

Built in reports show package execution and messages.  Logging can also be customized so only what is needed is logged. There is also an excellent logging replacement called BiXpress that provides both historical and real-time monitoring which is more efficient and much more robust than the built-in logging capabilities.  And none of this requires custom coding to make it useful unlike many other ETL tools.

• Extremely Large User Community

Just google if you don't know the answer - from youtube to blogs there's an incredible amount of information out there about this tool. I suspect far more than any other ETL tool.

   List of good features...

1. Respectable Performance and includes a balanced distributor that allows for endless parallelism of data flow pipes
2. Robust historical repository reporting provided in the included SSISDB repository
3. Includes connectivity to large variety of sources/targets
4. Built in CDC for multiple sources (formerly a purchased add-on from Attunity). This feature alone is often 6 figure add-on from other vendors.
5. High value/low cost Data Quality component integrations from MelissaData
6. Sophisticated breakpoint debug capability including inside VB and C# scripts
7. Integration with Change Control Software (e.g. TFS, Subversion, Git)
8. Fully integrated with Sql Server Agent for scheduling including dynamic job step parameters
9. Integrates with SSAS tabular and cubes as well as Data Mining algorithms
10. Includes data profiling task and wizard
11. High level of sophistication with source/target drivers
12. Free Attunity OLEDB drivers for higher performance connections to Oracle and Teradata
13. Multiple plug-ins for interfacing with applications such as Salesforce.com and Dynamics CRM
14. Longevity of the tool and consistent support and enhancements by Microsoft
15. Full power of either VB or C# script tasks to accomplish pretty much anything that isn't already included

The Bad

• No direct support for push-down of joins

You have to write push down join & lookup SQL to the database yourself via joins in the data flow source to get very high performance. That said, this is a common complaint for nearly all ETL tools on the market and those that offer an alternative such as Informatica offer them at a very expensive add-on price and even then don't work for all join situations.  (My best practice is complex joins go into views of the data lake/landing area tables anyways so the queries are easily audible but I know there are deferring opinions here.)  

• Slowly Changing Dimension (SCD) Wizard has poor performance

No surprise here as this is a common issue with ETL SCD wizards. Requires custom/shareware enhanced wizard or one from 3rd party vendor to get good performance. This begs the question why Microsoft can't come up with a better wizard since it's pretty straight forward to code a dynamic merge as a workaround and someone wrote a much better shareware version. Pragmatic Works also sells a much better and supported version in their toolkit along with many other tools.

Here's a list of constraints or potential gotchas...

1. Expression Language primitive and inconsistent with other languages. The workaround is to use the script task that allows either VB or C# but using those inside of a dataflow severely degrades performance.
2. Limited native scalability - Direct support for multiple server/clustered installation/processing requires at least the 2016 version but I haven't yet tested this feature so I'm leaving this in as a constraint for now.
3. Flat file connectors are overly difficult to build and maintain - Changes in columns and file layout is cumbersome and problematic. Datatype detection is almost always wrong requiring manual settings for every column. Flat files that use quoted fields between delimiters don't work if a quote is included in the data - it can't find the field alignment and the data flow errors out. I know of no simple solution for this as it errors in the source before you can apply a cleansing function to it.
4. Default datatypes not always correct when reading from views. This can cause syntax errors in data flows and components such as union all. Workarounds are to explicitly convert in datatype conversion task or override the default metadata datatype.
5. Previous metadata often does not disappear when changing targets/sources. Tasks have the tendency to hold onto previous datatypes and lengths and not automatically pick up the changes.  The tool cannot automatically adapt to metadata changes like some tools such as ADF.
6. Inconsistent data types for variables and parameters. Start with one set of datatypes coming from a database, combine with a completely different set of internal variable datatypes, another set with either Vb or C# variable datatypes when using script tasks, another set of datatypes when passing parameters to stored procedures, and yet another when applying SSIS expressions...and it's quite the mess in the end. You get it figured out eventually but it leaves you scratching your head asking why it has to be so difficult when it's all the same vendor's product. The C# and VB and database datatypes are a given but why can't the others follow one of those?
7. Confusing context/scope for variables to watch when running multiple levels of parent/child execution. Debug mode shows all of them at the same time and the context for each set is not intuitively obvious. For example you get list of each parent and child in the hierarchy of calls and its easy to mistake which package the variables relate to when viewing in the debugger.

10. Logging significantly impacts performance. You can customize logging however starting with 2016 version.

The Ugly

What's the future for SSIS?

Its only cloud capability is running it under the covers within SSIS-IR from ADF. The only decent monitoring tool for SSIS is BiXpress and it has now been deprecated! The writing is on the wall folks. The problem is ADF isn't architected to do a lot of small tasks efficiently like SSIS is. I have no way of testing this but based on my experience an attempt to re-engineer all our SSIS processes into ADF is likely to take our daily 6 hour process and turn it into couple of weeks. ADF just takes to long to move small amounts of data around.  That leaves us limping by with SSIS and ADF in combination until such point someone provides a viable cloud alternative.

SSDT is still 32 bit!

Yup...and you thought this was the year 2022 and everything is 64 bit. Apparently Microsoft doesn't know that yet. Combine that with its tendency to not release memory and its not difficult to hit out of memory errors when doing SSIS development.  But wait!  There is finally hope on the horizon…VS 2022 is 64 bit now but no word on when SSDT/SSIS will be released for 2022.

So...

Here we go with more not-so-pretty "features"...

Development environment and deployment wizard becomes unstable with larger projects

It is not unusual to get "out of memory" errors IF you use the default deployment wizard which is 32 bit on even medium sized projects. However there is a 64 bit version that eliminates this issue but you have to realize that using it isn't the default.

SSDT (the development tool) keeps grabbing more memory as you open new solutions so you have to exit at least once a day to free up memory. SSDT is unstable if you open more than about 30 packages at a time (such as when you're applying framework code to a bunch of packages - you have to limit how many you do at a time).

Containers that help group tasks have several very annoying bugs. For example, sometimes if you attempt to resize the container it will make the diagram tool move about wildly and out of control. A task within a container sometimes becomes detached and you can't get it back into the container.  This is common with sequence containers when you try to add a new task.  The new task seemingly disappears but is actually behind the container.  The workaround is to cut and paste it in but you may start to scream before you figure that out.  

And if that's not enough, here's a very special feature for you to enjoy...If you change the "show annotation" on a precedence constraint when the constraint is using a package parameter, Pennywise the clown slaps you in the face, laughs and then SSDT dies. There is fortunately a workaround.  You can make the constraint something generic like 1==1, change the show annotation again  and then put the real constraint into it. In the end you walk away with satisfaction knowing you found a way to slap it back.  

Prior to 2012 not recommended!

Prior versions had many issues including debugging instability with large parent-child package call volumes and .com locking issues when running many parallel threads. It's largest drawback however was it was WAY overly complicated with its configuration XML file method of passing data between packages. That said, it was still superior to ETL tools that require passing parameters via just files (such as Informatica)! But these issues were resolved with 2012 when they introduced project and package parameters and they also improved memory management for parent/child package calls.

Microsoft SharePoint is a powerful, enterprise intranet platform for creating and deploying intranets – for the enterprise, department or work group. It provides a central, secure place to store, organize, share, and access information from any device. About 90% of the Fortune 1000 use SharePoint in some shape or form.

SharePoint is not for everyone, and has its' limitations, but is the industry leader for enterprise intranets and digital workplace backbones for good reason: it is broad and complex, but easy to use and deploy out of the box with no capital costs (SharePoint Online, used by most is included in Microsoft 365 subscription; although the client-side SharePoint Server does require significant infrastructure investment).

Intelligent Intranet

SharePoint allows you to build an intelligent intranet that keeps employees informed and engaged. It offers a shared space for securely viewing and collaborating on content, connecting with colleagues, and building community within the organization.

Document management allows the user to upload, manage and share documents from within SharePoint, but link and connect with them in other tools within the Microsoft 365 workplace including Teams, Outlook and Delve. Web content management allows publishers to easily create and publish intranet sites, pages, and libraries in seconds using webparts that connect users to additional video, audio and social content.

• Delve – a dashboard like interface using machine learning and artificial intelligence (using Office Graph) to display the most relevant information of interest to you, based on your work, and of those in your network. Delve indexes and analyzes emails, meetings, contacts, social networks, etc., and presents this information as cards.
• Insights – Insights for Office searches, indexes and displays related, relevant information adjacent to content in a document or page, without leaving the document. Insights puts a search box to the Office ribbon, and it allows you to search the web while inside a document; you can also access this feature by right-clicking on a word; right-click on the word SharePoint and selecting Insight will produce a sidebar of what the Bing search engine deems the most relevant information, including Wikipedia definitions. 
• Search - administrators of SharePoint can limit the search to a particular site, to all SharePoint sites, or open the search to search all of the tools within the Microsoft 365 universe including OneDrive and OneNote. Admins can also custom the results experience by adding bookmarks, favorites and acronyms at the top of results pages for certain keywords.
• Viva Engage (formerly Yammer) – integrates micro-blogging with SharePoint content
• Stream – allows users to upload, store, stream and discover videos securely, within SharePoint pages
• Inline Social – enables users to have conversations right inside their documents.
• Mobile – out of the box, the mobile experience is seamless and accessible via the SharePoint app.

Cons

Though cheap to deploy out of the box, over time, the cost per user is expensive. Most organizations will therefore spend more money over time than deploying a less expensive alternative. However, if you're already using Microsoft 365, SharePoint Online is included at no extra cost. 

Web content management is limited, and certainly does not compare to the feature set and functionality of leading web content management platforms. Editing, formatting and marketing/promotional capabilities are extremely limited. The web content management functionality is very easy to use, but caters to the non-technical, elementary online publisher. Document management and records management capabilities are also limited and lag behind industry leaders.

Most organizations use webparts to customize the layout and functionality of SharePoint sites. Pure custom look and feel designs are difficult and expensive to deploy and can break easily. Most experts warn users not to implement custom designs from scratch, but rather use SharePoint's page templates and webparts to achieve more simple look and feel sites and pages. 

Finally, SharePoint sites have a habit of being created easily, and abandoned without little consideration to the impact on the user seeking out relevant information. Wild west sprawling intranets of limited and dubious content proliferate many organizations that do not put in place proper governance controls, which are limited in SharePoint. SharePoint requires proper governance controls on who can publish what and when, according to set standards, and have renewal or expiry dates that flush and delete outdated and cluttering content.

Summary

SharePoint’s intranet capabilities are designed to unite and inform employees, fostering a collaborative and informed workforce. It’s a powerful tool for achieving specific business outcomes and enhancing organizational efficiency. It is relatively cheap and simple to deploy out of the box, and easy to publish, but is expensive over time and has limited content lifecycle capabilities. 

--

Toby Ward is Founder and CEO of Prescient Digital Media, and the author of the first blog on intranets, www.IntranetBlog.com. You can download for free his white paper, SharePoint for Intranets.

What is our primary use case?

How has it helped my organization?

What is most valuable?

What needs improvement?

For how long have I used the solution?

What do I think about the stability of the solution?

What do I think about the scalability of the solution?

How are customer service and support?

How would you rate customer service and support?

Which solution did I use previously and why did I switch?

How was the initial setup?

What's my experience with pricing, setup cost, and licensing?

Which other solutions did I evaluate?

What other advice do I have?

Which deployment model are you using for this solution?

If public cloud, private cloud, or hybrid cloud, which cloud provider do you use?