When and how to use Script Task in SSIS
Script task is one of the available control flow task available in the tool box. When it comes to data flow, we can use script component for a similar purpose.
A script task or a script component is basically used when you want to do something programmatically.
For example, we have a file available from FTP and while taking the file to our local path/shared path in network if we want to rename the file (like, appending date and timestamp to the file name) and copying the file to archival purpose etc., we can do it through script task.
Script task is basically used to have c# code/vb code in place to tackle the requirements.
We can use the existing package variables or connection manager variables in script task.
Script task contains set of readonlyvariables, readwritevariables that can be used in the task. As the name suggests, readonlyvariables are readonly and readwritevariables are used to read and also to update the data associated with it.
We can select the variables into these portions by browsing through available system variables and user defined variables.
We can edit the script task name in General tab available at the left hand side.
We can implement the code in either c# or vb. We can select this option in Script Language drop down available in the menu. You can implement the customized logic in the .cs file or .vb file that will be populated upon clicking the Edit Script button available in the bottom. Also we can select the start point of the script execution in Entry point option available by selecting the method name in the script.
Once we click on edit script button, a new cs page will be populated.
We can write our code here to customize the options.
Also its better to declare a enum to return whether the script task is success or failure. By default it will be available with the ScriptMain.cs file.
C# code to retrieve variable values:
Boolean flag = (Boolean)Dts.Variables["IsAvailable"].Value;
C# code to retrieve connection manager values:
string connectionString = Dts.Connections["ConnectionString"].ToString();