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...
- Package upgrades are easy, as there is no early binding.
Starting 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