SSIS Image Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

SSIS Image Problem

Author

Topic

baddulas
Starting Member

11 Posts

Posted - 2014-04-11 : 05:31:38

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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-11 : 10:21:01

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

baddulas
Starting Member

11 Posts

Posted - 2014-04-14 : 02:44:11

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/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-14 : 09:20:29

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

baddulas
Starting Member

11 Posts

Posted - 2014-04-14 : 11:05:08

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-14 : 12:55:35

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;

        }
    }
}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources