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
FatehbourghoudAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrichmonCommented:
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
FatehbourghoudAuthor Commented:
How to feed the results into an email task that emails the details you want of the selected records ?

Thanks for more details

mrichmonCommented:
You have several options, here are two

You can use a Data Flow task to export the rows to a flat file:
http://decipherinfosys.wordpress.com/2008/07/23/ssis-exporting-data-to-a-text-file-using-a-package/
Then use a Send Email task to send the file as an attachment.
If your Data Flow task uses a Row Count component to store the count into a variable, you can use it as a condition to not send the email (if the count is zero).

If you want to send a separate email per record, you could use a for-each loop, as in this example:
http://www.sqlis.com/sqlis/post/Shredding-a-Recordset.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
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;


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.