Month: August 2021

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