SSIS 作业 发邮件 两个重要环节

1.VBSCRIPT 接受变量发送（有两个变量是接收ssis包传过来的 AttachName ，EmailAddress）

 

 

Public Sub Main()
		'
		' Add your code here
		'
        Dim myHtmlMessage As MailMessage
        Dim mySmtpClient As SmtpClient
        Dim myAttachment As Attachment
        Dim myAttachmentName As String
        Dim myEmailAddress As String


        myAttachmentName = Dts.Variables("AttachName").Value.ToString()
        myEmailAddress = Dts.Variables("EmailAddress").Value.ToString()

        myHtmlMessage = New MailMessage("data@114study.com", myEmailAddress, "频道访问分布表", "频道访问分布表")

        myAttachment = New Attachment(myAttachmentName)

        myHtmlMessage.Attachments.Add(myAttachment)

        mySmtpClient = New SmtpClient("smtp.114study.com")
        mySmtpClient.Credentials = New System.Net.NetworkCredential("data", "111111")
        mySmtpClient.Send(myHtmlMessage)



        Dts.TaskResult = Dts.Results.Success
	End Sub

sql脚本

 

DECLARE @CmdString Varchar(100) 
Declare @filename varchar(50)

set @filename = 'E:/SSIS/Excel/' + convert(varchar(10),getdate(),121) + '.xls'

SET @CmdString = 'copy E:/SSIS/Excel/template/1.xls '+@filename
exec master..xp_cmdshell @CmdString


declare @cmd varchar(1000)
declare @ssispath varchar(1000)
declare @connstring varchar(1000) 
set @ssispath = 'E:/SSIS/Package.dtsx'
set @connstring = '"""Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@filename+';Extended Properties="Excel 8.0;HDR=YES";"""' 
---/de means the encrypt password
select @cmd = 'dtexec /F "' + @ssispath + '" /De "111111"'
select @cmd = @cmd + ' /SET /Package.Variables[User::ExcelFileName].Properties[Value];' + @connstring + '' 
select @cmd = @cmd + ' /SET /Package.Variables[User::EmailAddress].Properties[Value];"yanxiang@114study.com"' 
select @cmd = @cmd + ' /SET /Package.Variables[User::AttachName].Properties[Value];"'+@filename+'"' 

exec master..xp_cmdshell @cmd

 

两步联合及可定时发从数据库提取数据定时发到指定邮箱中