BizTalk vs. SSIS: Which tool makes sense for deep integration efforts?

Integrating disparate systems is always a challenge. Scott Lowe provides an overview of the data state at Westminster College and asks the TechRepublic community for feedback.

I'm a man on a mission.  For years, there has been a need at Westminster College to integrate information between disparate databases.  Over the past few months, the need to satisfy this demand has risen very, very quickly as various data-related operational challenges have raised their ugly heads.  Further, as an institution, we've identified database integration and business process reviews/improvements as a priority.  As is the case in many places, overall priorities can be moving targets, though, and we've been addressing some other issues and are now refocusing on these data challenges for the second half of the school year.

We already have quite a bit of automated database integration taking place.  Here are some examples:

  • We have developed in-house scripts that perform one-way synchronization of account information from our ERP to our course management system.
  • We use a tool called AD Bulk Users to automate the creation of student Active Directory accounts and Exchange mailboxes based on information from our ERP. This process also includes scripting that creates home directories for new students.
  • Information from our ERP updates user information fields in Active Directory which, in turn, updates our faculty and staff directory that's housed in our public-facing SharePoint web site.
  • Various processes that move student and staff photos from the campus ID card system to other systems, such as our student information lookup tool for security, our course management system and more.

Although we've made a start at automating some common tasks, in my mind, we're not yet moving in the right direction and have a lot of work ahead of us.  Most problematic, in my opinion, is that we have yet to implement a consistent, standard system for automation/database synchronization meaning that every integration process has been a reinvention.  Second, we have dozens of major integration challenges ahead of us.  Although we have an ERP, for a variety of reasons, not every department on campus uses the ERP.  For example, our Institutional Advancement/fundraising office uses a third party system.  Currently, we do an annual batch load and then hope and pray that data stays consistent between the ERP and the fundraising system.  As you might imagine, "hope is not a strategy" so our current methods leave a lot to be desired.  In an ideal world, if we're going to need to support systems outside the ERP, I'd like integration so tight that one can't tell where the ERP ends and the third party system begins.  That said, I don't want to undertake what will be a major integration project using the same tools and processes that we have in the past.  In other words, I don't want to invent an integration system for this single challenge; I'd rather build an integration system that is common that we can extend to any other services that we might ultimately bring to campus.  Over time, having a few totally separate integration methods is ok, but as we grow our data systems, standardization will be increasingly important.

And that's where I am right now - how do we standardize our integration processes?  Obviously, the end result is the appearance of a single system.

We're looking at a number of options, but are focusing our efforts on SQL Server Integration Services (SSIS) and BizTalk.  We're very early on in our evaluation, but my understanding so far is that SSIS is ideal for batch-type extract, transform, load (ETL) processes where BizTalk is more ideal for more granular work.  Obviously, it's entirely possible that I'm incorrect in this assessment and I invite constructive feedback in the comments section if this is the case.  I'm fine with either choice as long as it meets the needs.  I expect that BizTalk will provide us with more capability than SSIS, but we may not need the additional complexity.  Our integration efforts will require a lot of integration at the SQL level, but also the use of a few 3rdparty APIs.

At the onset of our integration efforts, we'll be looking at a significant batch load between systems but once that is done, overall synchronization will happen at regular intervals and involve only that information that has changed since the last interval.

I'll report back on progress as it continues.

In the meantime, I'd like to ask the TechRepublic community for its thoughts on this type of challenge.  What tools do you use to provide deep integration between systems?