SQL Server Integration Services (SSIS) – Part 8 – Scripting in SSIS Packages

Karthikanbarasan
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 13626 red flag
Rating: 5 out of 5  
 2 vote(s)

This is part 8 of the series of article on SSIS


 Download source code for SQL Server Integration Services (SSIS) – Part 8 – Scripting in SSIS Packages

Introduction

In this article we are going to see on 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 go through the below link

SSIS Articles Part 1 to 6

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 on 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 below 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 below 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 below 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.

Conclusion

So in this article we have seen on how to create a Script task and write a custom code as per our requirement.

Page copy protected against web site content infringement by Copyscape

About the Author

Karthikanbarasan
Full Name: Karthikeyan Anbarasan
Member Level: Silver
Member Status: Member,Moderator,Microsoft_MVP,MVP
Member Since: 1/2/2011 3:00:14 AM
Country: India
Thanks Karthik www.f5Debug.net
www.f5Debug.net
www.f5Debug.net Around 5 years of experience in Microsoft technologies like ASP.Net, VB.Net, C#.net, SQL server, Biztalk Server, WCF, WPF, SSIS, SSRS, SSAS, AJAX and working as Senior Analyst in a US based MNC.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)