Updated February 22, 2023
Definition of SSIS Script Task
SQL Server Integration Services (SSIS) is a collection of tools for performing data connectivity. It’s used to simplify data storage and address complicated business problems. The Script task enables coding to perform various functions that aren’t accessible in SQL Server Integration Services’ built-in tasks and transformations. Instead of numerous tasks and modifications, the Script activity can integrate functionalities into a single script.
What is SSIS Script Task?
One of the most intriguing tools for expanding SSIS capabilities is the SSIS Script Task. We can use the script job to program new functionality using C# or VB. Scripting Tasks (most notably the SSIS Script Task) use scripts to augment package capabilities. In addition, it carries out duties that aren’t covered by the Integration services tasks. Data Flow Tasks, data preparation tasks, workflow tasks, SQL Server tasks, Analysis Services duties, maintenance tasks, custom tasks, and scripting activities are the different SSIS tasks.
The Script job can be used for the specified objectives:
1. Other technologies not enabled by built-in connection types can be used to access information. A script, for instance, can retrieve and collect user names from Active Directory using Active Directory Service Interfaces (ADSI).
2. Establish a productivity counter for each package. A script, for example, can construct a refreshed performance counter while a complex or inefficient job is running.
3. Determine whether certain folders are empty or include a certain row, then alter the control flow in packages based on that data. For instance, if data contain zero rows, the values of one variable are set to 0, and a precedence constraint evaluates the value, a File System job cannot copy the file.
The project deployment model and the legacy package deployment model are both supported by Integration Services. Using the project deployment methodology, one can deploy their applications to the Integration Services server. Now write the script that we’ve added the connection manager to the Script component. Microsoft Visual C# 2010 OR Microsoft Visual Basic 2010 are both options. Select the “Script” tab on the left, then perhaps the “Edit Script” button, as seen below.
How to use the SSIS script task?
The Script Task is utilized in a package’s Control Flow. If the needed feature is not provided in the out-of-the-box Data Flow tasks, Script Tasks can be used as an overall utility component. In a control structure, a script task is used. It’s utilized when we need to employ runtime program logic to manage package execution or conduct a task like obtaining or updating a variable inside a package.
The Script Task in the IDE only has one class, Script Main. Place the code we wish to run in the entry-point function, Main (). Calls to other functions or classes can be made as part of that code. Whenever we need to alter the name of the entry-point function for whatever reason, enter the names in the Entry Point field on the Script tab of the editor. We could see a collection of assembly components added to the project and naming conventions set up in the VSTA co-generated class Script Main. A Script task executes program code throughout the delivery workflow at a certain point. It only executes once until we put this in a loop container or an event handler. A task is a collection of activities, and we’ll have many types of work to complete various types of labor. Executing SQL Tasks is one of the most frequent SSIS tasks. This is used to run SQL queries against a database that’s in a relational format.
Steps to Run an SSIS Package from a Script Task in SSIS
– Make a note of the SSIS namespaces.
– Using only an msdb connection, access to the SSIS catalog.
– Set up any settings that the child package will get.
– Choose whether the package should operate in a synchronous or asynchronous mode.
Next, open the Script task editor as below:
SSIS script task Example
Using the VB NET Scripting Language, discover using variables in Script Tasks in SSIS Packages.
Public Sub Main()
Dim fname As String
Dim Source As String
fname = Dts.Variables("User: Filename").Value.ToString()
Source = Dts.Variables("User::SourceFolder").Value.ToString()
Dts.Variables("User::FileFullPath").Value = Source + fname
MessageBox.Show(Dts.Variables("User::FileFullPath").Value.ToString())
// code
Dts.TaskResult = ScriptResults.Success
End Sub
Example #2
Let’s see how to add jobs and execute them in an SSIS package using C# code. In this example, we’ll programmatically construct packages and copy a document from a root directory to a destination folder.
The next step is to Double-click the task and set the attributes as follows. dbdemo.The System Files Task will copy the backups to the script destination folder. An exception to this rule is at the start of the script, once we include the appropriate lines to define the applicable namespaces. The first line is the namespaces, followed by the file connection statement.
using Microsoft.SqlServer.Dts.Tasks.FileSystemTask;
try
{
String sourcePath = @"C:\scripts\dbdemo.bak";
String destinationPath = @"C:\scriptsdestination";
Package myP = new Package();
Executable myExecutable = myP.Executables.Add("STOCK:FileSystemTask");
TaskHost myT = myExecutable as TaskHost;
myT.Properties["IsSourcePathVariable"].SetValue(myTaskHost, false);
myT.Properties["IsDestinationPathVariable"].SetValue(myTaskHost, false);
ConnectionManager myConn = myPackage.Connections.Add("FILE");
myConn.Name = "dbdemo.bak;
myConn.ConnectionString = sourcePath;
myConn.Properties["FileUsageType"].SetValue(myConn, DTSFileConnectionUsageType.FileExists);
myT.Properties["Source"].SetValue(myT, myConn.Name);
ConnectionManager myDestCon = myP.Connections.Add("FILE");
myDestCon.Name = "scriptsdestination";
myDestCon.ConnectionString = destinationPath;
myDestCon.Properties["FileUsageType"].SetValue(myDestCon, DTSFileConnectionUsageType.FolderExists);
myT.Properties["Destination"].SetValue(myT, myDestCon.Name);
myT.Properties["Operation"].SetValue(myT, DTSFileSystemOperation.CopyFile);
myT.Properties["OperationName"].SetValue(myT, "Copy File");
myT.Properties["OverwriteDestinationFile"].SetValue(myT, true);
DTSExecResult res = myP.Execute();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(18, "Failed Process", ex.ToString(), "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
The above code creates a package with connections and flows. Following it add the File Task to the respective package. Next, specify variables to false. Next, make a connection name and a source path to create a connection automatically. Finally, select execute the package to display a message.
Conclusion
The SSIS Script job and its application in our Integration Services packages were the topics of this study. In addition, we demonstrated some of the applications of both scripting features. The Script Task is an extremely popular and efficient tool for completing one’s everyday tasks. We learned how to work with variables, manage failures with try… catch, and then use loops inside this article.
Recommended Articles
This is a guide to SSIS Script Task. Here we discuss the definition, What is SSIS Script Task, and How to use SSIS script task, along with Examples & Outputs. You can also go through our other suggested articles to learn more –
