SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 SSIS Image Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

baddulas
Starting Member

India
11 Posts

Posted - 04/11/2014 :  05:31:38  Show Profile  Reply with Quote
Hi All,

Please help on the following issue.

I want to send a email to End users with image.

How can we achieve this using SSIS.

Which approach is best for the above Scenario.

Thanks in Advance...

gbritton
Flowing Fount of Yak Knowledge

1130 Posts

Posted - 04/11/2014 :  10:21:01  Show Profile  Reply with Quote
http://www.mssqltips.com/sqlservertip/1731/sending-email-from-sql-server-integration-services-ssis/
Go to Top of Page

baddulas
Starting Member

India
11 Posts

Posted - 04/14/2014 :  02:44:11  Show Profile  Reply with Quote

Hi,

The below code in VB.I need C# Coding.

quote:
Originally posted by gbritton

http://www.mssqltips.com/sqlservertip/1731/sending-email-from-sql-server-integration-services-ssis/




Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1130 Posts

Posted - 04/14/2014 :  09:20:29  Show Profile  Reply with Quote
quote:
Originally posted by baddulas


Hi,

The below code in VB.I need C# Coding.

quote:
Originally posted by gbritton

http://www.mssqltips.com/sqlservertip/1731/sending-email-from-sql-server-integration-services-ssis/





Translating snippets of VB.NET to C# is usually no biggy. If you can't do it yourself. post the code you want translated
Go to Top of Page

baddulas
Starting Member

India
11 Posts

Posted - 04/14/2014 :  11:05:08  Show Profile  Reply with Quote
Hi Britton,

I am want below code in C#
---------------------------------
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.Net.Mail
Imports System.Data.SqlClient
Imports System.Xml
_
_
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dim mySmtpClient As SmtpClient
Dim ConnString As String
ConnString = "Data Source=BADDULA-LAPTOP;Initial Catalog=Learning;Integrated Security=True;"
Try
Dim SqlQuery As String = "GetMailsToBeSent"
Using conn As New SqlConnection(ConnString)
conn.Open()
Dim comm As New SqlCommand(SqlQuery, conn)
comm.CommandType = CommandType.StoredProcedure
Dim adap As New SqlDataAdapter(comm)
Dim ds As New DataSet()
adap.Fill(ds)
If ds.Tables(0).Rows.Count > 0 Then
Dim intCount As Integer
Dim intCCCounter As Integer
Dim intBCCCounter As Integer
For intCount = 0 To ds.Tables(0).Rows.Count - 1
'Create an instance of MailMessage class and pass "From EmailID" and "To EmailID" to the constructor
'"To" list can accept multiple email address deliminated by comma
Using myMessage As New MailMessage(ds.Tables(0).Rows(intCount).Item("From").ToString(), Replace(ds.Tables(0).Rows(intCount).Item("To").ToString(), ";", ","))
'CC List
'MailMessage.CC property is a collection of type MailAddressCollection so you need to add one email address at a time in this collection as I am doing it in a loop for multiple address
'Also you can create a MailAddressCollection first, add all the CC email address to it and finally assign it to MailMessage.CC property
Dim CCAddressList As MailAddress
If ds.Tables(0).Rows(intCount).Item("Cc").ToString().Length > 0 Then
If ds.Tables(0).Rows(intCount).Item("Cc").ToString().IndexOf(";") > 0 OrElse ds.Tables(0).Rows(intCount).Item("Cc").ToString().IndexOf(",") > 0 Then
Dim strEmails As String()
strEmails = Split(Replace(ds.Tables(0).Rows(intCount).Item("Cc").ToString(), ";", ","), ",")
For intCCCounter = 0 To strEmails.Length - 1
CCAddressList = New MailAddress(strEmails(intCCCounter))
myMessage.CC.Add(CCAddressList)
Next
Else
CCAddressList = New MailAddress(Replace(ds.Tables(0).Rows(intCount).Item("Cc").ToString(), ";", ","))
myMessage.CC.Add(CCAddressList)
End If
End If
'BCC List
'MailMessage.BCC property is a collection of type MailAddressCollection so you need to add one email address at a time in this collection as I am doing it in a loop below for multiple address
'Also you can create a MailAddressCollection first, add all the BCC email address to it and finally assign it to MailMessage.BCC property
Dim BCCAddressList As MailAddress
If ds.Tables(0).Rows(intCount).Item("Bcc").ToString().Length > 0 Then
If ds.Tables(0).Rows(intCount).Item("Bcc").ToString().IndexOf(";") > 0 OrElse ds.Tables(0).Rows(intCount).Item("Bcc").ToString().IndexOf(",") > 0 Then
Dim strEmails As String()
strEmails = Split(Replace(ds.Tables(0).Rows(intCount).Item("Bcc").ToString(), ";", ","), ",")
For intBCCCounter = 0 To strEmails.Length - 1
BCCAddressList = New MailAddress(strEmails(intBCCCounter))
myMessage.Bcc.Add(BCCAddressList)
Next
Else
BCCAddressList = New MailAddress(Replace(ds.Tables(0).Rows(intCount).Item("Bcc").ToString(), ";", ","))
myMessage.Bcc.Add(BCCAddressList)
End If
End If
myMessage.Subject = ds.Tables(0).Rows(intCount).Item("Subject").ToString()
myMessage.Body = ds.Tables(0).Rows(intCount).Item("Body").ToString()
If ds.Tables(0).Rows(intCount).Item("IsHTMLFormat").ToString().ToUpper() = "TRUE" Then
myMessage.IsBodyHtml = True
Else
myMessage.IsBodyHtml = False
End If
If ds.Tables(0).Rows(intCount).Item("Priority").ToString().ToUpper() = "L" Then
myMessage.Priority = Mail.MailPriority.Low
ElseIf ds.Tables(0).Rows(intCount).Item("Priority").ToString().ToUpper() = "H" Then
myMessage.Priority = Mail.MailPriority.High
Else
myMessage.Priority = Mail.MailPriority.Normal
End If
'To be used for sending attachements
'myMessage.Attachments.Add(New Attachment("c:\example1.txt"))
'myMessage.Attachments.Add(New Attachment("c:\example2.txt"))
mySmtpClient = New SmtpClient("smtpserver")
'You can set the SMTP port number if it is not listening on default port
'mySmtpClient.Port = 26
'The credentials returned by DefaultNetworkCredentials represents the authentication credentials for the current security context in which the application is running.
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
'If you don't want to use windows authentication to connect to your SMTP host and want to specify a different username and password, you can then use NetworkCredential class as shown below
'The NetworkCredential class is a base class that supplies credentials in password-based authentication schemes such as basic, digest, NTLM, and Kerberos.
'mySmtpClient.Credentials = New NetworkCredential("arshad", "abcd", "corpnet")
mySmtpClient.Send(myMessage)
End Using
Next
End If
conn.Close()
End Using
Catch E As Exception
Dts.Events.FireError(-1, "Failure in Script Task while sending mails.", E.Message.ToString(), "", 0)
Dts.TaskResult = ScriptResults.Failure
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
End Class

Thanks In Advance
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1130 Posts

Posted - 04/14/2014 :  12:55:35  Show Profile  Reply with Quote
Pretty much a direct (untested) translation.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.Net.Mail;

namespace VB_TO_CS
{
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    {

        enum ScriptResults {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
       }

        void Main(string[] args)
        {
            SmtpClient mySmtpClient;
            string ConnString = "Data Source=BADDULA-LAPTOP;Initial Catalog=Learning;Integrated Security=True;";

            try
            {
                var SqlQuery = "GetMailsToBeSent";
                using (var conn = new SqlConnection(ConnString))
                {
                    conn.Open();
                    var comm = new SqlCommand(SqlQuery, conn);
                    comm.CommandType = CommandType.StoredProcedure;
                    var adap = new SqlDataAdapter(comm);
                    var ds = new DataSet();
                    adap.Fill(ds);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        int intCount, intCCCounter, intBCCCounter;
                        for (intCount = 0; intCount <= ds.Tables[0].Rows.Count - 1; intCount++)
                        {
                            using (var myMessage = new MailMessage(
                                   ds.Tables[0].Rows[intCount]["From"].ToString(),
                                   ds.Tables[0].Rows[intCount]["From"].ToString().Replace(';', ','))
                                   )
                            {
                                MailAddress CCAddressList;
                                if (ds.Tables[0].Rows[intCount]["Cc"].ToString().Length > 0)
                                {
                                    var strEmails = ds.Tables[0].Rows[intCount]["Cc"].ToString().Replace(';', ',').Split(',');
                                    foreach (var emailAddr in strEmails)
                                    {
                                        CCAddressList = new MailAddress(emailAddr);
                                        myMessage.CC.Add(CCAddressList);
                                    }
                                } else{
                                    CCAddressList = new MailAddress(ds.Tables[0].Rows[intCount]["Cc"].ToString().Replace(';', ','));
                                    myMessage.CC.Add(CCAddressList);
                                }

                                MailAddress BCCAddressList;
                                if (ds.Tables[0].Rows[intCount]["Bcc"].ToString().Length > 0)
                                {
                                    var strEmails = ds.Tables[0].Rows[intCount]["Bcc"].ToString().Replace(';', ',').Split(',');
                                    foreach (var emailAddr in strEmails)
                                    {
                                        BCCAddressList = new MailAddress(emailAddr);
                                        myMessage.Bcc.Add(BCCAddressList);
                                    }
                                }
                                else
                                {
                                    BCCAddressList = new MailAddress(ds.Tables[0].Rows[intCount]["Bcc"].ToString().Replace(';', ','));
                                    myMessage.Bcc.Add(BCCAddressList);
                                }

                                myMessage.Subject = ds.Tables[0].Rows[intCount]["Subject"].ToString();
                                myMessage.Body = ds.Tables[0].Rows[intCount]["Body"].ToString();
                                if (ds.Tables[0].Rows[intCount]["IsHTMLFormat"].ToString().ToUpper() == "TRUE")
                                {
                                    myMessage.IsBodyHtml = true;
                                }
                                else
                                {
                                    myMessage.IsBodyHtml = false;
                                }

                                if (ds.Tables[0].Rows[intCount]["Priority"].ToString().ToUpper() == "L")
                                    myMessage.Priority = MailPriority.Low;
                                if (ds.Tables[0].Rows[intCount]["Priority"].ToString().ToUpper() == "H")
                                    myMessage.Priority = MailPriority.High;
                                else
                                    myMessage.Priority = MailPriority.Normal;

                                mySmtpClient = new SmtpClient("smtpserver");
                                mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials;
                                mySmtpClient.Send(myMessage);
                            }

                        }
                    }

                    conn.Close();
                }
            }
            catch (Exception e)
            {
                Dts.Events.FireError(0, "Script Task Example", "Internet connection not available.", String.Empty, 0);
                Dts.Events.FireError(-1, "Failure in Script Task while sending mails.", e.Message.ToString(), "", 0);
                Dts.TaskResult = (int) ScriptResults.Failure;
            }
            Dts.TaskResult = (int) ScriptResults.Success;

        }
    }
}


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000