I have one table called Product.
The product table have more records, Some time the table not have record.
So i want to check the product table,
if it's have the records i send all table information as mail.
if it's not have record no need to send mail.
Please help me.
You can apply different approach.. Just execute a SQL Database task from SSIS to execute SP "only if you specifically want SSIS step to be executed" (or you can just create SPand schedule it in job scheduler that it). and inside the SP you can send email. in case ,if you dont know how to setup email profile refer this (https://www.codeproject.com/Articles/485124/Configuring-Database-Mail-in-SQL-Server)
example as below -
Step-1 : Create a Object data type variable which will hold the result set of a given table and a string type variable to hold the recipient email IDs.
Step-2 : Use a Execute SQL Task Editor and choose the Result set option as Full result set and a query statement to fetch the data from a given table. Use the object variable to hold the result set of your table as shown in screen shots below:
Step-3 :Take a Script task Editor and use
Recepient_email_id
variable as read only variable and Use the belowC#
script to send an email.Please note : You need to change the sender email id, email subject and server IP address for the below statement in above script.
Below is the format of email which you receive.
Here is one possible option. Following example might give you an idea of how you can achieve emailing a result set using
Send Email task
. This example shows how to loop through the query result set to form the message body that will then be emailed usingSend Email task
.If you don't want to send e-mail when the result set is blank, you can add an
Expression
to the precedence constraint between Loop resultset and Send email tasks.The example uses SSIS 2008 R2 and SQL Server 2008 R2 database.
Step-by-step process:
Create a table named
dbo.EmailData
using the script provided under SQL Scripts section.Screenshot #1 shows sample data that
Execute SQL
task will query and send it in an e-mail in this example.On the SSIS package, create 5 variables as shown in screenshot #2.
On the SSIS package, place the following tasks:
Execute SQL task
,Foreach loop container
,Script task
within the Foreach loop container andSend Email task
.Configure the
Execute SQL task
as shown in screenshots #3 and #4.Configure the
Foreach loop container
as shown in screenshots #5 and #6. Variable mappings section shows the order in which the query result columns appear and how they are assigned to SSIS variables. These variables will be used to form the email message inside theScript task
.In the
Script task
, replace the code with the one shown under the Script task code section. The script task has very simple plain text email message formatting.Configure the Send Email task as shown in screenshot #7. You need to configure it with valid email address in From and To fields.
After configuring the Control flow tasks, your package should look like as shown in screenshot #8.
Sample package execution is shown in screenshot #9.
E-mail sent by the package is shown in screenshot #10. Some information have been removed from the screenshot. You can compare the table data shown in screenshot #1 with this email output and they should same.
Hope that helps.
SQL Scripts: .
Script task code:
C# code that can be used only in
SSIS 2008 and above
. .Screenshot #1:
Screenshot #2:
Screenshot #3:
Screenshot #4:
Screenshot #5:
Screenshot #6:
Screenshot #7:
Screenshot #8:
Screenshot #9:
Screenshot #10: