I’ve done a fair bit of work implementing custom ETL solutions with SSIS concluding that the tool provides excellent “out-of-the-box” support for ETL as well as almost limitlessly extensibility with the Script component. If you have a need to bring data from there to here with transformations along the way then SSIS is the solution; full transactional support, easy to use, fast, and extensible.
I use C# to develop my Script components. As a simple example, during development I sometimes use a dialog to display running values within the package.
Notice that the plumbing has been generated by the system allowing us to focus on functionality. All of the variables are addressable within the script with read / write access defined in the Script Component’s properties dialog in BIDS. Notice the call to display the MessageBox (look familiar?):
public void Main()
{
MessageBox.Show(Dts.Variables["RowCount"].Value.ToString() + "/" + Dts.Variables["UnmatchedRowCount"].Value.ToString(), "RowCount", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
Dts.TaskResult = (int)ScriptResults.Success;
}
There isn’t much that can’t be done with a Script Component; on occasion I’ll simplify a Data Flow by encapsulating multiple data transforms or cleansing operations in a script. Even if you are a magician with the built-in tasks and components in SSIS I think you’ll find that using the Script Component will simplify and extend the capabilities of your package.