SSIS ETL Framework v1 goes Beta!

A quick update on the status of the BI Monkey SSIS ETL Framework (on Codeplex @ http://ssisetlframework.codeplex.com/)

The Framework v1 has gone into Beta – i’ve completed all the testing, and now just need to tidy up the reports and add some extra logging capability – but it is now fully functional. I’ve left it in Beta as I want to get some feedback on it before I move it live, plus fix those small details I mentioned.

So now I will press on with updating the documentation (yes, really!) and start laying the foundations for the more Enterprise level v2 framework.

I look forward to your feedback – please take advantage of Codeplex’s issue logging functionality to help me manage bugs and improvements.

7 thoughts on “SSIS ETL Framework v1 goes Beta!

  1. Hi James,

    Currently I’m trying to create a proper SSIS framework (not the first time :)) that can handle logging in an effective way in case of multiple parent-child levels. Apart from that phase it’s in Alpha phase and I think it’s quite useful for troubleshooting.
    What I try to achieve:
    – I have a MasterPackage with global OnPostExecute and OnError event handlers. The first logs if a task was successful (along with its name, starttime, endtime) the later does the same in case of an error.
    – I have a ChildPackage called by master that also contain Event Handlers in the same way.
    – The system works fine separately, I mean it logs everything I currently need
    – The problem is that when the Child is called from Master then every task in Child is logged twice because the Event Handlers are inherited to the child. You can ask “Why don’t you just turn off the Event Handlers in the Child?” – that would be an obvious solution but there’s an issue: when a task logged in the Child package I want that logged as PackageName = Child. But if the Master event handler logs it the PackageName remains Master.
    So I was trying to disable the event handlers for the Execute Package Task (that calls the Child in Master) by the following methods (without success :( )
    1) I set the DisableEventHandlers property to TRUE
    2) I created a separate and empty event handler for the Execute Package Task and set the Propagate sys variable to FALSE
    3) I set the LoggingMode property to Disabled

    No use :(

    Moreover the child package runs correctly but Master considers it as an error (despite that everything is green in the Child) but I haven’t thoroughly checked this strange behaviour.

    If you could spare some minutes to contemplate about this EventHandler issue I’d be grateful. I don’t really know if the problem is with my logic or with the tool?

    Thanks,
    MartinIsti

    p.s. feel free to reply in email if you think it would be a longer conversation :)

  2. That Propagate trick seems to be working! Prolly I made a mistake at the first time using it.

    Thanks anyway, and I think I’ll have some questions/remarks about your framework. I haven’t thoroughly looked it through but at first glance it has too many tasks. I mean without using Event Handlers it seems a bit overcomplicated. Although I have to admit that using Event Handler in itself also makes things more complicated.

  3. I look forward to your feedback. I’m not sure why you think the Framework has too many tasks – do you mean in the control flow? I’ve avoided using event handlers as they are not immediately visible as part of the control flow.

    I intend to use them for logging errors only, as you’ve done. The multiple logging is a pain I agree, but it could be possible to handle this using the execution GUIDs – something i’ve yet to investigate properly.

  4. Hi James,

    I think it’s great what your going for with this project. In my experience a good ETL framework is key to a SSIS implementation. I had a codeplex project I was wroking on that might dovetail nicely with what you have here. The project is to build a data mart and cube around SSIS performance. I was attempting to pull natively from the SSIS sql logs however there were issues with the aggregations in the cube. If you’re interested have a go at the link below and let me know what you think…

    http://ssisbi.codeplex.com/

    Cheers!
    Ben

  5. Thanks for your response James!

    By “too many tasks” I meant that the framework itself without actual data transfer content looks a bit complex so that I cannot get a high level understanding at the first glance. It’s quite simple and straightforward when I follow your logic though.
    But I see your point why avoiding event handlers. For the same reason I try to avoid script tasks because they are hidden. Okay, you can see the existance of a script but generally not its function.
    You can see here what I think a clean parent package: http://martinisti.files.wordpress.com/2010/08/ssis_2ndlevelmaster.jpg

    It’s not finished yet and it’s a 2nd level Master but you can get the idea I’m following. Before implementing this the previous framework contained at least 10 containers instead of the 3 major chunks from my linked picture.

    It’s never been easy but at least we, developers have quite a big sandbox to use in terms of SSIS :)

  6. MartinIsti – I see your point about a clean package. In my case i’m putting the complexity in the package so it’s easy to manage from the database – and child packages are not fixed. But agree – we have a lot of options!

    Ben – great idea – I strongly suggest you put some docco up – e.g. sample reports – so people can get a feel for what you are trying to achieve.

  7. Hi James,

    I was actually to initiate the simialr kind of ETL Framework for our new project. Your ETL Framework beta version it helps us to use as base . This looks very great and accelerate the development. I will come up with my feedback and or any additional improvements in the FM will logged in the Codeplex

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>