I was recently asked if it is possible to display the results of a query / stored procedure in an SSIS Message box. The answer is Yes, with a bit of C#.
Let’s build a quick project. We’ll use AdventureWorks2008R2 as our database.
First, let’s create a simple stored procedure that gets the top 10 employees from the view HumanResources.vEmployee.
use AdventureWorks2008R2 go create procedure dbo.GetTop10Employees as begin select top 10 FirstName , LastName , PhoneNumber from [HumanResources].[vEmployee] end |
As you can see, the stored procedure displays a simple list of the top 10 employees. Make note of the column names. We will need the column names later, in the SSIS script task.
With the stored procedure created, we’ll start with a new SSIS project.
We’ll add a connection to the database using an OLE DB connection. In this example, we’re connecting to my local machine.
Next, let’s create a variable to store the stored procedure results.
If you do not see the Variables window, right click on the SSIS package body, and click on Variables.
Click on the New Variable button to add a variable to the project.
Give the variable a name and select “Object” as the data type. Ensure that the scope is “Package” as well. If the scope is not package, then the variable will not available to the entire package.
Next, create a SQL task. Drag the SQL task from the SSIS toolbox to the package body.
If we use stored procedures and data sets, we must update several settings in the properties window that are not available in the regular SQL Task properties window.
To access additional properties, highlight the SQL task and Press F4 or View -> Properties Window. The properties window should appear in the panel on the right-hand side.
Next, update the following values within the properties Window:
IsStoreProcedure: True
ResultSetType: ResultSetType_RowSet
Next, we will update the Connection, the task name, task description, and Results Set Mappings. Double Click on the task to open the regular Execute SQL Task Editor.
First, set the connection to your target machine.
Next, give the SQL Task a name and thorough description.
Next, double click on SQL Statement to bring up the SQL Query box. Enter the stored procedure name in the box that appears.
exec dbo.GetTop10Employees |
Next, click on Results Set in the far-left panel to bring up the results set mapper.
Click on the Add button to add a new Results Set the SQL Task. Under Result name, simply enter 0 (the number zero). Under variable name, enter the name of the variable we created earlier.
Click OK. We now see the SQL Task has an updated name and no longer has a red X. If the SQL Task completes successfully, the result set will be stored in the mapped variable.
Next, we will utilize a Script Task to display the results. Drag a Script Task from the SSIS toolbox to the package body.
Drag the green arrow from the SQL Task to the Script Task.
This will execute the Script Task after the SQL Task complete successfully.
Now, to edit the Script Task. Double click the Script Task to bring up the Script Task Properties.
Under Script Language and EntryPoint leave the defaults of Visual C# 2010 and Main respectively. Next, we need to access the Results Set stored in the package variable. Click next to ReadOnlyVariables to bring up the variable picker.
Select the variable we created for this package and click OK. You will now see the variable is a read only variable available to this script task.
Next, click on the Edit Script Button to bring up the C# 2010 Editor. First, add System.Data.OleDb to the NameSpaces section.
Next, add the code below to the Main section:
public void Main() { // TODO: Add your code here string outString = ""; DataTable dt = new DataTable(); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.Fill(dt, Dts.Variables["User::sprocResults"].Value); foreach (DataRow row in dt.Rows) { outString += row["FirstName"].ToString() + "\t" + row["LastName"].ToString() + "\t" + row["PhoneNumber"].ToString() + "\n"; } MessageBox.Show(outString); Dts.TaskResult = (int)ScriptResults.Success; } |
This will load the Result Set into a data table, one row at a time. The code then appends the values to an “OutString.” That OutString is then given to the Message Box for display. Notice, we refer to the variable in this way: [“User::sprocResults”]. Also, we explicitly call out the column names in data row like this: row[“PhoneNumber”].ToString(). Between each column, I’ve also put in a tab character “\t” and a new line character at the end of every line “\n”.
Click on the save icon to save the C# script and close the window. Next, click on General on the far right to give the Script task a name and thorough description.
Click OK to close out the Script Task Properties window.
To execute the package, click on the green arrow or press F5 to execute the package.
The results from the stored procedure will now display in the message box! Lastly, click on the stop button to finish the package execution.
