Search code examples
ssisssis-2012ssis-2008

How to compare the two table row count , if counts matches than ok if not matches this will restart the SSIS package

I have made the ssis package in which i made the data flow for incremental data. Source and destination server ip's are different. Below you can find the flow diagram of my packageControl flow diagram Data flow diagram the package is working fine . In the Execute SQl task :- it controls the log table and start the incremental task query which i used is :-

insert into audit_log (
Packagename,
process_date,
start_datetime,
end_datetime,
Record_processed,
status
)values('CRM-TO-TRANSORGDB',null,GETDATE(),null,null,null);

select MAX(ID) as ID,MAX(process_date) as proc_date  from audit_log where Packagename ='CRM-TO-TRANSORGDB' ;

store the ID and proc_date in the variable.

in the Execute SQl task 1:- it just update the log table.

UPDATE audit_log
SET
process_date=?,
end_datetime = GETDATE(),
status='SUCCESS'
record_processed=?
WHERE (packagename = 'CRM-TO-TRANSORGDB') AND ID=? ;

this is the query we have used to update the log table.

In the Data flow simple fetching the all the records and put in into the destination table. this all i have done .
But my question are:- 1) How to compare the total no. of row counts from the source table to destination table in ssis package. 2) if its doesn't matches than it will restart my task automatically.

@thomas as per your instruction i have done the following thing: 1) i have made the Execute SQl Task for source and destination . 2) and Add the Execute Package task and added the condition for not matching the count.

add the execute SQL Task for the source and destination

and added the expression for check row_count_src!= row_count_dest

and in Source_table_count i have used the below query:

select count(SubOrderID) as row_count_src from fact_suborder_journey 
WHERE Suborderdate between '2016-06-01' and GETDATE()-1 ;

in dest_table_count i have used the below query:

select count(SubOrderID) as row_count_dest from fact_suborder_journey 
WHERE Suborderdate between '2016-06-01' and GETDATE()-1 ;

i have added the two variable as int64 in ths ssis package. and map in the result set below you can find the pic what i have done. variable assignments Result_set

but After done all this this i am getting this error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "row_count_src": "The type of the value being assigned to variable "User::row_count_src" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

Solution

  • I havent tested this completely but you might be able to do something like this. This creates a loop of your packages and will executes as long as your count variables are different from each other.

    What have i done?

    1. First i have a DataFlow Task which moves data from source to destination.

    2. Then i have an Execute SQL task which basically counts all rows from TableA and maps it to variable count1 eg. Source table

    3. Then i have an Execute SQL task which basically counts all rows from TableB and maps it to variable count2 eg. Destination Table

    4. Then i create an Execute Package task where i reference it too it self. Then i make a precedence constraint with an expression saying Count1 != count2.

    Because if they are different you want to restart the task. If they are equal the last task Execute Package task will never be executed.

    Hope that is something like that?

    enter image description here