Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sent Mail task Expand / Collapse
Author
Message
Posted Wednesday, July 9, 2014 2:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 5, Visits: 90
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 Column Source DB Count Destination DB Count Updation 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
Post #1590635
Posted Wednesday, July 9, 2014 2:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:48 AM
Points: 2,128, Visits: 1,488
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
Post #1590642
Posted Wednesday, July 9, 2014 4:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 5, Visits: 90
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
Post #1590659
Posted Wednesday, July 9, 2014 5:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:48 AM
Points: 2,128, Visits: 1,488
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
Post #1590670
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse