July 17, 2013 at 3:38 pm
Some interesting additional reading here: http://bimlscript.com/
July 18, 2013 at 3:52 am
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.
July 18, 2013 at 4:19 am
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
July 18, 2013 at 10:19 am
Great article! Thanks Andy!
July 18, 2013 at 1:47 pm
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?
July 18, 2013 at 3:37 pm
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
July 18, 2013 at 8:17 pm
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
July 18, 2013 at 8:19 pm
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
July 19, 2013 at 12:25 pm
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.
July 19, 2013 at 12:27 pm
#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/
July 25, 2013 at 8:42 am
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!
July 25, 2013 at 11:35 am
It looks like one would need to buy Mist software in order to get access to this feature:
August 1, 2013 at 9:04 am
Nice article, looking forward for the rest !!
August 14, 2013 at 8:07 am
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.
August 18, 2013 at 8:06 pm
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 31 total)
You must be logged in to reply to this topic. Login to reply