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?
2 Answers
I'd structure it like this
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
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.
first solution is : read excel file and load to data table ,then run query for compare two data table ,then send email . second solution is : when you read file by query select row count in bind in value1 and value2 then compare.
Related