Category: Data Design

I would like to get data out of Object variable in Script task or Script component. My object variable name is VersionList;

If we have used OLE DB to load data into Object variable, we need to use this;

// Set up the DataAdapter to extract the data, and the DataTable object to capture those results. This will write the contents of the Recordset to a new DataTable object.
// Create the connector
OleDbDataAdapter da = new OleDbDataAdapter();       

// Create the output DataTable
DataTable dt = new DataTable();                     

// Load the DataTable
da.Fill(dt, Dts.Variables["VersionList"].Value);       

// Now, replace the value in the VersionList variable. This SSIS variable will now contain a DataTable object filled with the results extracted from the ADO Recordset

Dts.Variables["VersionList"].Value = dt;

If we have used ADO.NET to load data into Object, then we need to use this;

 try
            {
                var ds = Dts.Variables["User::VersionList"].Value as DataSet;
                DataTable dt = new DataTable();                     
                // Create the output DataTable
                dt.TableName = "VersionList";
                // Now, replace the value in the VersionList object variable. This SSIS variable will now contain a DataTable object filled with the results extracted from the ADO Recordset
                dt = ds.Tables[0];
                Dts.Variables["User::VersionList"].Value = dt;

                Dts.TaskResult = (int)ScriptResults.Success;

            }
            catch (Exception ex)
            {
                var message = $"Error comparing version numbers. ERR: {ex.Message.ToString()}";
                //MessageBox.Show(message);
                Dts.Events.FireError(1, ex.TargetSite.ToString(), message, "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

We can use it in script component like this;

//get version and rules from VersionList variable
DataTable dt = (DataTable)Variables.VersionList;
//iterate over rows
foreach (DataRow row in dt.Rows)
{
   string message = $@"Details: {row["VersionNumber"].ToString()} - {row["VersionCode"].ToString()}
                           - {row["TabName"].ToString()} - {row["CellRangeCode"].ToString()} 
                            - {row["CellRange"].ToString()}";
   MessageBox.Show(message);
}

If for some reasons we are using Foreach loop and keep referring to same record set, then best design is to keep it ouside Foreach loop. An example is this;

We are getting VersionList from (1). We convert it to a data table in (2). We use it inside data flow task (3) of Foreach loop container.

Open a script task or script component. Press F4 to open properties windows. You will find target .NET framework;

My input is a Report Date. I would like to create a collection object of DateTime using Script task with this input. There is no Data Flow in this work flow. Later, I will be using this collection object in a Foreach loop container.

The object type of a package variable is really ADO dataset. This is finished package;

Variables window

Task A – SQLT – Report Date Lookup

Task B – SCPT – Build Pending Report Dates

private static DataTable MakeDateDataTableFor(DateTime reportDate)
        {
            //create a new Data Table
            DataTable table = new DataTable();
            //Declare variables for DataColumn and DataRow objects
            DataRow row;
            DataColumn column;

            //Create a new DataColumn and set it's data type
            column = new DataColumn();
            column.DataType = System.Type.GetType("System.DateTime");
            column.ColumnName = "ReportDate";
            column.ReadOnly = true;
            table.Columns.Add(column);

            //Create three new DataRow objects and add them to the DataTable
            for (int i=0; i <=2; i++)
            {
                row = table.NewRow();
                row["ReportDate"] = reportDate.AddDays(i);
                table.Rows.Add(row);
            }
            return table;
        }

             /// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
             public void Main()
             {
            // TODO: Add your code here
            var reportDate = (DateTime)Dts.Variables["User::ReportDate"].Value;
            //Create ADO Data Table object
            DataTable table = MakeDateDataTableFor(reportDate);
            Dts.Variables["User::ReportDateCollection"].Value = table;

                    Dts.TaskResult = (int)ScriptResults.Success;
             }

Task C – FELoop – Data processing for each date

Task D – SCPT – Show pending dates

public void Main()
             {
            // TODO: Add your code here
            MessageBox.Show(Dts.Variables["User::ReportStartDate"].Value.ToString());
                    Dts.TaskResult = (int)ScriptResults.Success;
             }

Resources

https://www.blogger.com/blog/post/edit/2414567817322296815/3075025082999390053

You can read more about Data Tables here;

https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable?redirectedfrom=MSDN&view=net-5.0

I would like to continue a Foreach loop container even if a task, for example, script task fails. Here is a quick fix;

Press F4, this will bring up Properties window. Change “ForceExecutionResult” property to “Success”.

Foreach loop will continue even if script task inside fails.

The recommended way to get a good error message from script task, decorate them with try/catch block. Here is an example;

try
{
   var x = 1/0;
}
catch (Exception ex)
            {
                var message = $"my error. ERR: {ex.Message.ToString()}";
                Dts.Events.FireError(1, ex.TargetSite.ToString(), message, "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;        
            }

These are good references on the topic;

https://www.mssqltips.com/sqlservertip/3575/continue-a-foreach-loop-after-an-error-in-a-sql-server-integration-services-package/

https://richardswinbank.net/ssis/errors_in_script_tasks_and_components