Stairway to Biml Level 1: What is Biml?

  • Some interesting additional reading here: http://bimlscript.com/

  • I created something similar in .NET recently for SSIS 2008 as part of a Masters project. It basically encapsulated and simplified the access to the SSIS object model allowing packages to be quickly built in C# using simple declarations and methods.

    Creating a For Each Loop container:

    ForEachLoopContainer ForEachFile = new ForEachLoopContainer(SQ,

    "For Each File",

    "C:\\Data",

    "IncomingData.csv",

    "FileName");

    Inserting a SQL Task into the container:

    SQLTask LogFile = new SQLTask(ForEachFile,

    "Log File",

    OleDBConnectionString,

    "insert into Filelog

    (InputFileName, DateProcessed)

    values(?, getdate())");

    I thought that the advantage of this over the BIML model was that it meant developers already fluent in SSIS and C# didn't have to learn another language.

  • sql_er (7/17/2013)


    Hi,

    Is there a way we can be notified if the next series article gets published (other than watching emails from SQL ServerCentral.com)?

    Thanks!

    Follow Andy on Twitter, he tweets when new articles are addded.

    Thanks,

    Simon



    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • Great article! Thanks Andy!

  • I am surprised I've never heard about BIML previously. I wonder if a lot of folks out there use it or is this something new?

  • Looking forward to the rest of the series, Andy. Thanks for your time and effort. Alas, I don't tweet but I do check back here very often. It would be GREAT if SSC could notify us when #2 is posted

  • mishaluba (7/18/2013)


    I am surprised I've never heard about BIML previously. I wonder if a lot of folks out there use it or is this something new?

    Biml is relatively new.

    :{>

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • shelleybobelly (7/18/2013)


    Looking forward to the rest of the series, Andy. Thanks for your time and effort. Alas, I don't tweet but I do check back here very often. It would be GREAT if SSC could notify us when #2 is posted

    Do you get the newsletter? It will listed be there.

    :{>

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • sql_er (7/17/2013)


    Hi,

    Is there a way we can be notified if the next series article gets published (other than watching emails from SQL ServerCentral.com)?

    Thanks!

    Not right now, but I'll add that to the development list.

  • #2 is scheduled for July 31 as of now, so watch for it.

    Don't forget about Andy's seminar as well if you are in the area: http://www.sqlservercentral.com/redirect/articles/100571/

  • I've read here (http://varigence.com/Forums?threadID=157) that a feature to reverse engineer an existing package into BIML would be release back in 2011.

    Does anyone know if it has been released?

    It would be nice to be able to reverse engineer an existing package into BIML.

    Thanks!

  • It looks like one would need to buy Mist software in order to get access to this feature:

    http://varigence.com/Products/Mist/Capabilities

    http://varigence.com/Forums?forumName=Biml&threadID=157

  • Nice article, looking forward for the rest !!

  • We used BIML (in 2012) to create 200+ SSIS packages that loaded source tables from a SQL db to a staging db. While it was usefull, there were quite a few pain points. These are some of the ones we encountered:

    •Product is ‘free’ so we never really knew how long the support/commitment from the vendor was going to last.

    •Only handles components delivered by Microsoft. So you couldn’t create a package that utilizes a 3rd party SSIS component.

    •Quite difficult to troubleshoot issues when your BIML script fails when creating the SSIS packages.

    •If you are creating lots of packages, the BIML package creation process sometimes fails (memory issue – might no longer be a concern for developers using 64bit OS though).

    •Vendor documentation (which listed out all the objects/properties/methods) wasn’t always up to date. Caused us a big problem when the version was upgraded last year.

    However, it was nice once we resolved the issues. I'd still only limit the use to relatively straight forward SSIS processes.

  • kenstockwell (8/14/2013)

    •Product is ‘free’ so we never really knew how long the support/commitment from the vendor was going to last.

    •Only handles components delivered by Microsoft. So you couldn’t create a package that utilizes a 3rd party SSIS component.

    •Quite difficult to troubleshoot issues when your BIML script fails when creating the SSIS packages.

    •If you are creating lots of packages, the BIML package creation process sometimes fails (memory issue – might no longer be a concern for developers using 64bit OS though).

    •Vendor documentation (which listed out all the objects/properties/methods) wasn’t always up to date. Caused us a big problem when the version was upgraded last year.

    Hi Ken,

    Fair points. My experiences with the product follow:

    •The Biml engine is supplied with BIDSHelper as part of a (brilliant) marketing strategy. The purpose is to introduce data integration developers with the power and flexibility of Biml. While the Biml engine is fully functional - and updated regularly with new editions of BIDSHelper - the marketing strategy is designed to lead data integration developers who need / desire more functionality to invest in a licensed copy of Mist.

    •Using Mist, you can create packages that utilize 3rd party SSIS components.

    •It is difficult to troubleshoot issues when your BIML script fails when creating the SSIS packages. This issue is a priority and will be addressed in future product releases. Stay tuned!

    •The 64-bit version of the product scales nicely. I've executed builds of greater than 1,000 packages without issue.

    •There were challenges with documentation maintenance. I occasionally encounter the odd broken link but a quick search usually takes me to the answer I seek. I try to let Varigence know when I find an issue. I've found them very responsive to me and others.

    However, it was nice once we resolved the issues. I'd still only limit the use to relatively straight forward SSIS processes.

    I concur. I've learned Biml / Mist are not the answer for every SSIS project. My primary use case for the product is for solutions with a limited number of transformation rules for the domain of data. The best number of transformation rules is one (1), as in staging data. If the transformation rules are well-defined, it is likely Biml can help some (or tremendously). It depends on factors like "template-ability."

    Another use I've found for Mist is maintenance. Even if the solution isn't "template-able," I create the SSIS package(s) manually and then import them into Mist. When something changes, it is sometimes easier to maintain inside the Mist environment than using Visual Studio. Again, it depends.

    Thank you for reading the series here, and I welcome your feedback and experience.

    :{>

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply