Search code examples
excelssiscountcomparisonrow

I want to compare the row count of two different Excel files in SSIS get an email alert

I am looking for a way to compare the row count of two Excel files in SSIS, and if the row count of one of the files is >= the row count of the second, I would like to receive an email informing me of this. Is this something I can do in Visual Studio, and if so, how?

Solution

  • I'd structure it like this

    enter image description here

    I have 4 SSIS variables defined. Two of them will be used in the data flows to capture the amount of rows generated from the sources.

    The other two have Expressions applied to them to calculate values.

    @[User::RowCountFile1] >  @[User::RowCountFile2]

    That generates a true/false value that I will use in Send Email to determine whether there is any work (email) to be done.

    Since I'm lazy, I also used an Expression to generate the body of the email

    "The value of  File1 is " +  (DT_WSTR,20) @[User::RowCountFile1] + " and File2 is " +  (DT_WSTR,20) @[User::RowCountFile2]

    Both data flow tasks look like this

    enter image description here

    The final configuration is to add an Expression to the Send Email task and change the Disable property to be driven by the our @[User::IsFile1BiggerThan2] variable.