Avoid creating Empty flat file at the destination in SSIS

 

We have a requirement to generate the audit report when we are getting the less than 10% of the records from yesterday's load. The audit report has yesterday's record count and today's records count and variance of the counts. If the file has the expected record then in this case we don’t want to create the empty audit report. I mean we want to avoid the empty audit report. Also, we need to fail the package and not load it into the main table.

For example

If yesterday we load 20 records and today we get less than 18 records in this case we need to generate the Audit report like below.

Yesterday_record_Count	Today_record_Count	variance
20	15	5

 Let’s see how to achieve the say.

File data  

Table

create table sales_Stg ( SalesOrderNumber varchar(30), SalesAmount decimal(18,10), UnitPrice decimal(18,10), ExtendedAmount decimal(18,10), TaxAmt decimal(18,10), Created_Date date, created_by varchar(100) ) create table sales ( SalesOrderNumber varchar(30), SalesAmount decimal(18,10), UnitPrice decimal(18,10), ExtendedAmount decimal(18,10), TaxAmt decimal(18,10), Created_Date date, created_by varchar(100) )

First of all, we are loading the data into the staging table and then we compare the yesterday load and toady load then we can precede the load or audit report.

We have designed the below package

  

SQL_Delete_STG 

This task will truncate the Stage table before load the data.

DFT_Load_data_In_STG

  

SQL_Get_Record_Count   

declare  @Last_day_load int,                                  @Today_day_load int,                                  @Records_variance int,                                  @variance int,                                  @Generate_audit_rpt bit; set @Today_day_load=(select Count(*) from sales_stg where  Created_Date=cast(getdate() as date)) set @Last_day_load=(select Count(*) from sales where  Created_Date=cast(getdate()-1 as date))   Set @Records_variance = @Last_day_load-@Today_day_load set @variance= @Last_day_load *.9 Set @Generate_audit_rpt=0; if @Today_day_load < @variance Begin set @Generate_audit_rpt=1; select @Generate_audit_rpt end else begin set @Generate_audit_rpt=0;  select @Generate_audit_rpt end

 

With the help of this task, we will get the record variance and also set the variable.   

Storing the result in the variable.  

Based on the result we are redirecting.

If we find the variance in the record count we are redirecting to generate the audit report and failing the package. If not then we are loading the data into the main table.  

Redirecting to generate the report.    

In the source, we have written the below code

declare  @Last_day_load int,                                  @Today_day_load int,                                  @Records_variance int,                                  @variance int;   set @Today_day_load=(select Count(*) from sales_stg where  Created_Date=cast(getdate() as date)) set @Last_day_load=(select Count(*) from sales where  Created_Date=cast(getdate()-1 as date))   Set @Records_variance = @Last_day_load-@Today_day_load set @variance= @Last_day_load *.9 if @Today_day_load < @variance Begin select @Last_day_load as Yesterday_load_Count,@Today_day_load As today_Load_Count,@Records_variance as varince  end

 

After generating the report we are failing the package.

In the script task, we are writing the below code.

public void Main()                                 {            Dts.TaskResult = (int)ScriptResults.Failure;                                 }

 If there is no variance then we are loading the data into the main table.  

Before loading the data we are deleting the record of the current data if any data loaded.  

Delete from  sales where Created_Date=cast(getdate() as date)  insert into sales ( SalesOrderNumber ,SalesAmount,UnitPrice ,ExtendedAmount ,TaxAmt,Created_Date,created_by ) select SalesOrderNumber ,SalesAmount,UnitPrice ,ExtendedAmount ,TaxAmt,Created_Date,created_by from sales_Stg

 Our package is ready to run.

See yesterday load data (2021-03-13)   

We are running the package on 2012-03-14.

Files are having 15 records   

In this case, our package run and generates the audit report and fails the package.

Running the package.

                  

See the Audit report     

Now we are adding some records in the file and running this package.  

Now loading the file.

    

See the records in the main table.   

Hope this helps to avoid Generating empty files.