In SQL Server Integration Services (SSIS), the Script Task allows you to execute T-SQL commands and run SQL Management Object (SMO) tasks. In this blog post, we will explore how to fire events, execute T-SQL commands, and run SMO tasks using the SSIS Script Task.

Firing Events in SSIS

The Script Task in SSIS allows you to fire errors, warnings, and informational messages. Let’s start with an example of firing an error message. Drag and drop the Script Task onto the SSIS package and rename it to “Fire event”. Open the task and edit the script with the following code:

public void Main()
{
   // TODO: Add your code here
   Dts.Events.FireError(18, "The process failed", "The task failed", "", 0);
   Dts.TaskResult = (int)ScriptResults.Success;
}

In this code, we use the Dts.Events.FireError method to fire an error event. You can specify the error code, details about the event, the text message, and the identifier of the topic in the help. When you execute the task, you will see a red X in the event, and the error message will be displayed in the output.

Executing T-SQL Commands in SSIS

The Script Task also allows you to connect to SQL Server using an ADO.NET connection and execute T-SQL commands. Let’s create a new database as an example. Drag and drop the Script Task onto the SSIS package and rename it to “Connection sample”. Edit the script with the following code:

try
{
   // Add new connection and a SQL command instances
   SqlConnection myADONETConnection = new SqlConnection();
   SqlCommand myADONETCommand = new SqlCommand();
                
   // Connect to the existing connection
   myADONETConnection = (SqlConnection)(Dts.Connections["Test ADO.NET Connection"].AcquireConnection(Dts.Transaction) as SqlConnection);
   
   // Send the command
   myADONETCommand = new System.Data.SqlClient.SqlCommand("CREATE DATABASE db12", myADONETConnection);
   
   // Execute the query
   myADONETCommand.ExecuteNonQuery();
   
   // Release the connection
   Dts.Connections["Test ADO.NET Connection"].ReleaseConnection(myADONETConnection);

   Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
   MessageBox.Show(ex.ToString());
   Dts.TaskResult = (int)ScriptResults.Failure;
}

In this code, we create an instance of SqlConnection and SqlCommand. We connect to the SQL Server using an ADO.NET connection named “Test ADO.NET Connection” and execute the T-SQL command to create a new database. Finally, we release the connection. When you execute the task, a new database named “db12” will be created.

Running SMO Tasks in SSIS

SMO (SQL Management Object) allows you to program SQL Server administration tasks. Let’s create a backup using SMO. Drag and drop the Script Task onto the SSIS package and rename it to “SMO Backup”. Edit the script with the following code:

try
{
   // Creating the Server and backup instance
   Server myServer = new Server("localhost");
   Backup mybackup = new Backup();
   
   // Connecting to the Server
   myServer.ConnectionContext.LoginSecure = true;
   
   // Database Backup of the database db1 in the file system
   mybackup.Action = BackupActionType.Database;
   mybackup.Database = "db1";
   mybackup.MediaName = "FileSystem";

   // Create a new device and specify the path
   BackupDeviceItem myDeviceItem = new BackupDeviceItem();
   myDeviceItem.DeviceType = DeviceType.File;
   myDeviceItem.Name = "C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\Backup\\db1_test2.bak";
   mybackup.Devices.Add(myDeviceItem);

   // Start the backup
   mybackup.Initialize = true;
   mybackup.SqlBackup(myServer);

   Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
   MessageBox.Show(ex.ToString());
   
   Dts.TaskResult = (int)ScriptResults.Failure;
}

In this code, we create an instance of Server and Backup. We connect to the SQL Server using trusted authentication and create a backup of the database named “db1” in the file system. Finally, we start the backup using the Server information. When you execute the task, a new backup will be created in the specified path.

Conclusion

The SSIS Script Task provides a powerful way to execute T-SQL commands and run SMO tasks. By using the examples provided in this blog post, you can learn how to fire events, execute T-SQL commands, and run SMO tasks in your SSIS packages. With these capabilities, you can enhance your data integration workflows and automate SQL Server administration tasks.