SQL Server Integration Services (SSIS) - Scripting in SSIS Packages

Article

Introduction

This article describes the scripting option available with SSIS, which is nothing but the Script Task component in SSIS. The very cool feature available in SSIS packaging is the Script Task, to justify is many task components are available which performs a single operation as per the nature of the task. But with this Script task we can perform our own custom task on how the process should happen as per the business. We will see the step by step process on how to use this task.

Before proceeding to the steps, in order to follow the series of articles on SSIS.

Steps

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see how to use the Script Task component.

On the Tool box available in the right side pane, select the Script Task component as shown in the following screen.

Right Click on the Script Task and select edit or double click on the task component in order to go through the properties of the task as shown in the following screen.

In the Script Pane, we have option as Script Language which points to Microsoft Visual Basic .Net. We have only this option in SSIS 2005 version, if we have installed with SSIS 2008 then we have option for C#.Net as well. Entry Point option is the place which we need to specify the main method for the script which we are going to write. Readonlyvariables and Readwritevariables are the options where we can use it for specifying the variables which are used at the script. We can specify the variables in comma separated format in order to use more than a single variable.

At the bottom of the pane you can find Design Script option which is the main window where we are going to put the script code. Clicking on that button will open the window as shown in the following screen.

As you see in the screen ScriptMain is the main method which is going to act as the entry point. This window helps to put our own custom logic as per the business using all the Visual Basic references and build a complete useful task.