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.
- Free for test and development within Visual Studio
- Support for SQL Server 2005, 2008, 2012, 2014, 2016, 2017, 2019
- 200+ reusable components