Sent Mail task

  • Hi All,

    I am new to SSIS, I have a 39 tables in source and same tables in destination, The source and destination both are in SQL Server 2008 databases. So i have taken one sequence container in side container i have set to 39 data flow tasks. In data flow tasks i have put OLE DB Source and OLE DB destination, Based on created data daily i have moved records from source to destination.

    Now on this package i am ready to deploy the job so once package run successfully or failure case and how many records inserted and updated in destination tables, those information i need it on a mail. The information should be in table format like given below.

    Table Name Compare ColumnSource DB CountDestination DB CountUpdation Status

    abcd CreatedDt -- -- No data difference

    abcd_info CreatedDt -- -- No data difference

    com_info UpdatedDt -- -- Moved sucessfully

    daily_stats CreatedDt 1936 1936 Moved sucessfully

    abc_data CreatedDt 3872 3872 Moved sucessfully

    abcd_history CreatedDt -- -- Error

    Thanks & Regards,

    Babu

  • You'll need to use Rowcount transformations in each dataflow to store the count of extracted/inserted rows in a number of user-defined variables. You can then reference those variables in your Send Mail task.

    As you are new to SSIS, I recommend taking a look at Andy Leonard's 'Stairway to Integration Services' series of articles on this very website: http://www.sqlservercentral.com/stairway/72494/

    Regards

    Lempster

  • Hi Lempster,

    Rowcount transformations i can used between source and destination. where can i mention user-defined variables and where can i refer those variables in Send Mail task. Please let me know with screen shots if it passably. I am SQL DBA admin i don't know much in SSIS.

    Thanks,

    Babu

  • annam.dba (7/9/2014)


    Hi Lempster,

    Rowcount transformations i can used between source and destination. where can i mention user-defined variables and where can i refer those variables in Send Mail task. Please let me know with screen shots if it passably. I am SQL DBA admin i don't know much in SSIS.

    Thanks,

    Babu

    When you use a Rowcount transformation, you have to store the result (i.e. the row count) in a user-defined variable so set up a variable first (you may need to go to the View menu in BIDS and slect 'Variables' in order to be able to see/add variables) and then reference that variable when you use your Rowcount transformation.

    To use the same variable in a Send Mail task, you need to set the contents of the email message using an Expression.

    Sorry, I don't have the time to provide a detailed explanation with screenshots - have a look at the link I provided in my earlier post.

    Regards

    Lempster

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply