Sending results of import via email

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 2005 Forums

SSIS and Import/Export (2005)

Sending results of import via email

Author

Topic

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-12 : 06:33:30

When I used the following code:

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net
Imports System.Data
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline
Imports System.Text

Public Class ScriptMain
Public Sub Main()
Dim msg As MailMessage
Dim smtp As SmtpClient

Dim dt As Data.tbl_clients
Dim ad As New Data.OleDb.OleDbDataAdapter
dt = New System.Data.tbl_clients
ad.Fill(dt, Dts.Variables("User::AllFilesFound").Value)

Dim data As String

For Each row As Data.DataRow In dt.Rows
For Each column As Data.DataColumn In dt.Columns
data = data & (column.StaffNo & " : " & row(column.Ordinal).ToString()) & "<br>"
Next
Next

Dim sendNotify As String
sendNotify = "<pre>Cards to be printed " & Today() & " : <br><br>" & data.ToString() & ""

msg = New MailMessage( _
"my email", _
"my email again", _
"Subject: Cards to be printed", sendNotify)
smtp = New SmtpClient("smtp client")
smtp.Credentials = CredentialCache.DefaultNetworkCredentials
msg.IsBodyHtml = True
smtp.Send(msg)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class

I got the following error:
The query failed to parse. An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

I would like to send an email notification after data has been imported into the table. Any response will be highly appreciated.

Many thanks,

Rupa

nduggan23
Starting Member

42 Posts

Posted - 2007-11-12 : 06:39:31

You problem doesnt reside with the email function. Check your sql and your error message. its saying you are not declaring your select into statement correctly. should be something like: select c1, c2 INTO x from y

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-12 : 07:57:36

Thanks nduggan..The 'Send Mail Task' works for me :-) I shall use that now!!

Many thanks,

Rupa

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2007-11-13 : 11:23:14

Rupa,

Why not use sp_send_dbmail? Also, how are you using Send Mail Task to send query results, I want to learn that. =)

http://msdn2.microsoft.com/en-us/library/ms190307.aspx

Rupa
Posting Yak Master

123 Posts

Posted - 2007-11-21 : 04:56:09

Sorry about the late reply igorblackbelt. I don't get email notifications when someone replies to my post.

Use a Data Flow Task that will use a select statement from OLE DB Source and that would be sent to a Flat File Destination (.csv file) and then use Send Mail Task to send that file as an attachment. It's really simple and works great :-)

Let me know if you're unsure about anything.

Rupa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources