I am filling an Object variable with an Execute SQL Task and I want to use it in a Foreach Loop Container (Foreach ADO Enumerator), but the Foreach Loop stays empty. So I want to check the value of my Object variable. However debugging the package does not show me the value of Object variables. How can I see the content of my Object variable?
 |
| No (readable) value for Object variables |
Solution
A solution could be to use a Script Task after the Execute SQL Task to show the content of the Object variable. The script below shows the top (x) records in a MessageBox. The code doesn't need any changes. The only change that you could consider to make is changing the number of records to show in the MessageBox (see C# variable maxRows).
 |
| Getting content of Object variable |
1) Add a Script Script Task
Add a new Script Task to the surface of your Control Flow and connect it to your Execute SQL Task. Then edit the Script Task to provide one Object variable in the property ReadOnlyVariables or ReadWriteVariables. This should of course be the same Object variable as in your Execute SQL Task.
 |
| Provide one Object variable |
2) Edit Script
Make sure to select Microsoft Visual C# as Script Langugage and then hit the Edit Script button to open the Vsta environment. Then first locate the Namesspaces to add an using for System.Data.OleDb.
#region Namespaces using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.Data.OleDb; // Added #endregion
Then scroll down and located the Main method "public void Main()" and replace it with the code below.
public void Main()
{
// Show max number of data rows in a simgle messagebox
int maxRows = 3;
/////////////////////////////////////////////////////////////////////
// No need to change lines below
/////////////////////////////////////////////////////////////////////
// Create a table object to store the content of the object variable
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
DataTable myTable = new DataTable();
// Create message string to show the content of the object variable
string message = "";
string header = "Error";
// Five checks before looping through the records in the object variable
////////////////////////////////////
// 1) Is a variable provided?
////////////////////////////////////
if (Dts.Variables.Count.Equals(0))
{
message = "No read-only or read-write variables found";
}
////////////////////////////////////
// 2) Multiple variables provided
////////////////////////////////////
else if(Dts.Variables.Count > 1)
{
message = "Please provide only 1 read-only or read-write variable";
}
////////////////////////////////////
// 3) Is it an object variable?
////////////////////////////////////
else if (!Dts.Variables[0].DataType.ToString().Equals("Object"))
{
message = Dts.Variables[0].Name + " is not an Object variable";
}
////////////////////////////////////
// 4) Is it null or not an table?
////////////////////////////////////
else
{
try
{
// Try to fill the datatable with the content of the object variable
// It will fail when it is null or not containing a table object.
dataAdapter.Fill(myTable, Dts.Variables[0].Value);
}
catch
{
// Failing the third check
message = Dts.Variables[0].Name + " doesn't contain a usable value";
}
}
////////////////////////////////////
// 5) Is it containing records
////////////////////////////////////
if (myTable.Rows.Count > 0)
{
int j = 0;
// Loop through all rows in the dataset but don't exceed the maxRows
for (j = 0; j < myTable.Rows.Count && j < maxRows; j++)
{
// Get all values from a single row into an array
object[] valuesArray = myTable.Rows[j].ItemArray;
// Loop through value array and columnnames collection
for (int i = 0; i < valuesArray.Length; i++)
{
message += myTable.Rows[j].Table.Columns[i].ColumnName + " : " + valuesArray[i].ToString() + Environment.NewLine;
}
// Add an empty row between each data row
message += Environment.NewLine;
}
// Create header
header = "Showing " + j.ToString() + " rows out of " + myTable.Rows.Count.ToString();
}
else if (!message.Equals(""))
{
// Don't do anything
// Record count is 0, but an other validition already failed
}
else
{
// Record count is 0
message = Dts.Variables[0].Name + " doesn't contain any rows";
}
// Show message with custom header
MessageBox.Show(message, header);
Dts.TaskResult = (int)ScriptResults.Success;
}
Now close the Vsta environment and click on OK in the Script Task editor to finish it.3) The result
Now run the package to see the result. I tried to make it a bit monkey proof by adding some checks in the code. If you provide a good and filled variable then it will show the data. Otherwise it will show an error telling you what's wrong.
 |
| The result |
