My Point of View – Page 80 – Source, Ingest, Prepare, Analyze and Consume

SSIS Project Target .NET Framework

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

Using SSIS Script task with Object variable and ADO Data Set

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

Continue Foreach loop after task failure in SSIS

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

Cross Apply and Outer Apply

Microsoft has introduced APPLY operator in SQL Server 2005. It allows joining between two table expressions, for example joining left/outer table expression with right/inner table expression. The way it works is that we have a table-valued expression on the right side and we want this table-valued expression to be evaluated for each row from the left table expression.

An ideal use case would be where we are unable to form any kind a join between two tables. For example a project table and calendar table.

(Left Side Table – Project Table)

ProjectKey ProjectEndDate

XX-ABC10 2018-10-31

XX-ABC11 2018-11-30

XX-ABD12 2019-01-31

XX-ABC13

(Right Side Table – Calendar Table)

FiscalQuarterStartDate FiscalQuarterEndDate

2018-10-01 2018-12-31

2019-01-01 2019-03-31

Each ProjectEndDate falls between a FiscalQuarter. We need to append a new column in our result set that will be FiscalQuarterEndDate.

Here is the query;

SELECT p.ProjectKey, p.ProjectEndDate, dates.FiscalQuarterEndDate 
FROM Project p
CROSS APPLY
(
	SELECT FiscalQuarterEndDate 
	FROM FiscalCalendar calendar
	WHERE 1=1
	AND p.ProjectEndDate 
	BETWEEN calendar.FiscalQuarterStartDate AND calendar.FiscalQuarterEndDate
) dates
WHERE 1=1

Here is the result;

ProjectKey ProjectEndDate FiscalQuarterEndDate

XX-ABC10 2018-10-31 2018-12-31

XX-ABC11 2018-11-30 2018-12-31

XX-ABD12 2019-01-31 2019-03-31

If we look at the result set, it returns only those rows that matches with the right table expression. Project Number XX-ABC13 is missing. CROSS APPLY is equivalent to an INNER JOIN. To be more precise its like a CROSS JOIN with a correlated sub-query).

If we want to return all rows from the left side then we need to apply OUTER APPLY which is equivalent to a LEFT OUTER JOIN.

Resources

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/