SQL SERVER – Who needs ETL Version Control?

While making some changes (read: mistakes) to my ETL business logic the other day, it occurred to me much too late that those unfortunate changes had replaced the once properly working logic with now very flawed logic.  The good news was that I remembered what the working logic was supposed to be.  The bad news, I had to re-create it.  Had I had the working logic already checked-in under version control, I could have saved myself the two hours of wasted time and effort.  In an ETL team development setting, these types of issues could easily multiply and significantly impede developer progress and productivity.

Version control makes it possible to have multiple people developing together and working on same project.  If I am working on something, a fellow ETL developer will not be able to check it out and make changes to it until I am finished with my changes and check those back in.   This addresses the common situation where one developer’s project artifact and code changes clobber that of another developer by accident.

Another reason to have version control is that it gives the developer confidence to try different modifications to the logic without having to worry about a negative impact to the previously developed logic.   Sure, I could always save off my work and create a copy to try things out on.  However, in my experience, I would wind up with various different copies all over the place and it would then become difficult to keep track of what’s what.  Version control allows me to capture and document relevant information about whatever I am checking in.

Finally, version control maintains a history of changes that have been made to the application logic.  In some environments, this information and traceability can prove extremely valuable if not necessary.   Ever wonder when a bug was introduced into the application logic?

Click to Enlarge

The expressor Studio desktop ETL tool allows developers to seamlessly check-in and -out project artifacts to expressor’s Repository, which supports version control and is available as part of their Standard Edition product offering.

Many data integration solutions do not have built-in version control.   Most offer some way to interface to version control systems, but expressor has it built-in with their Standard Edition product offering.   I requested a trial license to play with it and was pleasantly surprised at expressor’s seamless version control integration.  There was no need to do any separate 3rd party download, install, and configuration.  With expressor, check-in and check-out was almost transparent.  As someone who has developed ETL solutions, I really appreciated the clean and simple approach expressor has taken.   I have no doubt this would help make any ETL project go smoother and faster.

You can request a free 30-day trial of the expressor Standard Edition, or download their free expressor Studio community ETL tool at their site.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

ETL, SQL Scripts, SQL Utility
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Advanced Aggregates with the Over Clause – Day 11 of 35

Related Posts

8 Comments. Leave new

  • What is wrong with using Change data capture and change tracking in SQL Server, which are available in Developer edition? These 2 methods have always worked for me.

    Reply
  • Hi Pinal
    I am looking for versioning of ETL packages , do you had any experienced with ETL versioning tool.

    Reply
  • Michael Waclawiczek
    August 10, 2011 7:56 pm

    What Pinal talks about is how you can manage your ETL project artifacts, business rules, transformation logic, etc. in a team environment, where multiple developers and business users are involved. What an ETL Version Control capability allows you to do is to manage these artifacts both in terms of user access and versioning. Think of ETL Version Control having similar capabilities as to how you manage documents in Sharepoint.

    Regards,
    Michael Waclawiczek, VP Marketing, expressor
    http://www.expressorStudio.com

    Reply
  • Yann Nicolas (@yannart)
    August 11, 2011 11:41 am

    I am currently using Kettle ETL that are saved in XML format with Git as a version controller. It is a very powerful combination.

    Reply
  • Jon Massey (@jonny_boy27)
    September 29, 2011 8:25 pm

    I’ll second Yann by saying KETL + git FTW.

    Also, hooking Integration Services projects into VSS is trivial.

    Reply
  • Gogula G. Aryalingam
    September 29, 2011 8:58 pm

    Pinal,

    Could you let me know in short as to how the built in version control in Expressor differs with that of the SSIS-TFS combination, or is it similar?

    Thanks

    PS: Noticed that your writing style has changed somewhat in this post.

    Reply
  • Hi Pinal,
    we are planning to implement Version Control with IBM Cognos Data Manager Tool, can you please help me, can we use expressor studio with data manager tool. In data manager there is a facility to any use source code control systems.
    If would be great if you can reply me

    Thanks,
    Samiha

    Reply

Leave a ReplyCancel reply

Exit mobile version