Ssis Script Task Message Box Variable

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.

The common method of using variables is to send them into a Script Task as decision-making elements or to drive downstream decisions by setting the value of the variable in the script based on some business rules. To use a variable in a script, the variable must be locked, accessed, and then unlocked. This code simply populates a message box with the value from strMessage variable. Don't forget to supply the variable name in the ReadOnlyVariables property of the script, otherwise the script task won't be able to read the variable from the package. If you execute the package you'll get a pop up box that should show you this.

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.

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.

We'll add a connection to the database using an OLE DB connection. In this example, we're connecting to my local machine.

Join the millions already enjoying Kodi. Kodi is available as a native application for Android, Linux, Mac OS X, iOS and Windows operating systems, running on most common processor architectures. A small overview of the features can be found on our about page. For each platform, we offer a stable and development release(s). Kodi 17.6 on android 4.4 2. Kodi android 4.4.2. New Kodi Addons November. Welcome to BestforKodi.com. I have been taking a look at Install The Red Queen Kodi Addon Repo.This addon to. New Kodi Addons November. Guide Install MC 1080p Kodi Addon Repo. Welcome to BestforKodi.com. I have been taking a look at Install MC 1080p Kodi Addon Repo.This addon to hit. How To Install or Upgrade an Android TV box to Kodi 17.6 Krypton. Written Tutorial: https://www.wirelesshack.org/how-to-upgrade-an-android-tv-box-to-kodi-17. Download Kodi 17.6 APK - Kodi is a powerful media center that can bring all the content you want on your mobile device.

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.

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.

Ssis Script Task Global Variable

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:

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.

A lot of people still consistently ask me about how to read and write to variables in the SSIS script task. In this post I will demonstrate for you the two ways in which you can go about this, one from native SSIS functionality and the other from code. In SQL Server 2008 both will generally work out equally as well. In previous versions, you may want to stick with the coding piece as sometimes the ‘automatic' integration with the variable dispenser was a little hooky.

So for our example, I am going to set up a simple SSIS project. First I create 2 variables: MyName & YourName. I have scoped these at the packages level as it has always been my instinct that unless there is a compelling reason to scope it otherwise.

Now that we have our variables set up, we are ready to begin making our project. Since this example is going to be pretty straightforward, we will just drop two script components onto the control flow design surface. The first will be the one using the native way of handling variables in the script component and the second will be using a little bit of custom code to do it programmatically. The setup is simply shown below.

Now opening up the script task you can click on the … button next to the readonlyvariables and readwritevariables areas. For this sample, I am setting up the MyName variable to be read only and the YourName variable to read-write.

When completed the screen will show our two variables in the boxes as shown below.

Now it is a matter of simply making a call, like below, to access your variables. For my example I am simply going to display the values in some message boxes. Reading from the MyName variable and overwriting the YourName variable. Pretty simple.

The variables integration with the script tasks has gotten much better in SQL Server 2008 and I have yet to run into any problems like I have previously. Still, there may be some instances that you come across that you would rather programmatically access variables. For this the code below should do the trick. I have two functions: one for reading a variable and the other for writing to a variable. You will notice in both instances that you must lock the variable first before trying to access it. Think of it as SSIS's version of row locking. You want to make sure that you are getting the most accurate version of the variable at that point in time. What good would it do you,since things can run in parallel, if another task is updating the variable at the same time you are trying to read it?

Please note on the code above it is also possible to use something like the following for locking and getting the variable into your collection

But it includes an extra step so it is not the most elegant solution. Additionally, you may notice that in the Finally block. This is not required as Microsoft ‘promises' in their documentation that variables are automatically unlocked when the execution of the script task stops. So you can trust that will happen or call it explicitly like me just so that you will sleep better at night.

Ssis Script Task Message Box Variable C#

Hopefully, this helps out some of my colleagues out there that may be struggling finding a good outline of this in the documentation.

Ssis C# Script Task Variables

Cheers,
AJ