Fire Events, Execute T-SQL Commands and Run SMO with the SSIS Script Task

By:   |   Comments   |   Related: More > Integration Services Development


Problem

I would like to learn how to fire events, execute T-SQL commands and run SMO with the SQL Server Integration Services (SSIS) Script Task. Can you help me?

Solution

This is the second part of the SSIS Script Task tutorial. If you did not read the first part and don't have much experience using the SSIS Script Task, we strongly recommend you read that tip.

Requirements
  1. SSIS installed
  2. SQL Server Data Tools Installed (SSDT) or BIDS (Business Intelligence Development Studio)
  3. A SQL Server database backup
  4. You can use SQL Server 2005 or later versions. In this example, we are using SQL Server 2014.

 

Example 1 - Firing events in SSIS

In the Script Task it is possible to fire errors, warnings and informational messages. In this first example, we will show how to fire an error message.

Drag and drop the Script Task and rename the task to "Fire event".

Integration services

Open the task and edit the script with the following code:

FireError 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;
}

The dts.Events allow firing errors, progress messages, warnings and informational messages. You can specify the error code (18 in this example), detail about the event, the text message and the identifier of the topic in the help (0).

Once you have the code in place, Execute the task.

execute Fire Event ssis

When this runs, you will see a red X in the event.

Fire Event Task

If you go to the output (View > Output), you will be able to see the error message raised: The process failed: The task failed. This is the error message that we coded in the step above. Usually you will raise an event in the catch section of the code.

SSIS output

 

Example 2 - Connect to ADO.net and run a T-SQL command in SSIS

The next example will show how to connect to SQL Server using an ADO.net connection and then we will create a new database.

For this example, drag and drop the Script Task and rename it to "Connection sample".

Connection sql script task

Double click on the task, press the edit script and edit the code. In the region Namespaces, add the System.Data.SqlClient to work with the sql connection as shown below.

script task sqlclient 

using System.Data.SqlClient;

In the Script Task, add the following code:

ssis code ado connection example 

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;
}

The code creates an instance of sqlconnections and commands. The connection will be obtained from an ADO connection named test ADO.NET. After that, we are creating a database named db12 using the command. You can send any T-SQL code with the SqlCommand. The execute nonquery is to run commands that are not queries (don't return data). Finally, the ReleaseConnection releases the connection done.

Save the code and close the script task.

In the Connection Managers, right click and select New ADO.NET Connection...

creating a new Ado connection

In the Server Name, enter the SQL Server Name and then select the database. In this example, we are using the localhost (.) and the master database.

Connection information

Rename the connection to Test ADO.NET Connection to match the script task code.

Renaming ssis connection

Once finished, right click on the task and select Execute Task.

Connection example execution

If everything is OK, a new database named db12 will be created.

SSMS database

You may ask yourself if it is not better to use the T-SQL Task instead of the Script Task. For this particular example yes, but sometimes there are more complex needs or you may already have C# or VB code written that you can use.

 

Example 3 - SMO with the SSIS Script Task

This is the last example. SMO is the SQL Management Object. With SMO you can program any SQL Server administration task like configuring, changing properties, etc. Today we will program the classic backup.

In order to start, drag and drop the Script Task and rename to SMO Backup or any name of your preference.

SMO script task

Double click on the task and press the Edit Script button. In the region Namespaces add the using.Microsoft.SqlServer.Management.Smo;

code SMO

using.Microsoft.SqlServer.Management.Smo;

In the Solution Explorer right click on References and select the Add Reference option.

Solution explorer references

In Assemblies > Extensions, check off the following libraries:

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoExtended
SMO assemblies

The final libraries will be as follows:

list of assemblies added

In the script, add the following code in the public void Main():

SMO code to backup

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;
}

This code creates an instance of the Server and the backup. Then the ConnectionContext connects using trusted authentication (Windows Authentication). Then the action is to create a backup of the database named db1 in the file system. The next step is to create the device and specify the path. Once done, the backup is executed using the Server information.

Save the code, close and accept the Script Task changes and execute the task.

execute SMO Script task

If everything is OK, a new backup will be created in the path specified.

backup created by SMO

 

Conclusion

If you do not have experience with C# or VB, you will not be able to fully take advantage of the Script Task. However, with this small demo you should now be aware of some of the capabilities.

Next Steps

Here are several links that will be useful to you:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article

















get free sql tips
agree to terms