Right now, as I write this, I’m between jobs. Earlier today I finished my last day as an in-house data warehouse solutions architect and business intelligence developer. Tomorrow I’m starting my new job as a technical architect in a consulting company. It’s sad to say goodbye to wonderful coworkers, but it’s also very exciting (and scary!) to start a new chapter in my life.
BIDS Helper and BimlExpress does not support creating SSIS project parameters from Biml out of the box. There are workarounds (and I have previously blogged about my solution for creating project parameters from Biml), but why is this not a standard feature in BIDS Helper or BimlExpress? Many people have asked about this, so I sat down with Biml creator Scott Currie (@ScottCurrie) to get the full story.
Why doesn’t BIDS Helper or BimlExpress emit SSIS project parameters from Biml?
Well, technically it could, but it shouldn’t. The user experience would have serious issues, leading to confusion, frequent errors, and the potential for data loss. How can that be?
First a comparison to SSIS packages
When the BimlEngine emits a package, it knows that it is overwriting the entire .dtsx file. In general, this is safe, because each .dtsx file contains exactly one package. There is no risk that by overwriting one .dtsx file, that it might overwrite both the desired package and some other unrelated package. One package per file – good.
Furthermore, since it is one package per file, BIDS Helper or BimlExpress can present you with a convenient checklist of all the packages that are about to be overwritten. Maybe you made a mistake in your BimlScript code and accidentally generated a package that has the same name as a painstakingly created manual package. Maybe you noticed it in the overwrite list. Maybe not. Either way, you had the opportunity to prevent that bad thing from happening. Also, if you are using source control and checked in recently, you can revert the changes and restore your manual package.
Finally, if you have a package open with unsaved edits, not much changes from the above scenarios. The very uncommon worst case is that you lose a small number of changes that you made to a manual package since you last saved – and only when you also made the mistake of generating a package with the same name as your manually created package. The common case is that you lose some manual changes (e.g. moving boxes around on the design surface) that you never intended to preserve in the first place.
How are SSIS project parameters different?
SSIS project parameters do not work the same way as SSIS packages. All project parameters are stored as XML elements in a single XML document for the entire project called Project.params. This is the core reason why packages have a good overwrite story while parameters have a poor overwrite story.
It should be obvious that BimlExpress can’t just overwrite your Project.params file. Of course, BimlExpress would be creating the parameters you specified in your BimlScripts, but it would also be overwriting any parameters you might have created manually. If you are a Biml purist, you might not care about this, because you would be fine with creating all of your project parameters through Biml. Unfortunately, most Biml users are not Biml purists – and even fewer development teams are Biml purists.
The next logical thought is to avoid the overwrite problem through merging. Certainly BimlExpress could insert the generated project parameter elements into the existing Project.params file. Duplicate values could be ignored. Unfortunately, this only solves the problem for the very first version of your generated Project.params file. What happens when you change the names of the parameters in your Biml code or delete some of them? The BimlEngine has no way of knowing that a given parameter in your Project.params file was deleted or renamed and not just created by hand. This would lead to a potentially large number of orphaned project parameters that you would need to manually manage.
Perhaps, you might suggest, we could add some form of XML annotation to the Biml-generated elements in the Project.params file to solve or at least improve the merging capabilities? That could be a great solution, but Visual Studio / SSDT strips any additional properties you add whenever it saves the Project.params file. Even if Visual Studio / SSDT preserved those additional annotations, this could still be a risky strategy, since other Microsoft and 3rd party tools have the potential to fail or otherwise misbehave if the BimlEngine diverges from the standard accepted encoding for the Project.params file.
This gets even worse for project parameters in the scenario where the user has unsaved manual changes to the Project.params file at the time when generation is performed. These unsaved changes are impossible to detect and merge because the SSIS project system does not expose those changes to add-ins until they have been saved in the Project.params file. This means that parameter changes would have to be force-saved prior to any Biml generation, but would then still suffer from all of the above issues.
Can’t you do anything? Even with an optional setting?
Based on the above analysis, the only scenario that doesn’t create more user confusion and frustration is the Biml Purist scenario. For this case, we might be able to offer an option to always overwrite the Project.params file, but that would satisfy a minority of users and would also be very frustrating in cases where you forgot to turn it off for non-purist projects. Our thinking is that it is not worth the trouble it might cause to add this option.
Thank you, Scott!
Thank you to Scott Currie (@ScottCurrie) for taking the time to give us such a detailed explanation! I really appreciate it, and I hope this helps all of you Biml users who have been wondering why you can’t create SSIS project parameters from Biml. (Well, you can, but as you have probably realized by now – it can be risky.)
Finally, both Scott and I would love to hear your thoughts on this. Would you like to see an optional setting? Is it worth the risk, especially when working with Biml-generated packages and manually created packages? Is it worth the risk when working in mixed development teams?
What do you think?
SQLSaturday Oslo is back for the third time in 2016, and this year we are offering four amazing pre-conference workshops! We are very happy to welcome Microsoft Data Platform MVPs Grant Fritchey, Dejan Sarka, André Kamman and Mladen Prajdic to Norway :)
Early Bird Tickets
Early Bird tickets are available until July 1st. For as little as 1200 NOK you can attend a full day of high-quality training with some of the best experts in the world. (That’s less than 1/3 of what you usually pay for high-quality courses like these!) After July 1st the price goes up to 1600 NOK, so make sure you register soon – and bring your coworkers :)
SQLSaturday Oslo 2016 Pre-Conference Workshops
These four pre-conference workshops will be held on Friday, September 2nd in Oslo:
Query Tuning with Execution Plans
by Grant Fritchey (@GFritchey)
Level 200 – Intermediate
At least 80% of all database performance problems can be related directly to your T-SQL code and your data structures. This full-day workshop gets right to the heart of that issue and shows you how to identify common performance problems using the tools available to you within SQL Server.
(…read more and register)
Data Mining Algorithms in SSAS, Excel, R, and Azure ML
by Dejan Sarka (@DejanSarka)
Level 400 – Expert
Data mining as the most advanced data analysis technique is gaining popularity. With modern data mining engines, products and packages, like SQL Server Analysis Services (SSAS), Excel, R, and Azure ML, data mining has become a black box. This course explains how the most popular data mining algorithms work, when to use which algorithm, and advantages and drawbacks of each algorithm.
(…read more and register)
PowerShell for DBAs
by André Kamman (@AndreKamman)
Level 300 – Advanced – Half-day
DBAs will learn all they need to know today to start using Powershell as their main tool to deploy, manage and monitor their whole environment. We will talk about error handling, creating modules, building in pipeline support, whatif support, logging, parameters and more general Powershell concepts.
(…read more and register)
Security Best Practices for SQL Server and Application Developers
by Mladen Prajdic (@MladenPrajdic)
Level 300 – Advanced – Half-day
In this precon we’ll look at how to implement security conscious patterns and practices for SQL and .Net application development like multilayer security architecture and the pessimistic approach. Demo attacks will include SQL Injection (SQLi), Cross Site Scripting (XSS), Cross-Site Request Forgery (CSRF), Distributed Denial of Service (DDOS), Social Engineering (SE) and respective protective measures. We’ll try to blow your mind with what malicious attackers can do to your organization and how to protect against it.
(…read more and register)
Spread the word
We hope you want to help us make SQLSaturday Oslo 2016 a great event for everyone, so tell your friends and coworkers about these pre-conference workshops! If you’re on Twitter, follow @SQLSatOslo and use the hashtag #SQLSatOslo. The SQLSaturday Oslo team and Microsoft Data Platform User Group Norway look forward to seeing you in September! :)
Say welcome to BimlExpress – the newest, shiniest and completely free Biml toy! :) I first mentioned BimlExpress at SQLSaturday Vienna and have been waiting for the official release since then. I’m very happy that I can now send you all over to Varigence’s website to download the Visual Studio Add-in!
What is BimlExpress?
BimlExpress is a free Visual Studio add-in for working with Biml in your SSIS projects. It allows you to add and edit Biml files, generate SSIS packages from Biml, and the code editor is fully featured with syntax coloring, error highlighting and intellisense.
If you are already using BIDS Helper, you will see that BimlExpress works the same way and includes all the same Biml features as in BIDS Helper – just with a new and improved code editor. No more squiggly red lines, yay!
Should I use BimlExpress or BIDS Helper?
Both! :) BIDS Helper is a fantastic, free add-in with a ton of useful features for your Business Intelligence projects. The release of BimlExpress simply means that Varigence can update the add-in and the Biml engine with new features more frequently and separately from BIDS Helper. When BIDS Helper is updated it will include the latest Biml engine. Keep using BIDS Helper, and install BimlExpress as well for the latest Biml features.
And all of you Biml fans agree with me that the new code editor is worth it, right? Right!? :)
The recording of SQLHangout #36 is now available! :) In this episode, Koen Verbeeck (@Ko_Ver), Boris Hristov (@BorisHristov) and I chatted about presentation design, why design matters in your SSIS projects, about new features in SQL Server 2016, and why it is a fun time to be working with Microsoft technologies. It is also a special episode, since it is my first and Boris’ last episode as a host.
SQLHangout #36 – Why Design Matters with Koen Verbeeck and Boris Hristov
All the recorded videos are available in the YouTube playlist and on the SQLHangouts Facebook page. You can find all of Boris’ previous blog posts in his SQLHangout archive, including his latest blog post about SQLHangout #36.
Do you want to participate in a SQLHangout some day? Let me know! :) If you don’t want to participate in a SQLHangout yourself, but you have an idea for a topic or speaker, use this form or tweet and use the #SQLHangout hashtag.
I also want to say a huge thank you to Boris for everything he has done so far, and for trusting me to take over the SQLHangouts! I’m going to miss you as a host and promise to do my best to keep the SQLHangouts interesting :)