Communication between two apps, is SSIS the way to go?

Question

Hi, working with a team of more traditional developers we came across this situation:

We have a growing number (two right now) of apps that will be accessing some common data inserted via the ui of one of the apps, which could be called the main administrative app. Since the other apps just need some of the data or needed formatted with a different schema, one of the solutions brought forward is to have a database per app and a sync job running with a certain frequency that updates the data from one db to the another. Being this a common SOA scenario I was too quick to discard this solution in favor of a service oriented one where the data is stored in one main repository and accessed via exposed services.

So, I'd like to read your thoughts as I think I could be biased in favor of relative new technologies and might not be appreciating the value in more traditional solutions.

Answer 1

My advice when choosing a technology is to really weigh the pro's and con's. "New" vs "Old" can get you in traps all of the time. There's always going to be a hot new buzzword for something or other out there at any given time and it may or may not be a good way to go. So let's analyze:

Pro's of an SSIS package vs. a Service:

• You can transform your data into your destination databases to conform to your referential integrity relationships.
• It's easy to access and modify your existing applications to access this data.
• If you have a small amount of data to transform, this job will run quickly.
• The SSIS package is a nice, centralized place to debug data transformation problems.
• Security can be easier by transforming the data with SSIS. You don't have to worry about potential issues such as Kerberos delegation between service to service.
• Accessing the data itself at the destinations will be much faster because you don't have to make another service request. You can just join to the data already in your database.

Con's of an SSIS package vs. a Service:

• You don't have real-time access to your source data at your destinations.
• If there's a large amount of data, or if this package is poorly written, it can take a long time.
• If you have a rapidly growing number of destination databases, you will have to modify the SSIS package often.
• You can set up your own security mechanism with the service, such that you can control and audit access to its information.
• If your destination systems need to write back to a central source, merging all of this back together with SSIS can be a real pain.

So I boil it down to this:

If you're writing back to the centralized source, or you can't have any significant latency between updates, a service makes a lot of sense. Otherwise, favor copying the data with SSIS. At my place, we use SSIS to transform our user data and import it into our systems' databases, but we use services to implement a organization-wide logging infrastructure.