Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sent Mail task


Sent Mail task

Author
Message
annam.dba
annam.dba
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 189
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
Lempster
Lempster
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 Visits: 1657
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
annam.dba
annam.dba
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 189
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
Lempster
Lempster
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 Visits: 1657
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search