SSIS is a fantastic platform for automating your ETL scenarios. It provides many useful adapters and transformations out of the box. Additionally, when you need to handle anything out of the ordinary, you can create a script in C# or VB.NET. You can harness the full power of .NET Framework there and meet any special requirement.
Still, everyone who has used SSIS scripting knows there are several pain points with it:
- Upgrading scripts to the next SQL Server version is not an easy affair, as the scripts are early-bound to the current SSIS framework modules.
- The script code is compiled to intermediate language (CIL) and embedded as part of each package. This makes it very difficult to programatically set or change the script code in an SSIS package.
- An SSIS script is actually an entire .NET project in disguise: heavy to load and heavy to use. VSTA is a complex IDE which might become unstable, depending on what versions and patches of Visual Studio are installed on the development machine.
- C# and VB.NET are quite complex, general-purpose languages, which haven't been specifically designed for scripting. As many SSIS users are not advanced developers, it can be a struggle to get the job done with such high-ceremony languages.
Ever wondered what factors lead SSIS scripting to the current suboptimal status quo? Well, back in the 2000s we had the so-called "culture wars", where it was essentially Microsoft vs everyone else. Sun's Java was all the rage - extremely successful for many good reasons. Microsoft had to respond and they did a great job with the .NET Framework and the related C# and VB.NET managed languages. The new and glamorous SSIS which followed in the footsteps of DTS (Data Transformation Services) was designed with many .NET Framework components in use as foundation. It was among the first large scale projects to use the .NET Framework to a smashing success. The new ETL platform had to also use a brand new scripting platform called VSTA. It was powerful and based on the .NET Framework. But it was neither lightweight, nor super convenient for accomplishing small tasks. It still isn't...
As to this day, Microsoft had not revised the original approach to SSIS scripting, we decided it is time to do something about it. We were certain that there was a better and simpler way to achieve similar results. We are happy to introduce the new SSIS JavaScript scripting. This revolutionary SSIS scripting technology addresses all pain points mentioned above:
- Package upgrades are easy, as there is no early binding.
- There is no IL embedding. The JavaScript code is compiled at runtime to CIL and executed at full speed. The performance overhead of the one-time on-the-fly compilation should be negligible for most scenarios.
- A script is just a single file. No separate Visual Studio project, no waiting for an external IDE to load up. You get a full-featured editor embedded right in the task/component JavaScript code tab.
- Compared to C# and VB.NET, the JavaScript language is more forgiving and simpler to use.
It took us a while to decide what language to rely on for delivering the most convenient SSIS scripting experience. After careful analysis, JavaScript won over several strong contenders with good .NET interoperability (IronPython, Lua, PowerShell, F#). We wanted something that's both popular and easy to use. JavaScript ticked all the boxes. The fact that we have been using it since 2016 in SSIS+ REST Adapters also helped for making the final decision.
It is kind of ironic the introduction of the COZYROC JavaScript scripting harks back to the old days of the simpler to use DTS where JavaScript scripting was an option. Everyone who has used DTS can tell stories what life was back then and we want to bring back those good stories.
All COZYROC reusable scripts are now in the process of translation to the new JavaScript scripting and we expect the process to be complete for the official SSIS+ 1.9 release. The existing Visual Basic-based scripts would not be receiving upgrades for SQL Server 2019 or newer versions. To avoid issues when upgrading your projects to later SQL Server versions, it's recommended to switch to their JavaScript equivalents.
We would also want to announce that COZYROC Script Task Plus and Script Component Plus transition to maintenance-only mode, i.e. there would be no enhancements or new features added after the upcoming SSIS+ 1.9 release. If you have developed your own reusable scripts with them, please consider porting your scripts to the new JavaScript scripting engine. If you would really want us to extend the support of Script Task Plus and Script Component Plus beyond SQL Server 2017, please drop us a line.
We expect the availability of the new JavaScript scripting will have far-reaching, positive consequences for the SSIS community. Cheers to a brighter future!
Visit JavaScript documentation pageStarting on December 1st, 2018 the cost of one SSIS+ Ultimate subscription becomes $599/year for new subscriptions. The price for existing subscribers stays the same, as long as their subscriptions are kept current.
Thank you for investing your hard-earned dollars in our products! Your investment is in safe hands and we will not disappoint you!
The COZYROC Team