SSIS Or Log Shipping?

SSIS or Log Shipping? A New Analysis

SSIS Or Log Shipping?

SSIS or Log Shipping?

Brian Moran (@briancmoran) and I were meeting with a Linchpin People (@LinchpinPeople) client recently. They had a common complaint. “Our Production system performance is being impacted by reporting queries,” they told us. The client had done their research and concluded a data warehouse is the long-term solution. We agreed.

“But what can we do right now?” they asked. As we talked through possible short-term options, it became clear that Brian and I were advocating different solutions. We were doing so while considering the same constraint – time. Brian was advocating Log Shipping. I was advocating an SSIS solution. But we were both thinking, “How do we get this client out of hot water as soon as possible?”

Considerations and Constraints…

Log shipping can be set up quickly. It allows us to set up a second “warm” (minimally latent) copy of the data that the reporting folks can utilize for a source. There are several other facets to consider but our primary thought was time, and so log shipping made sense.

“What about SSIS?” I thought. We could use SSIS to maintain a warm copy of the data. Further, utilizing the incremental load pattern we could load vital tables every five minutes. The reporting database fed via incremental loaders would also make an excellent staging database for the client’s eventual enterprise data warehouse.

Traditionally it takes roughly two hours per to write (and test) an incremental load SSIS package. I like to build one SSIS package per table. If the client has 50 tables, I’m quoting 200 hours for SSIS development. We can set up and test log shipping in a matter of days. When time is your top concern, log shipping wins over traditional SSIS hands down.

But we’re not doing traditional SSIS.

SSIS and Business Intelligence Markup Language (Biml)


Linchpin People’s Data Integration Group uses Mist from Varigence to compose Business Intelligence Markup Language (Biml). Our solution – originally developed by Varigence as a demo – allows us to condense hundreds of hours of development into a few days of work. As Brian and I talked with our customer we realized we could deliver either log shipping or an SSIS solution in the same amount of time.

With the time difference off the table, we could compare the advantages and disadvantages of each solution. Our customer agreed with us – options are good!


Little Data Remains Important in Healthcare IT

SameAsItEverWasIn his article Healthcare’s Big Problem With Little Data, author Dan Munro raises salient points about the state of health-related data. Electronic Health Records (EHR) were promoted as the end-all-be-all solution for the industry – a standardization that, I suppose, many thought would organically and naturally occur, stabilize, and be maintained.

It hasn’t. At least not yet.

My doctor and I speak about this almost each time I visit with him. The corporation that operates his practice nowadays seems endlessly locked in cycles of changing billing and EHR systems in search of low-cost compliance and integration. They’ve (literally) spent millions of dollars and my doctor hates the interfaces forced upon him and his patients (well, one, at least) hates the complexity of the billing and patient records systems. Can’t these systems all just get along?

The result? Higher medical data management costs. I’ll give you one guesses who pays these costs.

Munro posits the following from his article:

By at least one estimate (here) there are now about 500 independent EHR vendors.  Out of that large group is a subset of about 400 with at least one customer that has applied for Federal stimulus dollars through the labyrinthine process of meaningful use attestation. That would suggest a “first-cut” of about 100 vendors who made some commitment around certification – but have no reported customers (at least to date). That’s a staggering number of single-purpose software vendors for any industry to support – even bloated healthcare. The simple fact is it can’t. While there have been a few high-profile cases of EHR vendors shutting down, this last week was the first high-profile example of a vendor that was effectively decertified by the Feds for both their “ambulatory” and their “inpatient” EHR products. From the website last Thursday:

“We and our certification bodies take complaints and our follow-up seriously. By revoking the certification of these EHR products, we are making sure that certified electronic health record products meet the requirements to protect patients and providers,” said Dr. Mostashari.“Because EHRMagic was unable to show that their EHR products met ONC’s certification requirements, their EHRs will no longer be certified under the ONC HIT Certification Program.”

You may ask yourself, well, how did we get here? This, folks, is a mess. What’s missing? Applied standards.

“But Andy, you’ve told us standards slow down development!”

And I stand by that statement; standards do slow down development…unless you’re building interfaces. And then standards become the means for decoupled snippets, functions, methods, applications, and even platforms to communicate with each other. In some cases, we simply cannot be productive without standards – like TCP/IP. What would happen if everyone coded their own version of internet traffic? If that was the case, very few of you would reading this post.

Yes, standards slow things down. And yes, they are necessary to insure base functionality. In my humble opinion, we have to get this right with healthcare data. We simply must. While we see similar issues of data management across many fields, medical data is too important to mess around with; it’s (often literally) life and death. And it is certainly a high cost.

More to Consider

Standards exist. Administering and certifying 400-500 vendor solutions is hard.

Part of the Solution

From the actions of the Department of Health and Human Services last week, one can ascertain HHS is taking steps to address the matter. But will all 400-500 companies voluntarily congeal their schemas? Possibly, but doubtful.

My experience delivering US state Medicaid ETL solutions informs me there will be a need for data integration – regardless of the existence of standards and in spite of certification. Why? Standards are not static. The idea of de facto standards emerges from the life cycle of software because software is organic. Even if everyone agreed on the same interpretation of rigid standards (and they won’t), versions 2.0 through n.n will – at a minimum – add fields to the schema. And with additional fields comes additional data.

Standards will be revised when enough product schemas adopt the de facto, and this will drive the need for yet more integration. Don’t take my word for it, examine the entropic history of ICD-9 and ICD-10 codes – the direction of progress is more data, not less.

Learn More

This is one reason we at Linchpin People are focusing on Medical Data Integration. The recording of our first (free!) webinar about Medical Data Integration with SSIS 2012 is available here. Kent Bradshaw and I continue the series tomorrow presenting Medical Data Integration with SSIS 2012, Part 2 in which we focus on loading Provider and Drug data.

I hope to see you there!


From Zero To SSIS Training: 4-8 Mar 2013 in Reston Virginia

Want to learn more about SSIS? I  can help. In my course - From Zero To SSIS – I teach you how to effectively use SSIS to deliver data integration solutions.

This is not a market-y survey of the bells and whistles contained in SSIS. I focus on the tasks data integration and ETL developers will use to produce SSIS packages in the enterprise.

How do I know which tasks are used most?

SSIS Design Patterns, Apress, 2012

I have been using SSIS since the early beta versions. For over two years, I managed a team of ETL developers at Unisys as we built Medicaid solutions for two state governments and maintained the Medicaid solution for another state. When we needed more people, I had a hard time finding experienced SSIS developers. I needed a way to bring less-experienced developers up to speed quickly. I found a way to train data integration developers to use SSIS to build enterprise-ready, metadata-framework-driven SSIS applications in less than one week. The experience served as the basis for this course which has been delivered publicly and privately to hundreds of students over the past two years.

You can learn more about – and register for – the course here.

Advanced registration is available until 1 Feb 2013: $2,749 USD

Registration after 1 Feb 2013: $2,999 USD

I hope to see you there!


Using Enterprise Data Integration Dashboards

In this video session, Kent Bradshaw and Andy Leonard demonstrate and discuss Enterprise Data Integration Dashboards designed to capture runtime metrics from an SSIS Framework.

Topics include:

  • Data Integration Architecture
  • SSIS Development
  • Operational Intelligence
  • SSIS Design Patterns
  • Data Integration Performance
  • SSIS Frameworks
  • SSIS Performance Historical Analysis
Watch and learn more about using data integration dashboards to improve visibility into the status of data integration in your enterprise:

Creating SSIS Packages with the SQL Server Import and Export Wizard

In this post, I demonstrate how to use the SQL Server 2012 Import and Export Wizard to create a SQL Server 2012 Intgertaiion Services (SSIS 2012) package. The demo is created on a virtual machine running Windows Server 2012.

You can use the SQL Server Import and Export Wizard to learn more about SQL Server Integration Services. Often, one will reach the “Save and Run Package” just before executing the Wizard. The “Run immediately” checkbox is checked and the “Save SSIS Package” checkbox is unchecked by default. If you check the “Save SSIS Package” checkbox, you can select to store the SSIS package in the MSDB database (the “SQL Server” option) or File system. You may also select the Package Protection Level:

If you select these options, the next step is selecting the target location of the package:

The next page of the Import and Export Wizard displays a summary of the selected options:

Clicking the Finish button creates, saves, and executes the SSIS package as configured, and the results are displayed:

Cool. Now what?

Now you have an SSIS package saved in the file system. In this case, the file is named ImportExportWizard.dtsx. It can be opened in SQL Server Data Tools by right-clicking the file and selecting “Edit”:

Windows Server 2012 prompts for which program to use to perform the Edit. I select Microsoft Visual Studio Version Selector:

Sql Server 2012 Data Tools uses the Visual Studio 2010 Integration Development Environment (IDE) at the time of this writing. Note the “Run” (Play) button is disabled on the toolbar:

We have an SSIS package created with the Import and Export Wizard, but we cannot re-execute it.

Bummer. But all is not lost.

Visual Studio needs a debugger to execute packages in debug mode. When we open an SSIS (*.dtsx) file all by itself, Visual Studio doesn’t load a debugger. To have Visual Studio load the SSIS debugger, we can create a Solution containing a Project. Begin by clicking File, New, Project:

Name the new solution and project, and select a folder:

Once the solution and project are created, right-click the SSIS Packages virtual folder in Solution Explorer, and select “Add Existing Package”:

Navigate to the location where you stored the SSIS package in the final steps of the Import and Export Wizard:

When you click the OK button, the SSIS package is copied into the project folder and added to the new project:

When you open the SSIS package in the context of a Visual Studio project and solution, the Debug button is enabled on the toolbar and the SSIS package can be executed in debug mode:

You can use your knowledge of the Import and Export Wizard to learn more about SSIS package development. Happy Integrating!