I have seen people writing .net script inside a Script Task when they have to set the value of a variable. It is true that there is no dedicated task or mechanism in SSIS to achieve this. I have even seen people writing custom components, just for setting value to a variable. And setting a variable is a basic programming requirement and is often required while developing ETL. And people struggle to do this by using custom components/tasks, Script Task, etc. But there is a small work around to get this done easily.
Remember the simple For Loop Container? It has a “InitExpression” property where you can assign value to initialize a variable, normally the one controlling the iterations. This can be used to set value to a variable. You can set the loop to terminate right after the assignment. I guess this will be faster than the Script Task and it is definitely much more user-friendly than any other methods. Below screen shots explains how to use the For Loop Container. Hope it is self explanatory.
Tags: SSIS, Work around
