asked on

How To compare two fields value using SSIS ( using SQL Task or C# Script Task)

HI ;

I have Table Audit with the folllowing design

CREATE TABLE [dbo].[SSIS_AuditFiles](
      [PackageID] [varchar](100) NULL,
      [PackageName] [varchar](100) NULL,
      [_Date] [smalldatetime] NULL,
      [Banner] [varchar](1) NULL,
      [FileName] [varchar](8) NULL,
      [NbRecord] [int] NULL,
      [NbRecordQty] [int] NULL,
      [NbCtlRecord] [int] NULL
) ON [PRIMARY]

I need using SSIS to compare the NbRecord VS NbCtlRecord fields value , if the records number is the same Ok else I have to send email to notify the issue

Can you please show me how to do that

Regards

mrichmon

Have a SQL task to select the records that need to be emailed
SELECT * FROM [dbo].[SSIS_AuditFiles] WHERE [NbRecord] <> [NbCtlRecord]

Then feed the results into an email task that emails the details you want of the selected records

Fatehbourghoud

ASKER

How to feed the results into an email task that emails the details you want of the selected records ?

Thanks for more details

ASKER CERTIFIED SOLUTION

mrichmon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

Or you could simply send it out as an attachment:

declare @report_file_name varchar(30),
            @sql varchar(max),
            @emailbody varchar(4000)

      set @sql = N'SET NOCOUNT ON
            SELECT * FROM [dbo].[SSIS_AuditFiles] WHERE [NbRecord] <> [NbCtlRecord]'

      SET @report_file_name = 'file_list_at_' + convert(varchar(10),getdate(), 112) + '.txt'
      SET @emailbody = 'Here is the list you wanted as today(yyyy.mm.dd): '+ convert(varchar(10),getdate(), 102)
      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'yourSQLMAil smtp', -- mail profile here
            @recipients='mail@mailserver.com',
            @subject = 'File Content',
            @body = @emailbody,
            @body_format = 'TEXT',
            @query = @sql,
            @attach_query_result_as_file = 1,
            @execute_query_database = 'YourDB', -- your database name here
            @query_attachment_filename = @report_file_name,
            @query_result_header = 1,
            @query_result_separator = '      ',
            @query_result_width = 1000;