John Welch works at SentryOne, where he manages the development of a suite of BI products that make developing, managing, and documenting BI solutions easier. John has been working with business intelligence and data warehousing technologies since 2001, with a focus on Microsoft products in heterogeneous environments. He was awarded as a Microsoft Most Valued Professional (MVP) 2009 – 2016 due to his commitment to sharing his knowledge with the IT community, and an SSAS Maestro.  John is an experienced speaker, having given presentations at Professional Association for SQL Server (PASS) conferences, the Microsoft Business Intelligence conference, Software Development West (SD West), Software Management Conference (ASM/SM), and others. He has also contributed to multiple books on SQL Server and business intelligence, including “Microsoft Big Data Solutions”, “Smart Business Intelligence Solutions with Microsoft SQL Server 2008″ and the “SQL Server MVP Deep Dives”” series.

John writes a blog on business intelligence and SSIS topics at http://agilebi.com/jwelch. He is active in open source projects that help ease the development process for Microsoft BI developers, including ssisUnit (http://ssisunit.codeplex.com), a unit testing framework for SSIS.


  1. Tom Bohon says:

    Your article on interfacing with AD using SSIS is exactly what I needed so thanks for that.

    In that article (Retrieving Information from Active Directory with SSIS) it mentions an attached package containing both approaches that you tried. However, I’m unable to find any download link and was wondering if I’m just missing it or if the package is no longer available.

    Thanks again and thanks in advance for your reply.


  2. Richard Magee says:

    John, Thanks for your sharing your expertise. The articles about parent-child inserting are helpful to me, as I am currently doing a similar process using stored procedures. I’m curious if there would be any performance gains in using SSIS instead, as you illustrated in your sample packages. Please email me if you have time for a quick question. Thanks! Richard

  3. Dave Catherman says:


    I would like more info on the presentation you did at Charlotte Code Camp on automating SSIS.

    Is there a way to code generate SSIS script if importing many tables that are similar? I am currently using Entity Framework to import data to the warehouse, but it is way too slow. I assume SSIS will speed this up, but I have some complex mapping during import that further normalizes the data.


  4. Frank says:

    Hi John,

    Starting on BI on MS SQL 2008 and reading your, very good, book “Smart Business…….”, I just wondered if you can point me in any direction which covers Unit Testing when building BI solutions? I can read from your blog that you have been around this with at least SSIS, but how about SSAS and SSRS? I guess I could do some SSRS testing using Watir/Watin for testing through a webbrowser, but do you have a take on this?


    • jwelch says:

      There’s not a lot out there for true unit testing of SSIS / SSAS / SSRS. Right now, the closest you can really get is automated integration tests. One option is testing the results of queries against SSAS (http://biquality.codeplex.com/ can help with that). For SSRS, there is this: http://rsunit.codeplex.com/. And for SSIS, there’s ssisUnit.codeplex.com.

      Just be aware that these tools really verify results and end state, more than testing individual units of code. They are still valuable, but you have to set up tests at a higher level than you might be used to with traditional unit testing.

  5. [...] Some years ago I found a gem that detailed how to modify a package to record errors within a package, using event handlers and script components, and send the error information once the package completes using a send mail task. The post details the steps necessary and gives a great deal of insight into errors within SSIS, http://agilebi.com/jwelch/2007/05/05/handling-multiple-errors-in-ssis/, Thank youJohn Welch!!! [...]

Leave a Reply