0

SSIS/Sql Server/Visual Studio 2019.

I've got a big csv file that has a lot of iffy data. One thing I wanted to do was at least try to recognize sequential lines that are the same and kick the duplicates out. By "same" I wanted to check 2 specific column values - Name and Company.

I've got a flow that looks like it should work (given this is my first SSIS package and limited experience) but does not catch the sequential duplicates. I don't know if that's because I'm not undestanding the Conditional Split expression syntax, the way a Script Component works, or when/how package variable changes are supposed to take effect from Script components. Nobody is throwing errors, but the rows from the file that are sequential duplicates are not getting sent down the "rejects" chute.

I created two package variables (lastName and lastCompany), initialized to "".

The flow has a Flat file source reading in, and then a Conditional Split with, among other clauses in the expression,

... && !(Name == @[User::lastName] && Company == @[User::lastCompany]) &&

The idea was anything matching all the clauses would go on to the "success" path and the rows failing any of these checks would go to the "rejects" flow.

The next thing on the "success" flow is a Script Component that does some data normalization and (at least tries to) update the package variables. I found another oflow article for the template.

public override void PostExecute()
{
    base.PostExecute();

    // I've been trying to debug this and get some output on the VS output window but so far nothing has worked.
    // Trace is not showing up in DbgView, FireInformation and FireWarning didn't show up in any of the windows in VS
    Trace.WriteLine($"Starting: [{Variables.lastName}|{Variables.lastCompany}], Ending: [{lastName}|{lastCompany}]");

    Variables.lastCompany = lastCompany;
    Variables.lastName = lastName;
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    ...
    lastName = Row.Name;
    lastCompany = Row.Company;
}

What am I missing about

  1. The persistence of package variables and updates from script components?
  2. Conditional Split expression behavior?

I mean, it looks like it should work to me, but it's not.

Thanks

1
  • I would be tempted to load the csv to a staging table and use SQL Server Window functions (ex: row_number) to identify duplicates. A T-SQL approach would also address the potential that duplicates may not be sequential.
    – jim
    Jan 8, 2022 at 14:03

1 Answer 1

0

SSIS Variable values can only be modified in the OnPreExecute and the OnPostExecute phase of pipeline execution. During execution, they are static.

You can verify this behavior by adding a column to the dataflow with a Derived Column Task and use add your variable @[User::lastName] after the Script Task. You'll see value never changes between the first and final rows.

Therefore, you have a logical error with this snippet

(Name == @[User::lastName] && Company == @[User::lastCompany])

So, what do you do?

The biggest challenge of course is you have a 330M row file.

If we only care about sequential duplicates, then a script task as you designed is mostly correct except trying to assign to the SSIS Variables. Instead, you'll need to add column(s) to your data flow so your subsequent Conditional Split can make a logical choice. For normal work, I'd be fine with adding a lastName and lastCompany to the data buffer. However, given that you're going to be working in large sets of data, you need to fight for every scrap of memory you can get. I would instead perform the comparison logic in your Script Task and simply emit a boolean indicating whether this row is a duplicate of the preceding row.

I would have a Script Task, acting as a transformation sitting after your File source. It would take as readonly input columns Company and Name and it would add a new column to the output called IsDuplicate

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // member variables to keep track of previous row data
    private string lastName;
    private string lastCompany;

    public override void PreExecute()
    {
        base.PreExecute();
        // initialize our variables
        this.lastCompany = "";
        this.lastName = "";
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Encapsulate the logic from the Derived Column comparison here 
        //
        // Are the two elements the same?
        // Do we have to worry about nulls here? Assuming not
        // Also assuming a case sensitive match is desired
        if (Row.Company == this.lastCompany && Row.Name == this.lastName)
        {
            Row.IsDuplicate = true;
        }
        else
        {
            // strictly speaking, this is not required as the default value for a bit if false but I favor explicit behaviour
            Row.IsDuplicate = false;
        }

        // Update the member variables to the previous row's data
        this.lastName = Row.Name;
        this.lastCompany = Row.Company;
    }
}

Now you can simplify your Conditional Split to the condition of our column IsDuplicate. I route any rows matching that to an output called "Sequential Duplicate" and my default path is called "First or non-duplicate rows". Again, I try to be as obvious with my intention so I can get my brain back into the game when I have to maintain it.

enter image description here

That results in my data flow looking like

enter image description here

1
  • That was it! Thank you for taking the time to respond so thoroughly Jan 11, 2022 at 2:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.