T-SQL - Send mail driven by email ids from table

It is certainly possible to send emails using T-SQL by using sp_send_dbmail stored procedure which is present in msdb database. The earlier way of sending mail was by using SQL Mail. This is now being replaced with Database Mail in SQL Server 2005.

Step 1: Configure your smtp server. For this locate the Database Mail by connecting to the management studio Database engine.

Step 2: There will be a wizard opened for configuring the Database mail. Click appropriate radio button and click next. Here, we are creating a new profile so select first option.






Step 3: Configure your smtp server by entering the credentials and other required information. I have tested this piece using Gmail smtp configuration. Remember the account name. It will be used as the Profile name when using a TSQL query.

Step 4: Call the inbuild msdb.dbo.sp_send_dbmail stored procedure to send emails in our query. Assuming that the email ids are present in a table called ‘email_user’, the query sends mail to all the ids present in this table. There can be multiple conditions or joins applied to restrict the users. The resultset of a table is exported to a comma separated file with names based on current date and time.

A detailed help on sp_send_dbmail is available at

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

====================================================

Query:

declare @filename varchar(max)

declare @filepath varchar(max)

declare @emailids varchar(max)

declare @currDate datetime

set @emailids=''

set @currDate=getdate()

set @filepath='C:\\'

set @filename=@filepath+convert(varchar,Year(@currDate))+

convert(varchar,month(@currDate))+

convert(varchar,day(@currDate))+

convert(varchar,datepart(hh,@currDate))+

convert(varchar,datepart(mi,@currDate))+

convert(varchar,datepart(ss,@currDate))+'.txt'

select @emailids=@emailids+';'+email_address from test.dbo.email_user

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Your Profile',

@recipients = @emailids,

@subject = 'Sending Mail using Database Mail' ,

@body = 'Just testing the mail',

@query='SELECT convert(varchar(max),[EmployeeID])+'',''+

convert(varchar(max),[NationalIDNumber])+'',''+

convert(varchar(max),[ContactID])+'',''+

convert(varchar(max),[LoginID])+'',''+

convert(varchar(max),[ManagerID])+'',''+

convert(varchar(max),[Title])+'',''+

convert(varchar(max),[BirthDate])+'',''+

convert(varchar(max),[MaritalStatus])+'',''+

convert(varchar(max),[Gender])+'',''+

convert(varchar(max),[HireDate])+'',''+

convert(varchar(max),[SalariedFlag])+'',''+

convert(varchar(max),[VacationHours])+'',''+

convert(varchar(max),[SickLeaveHours])+'',''+

convert(varchar(max),[CurrentFlag])+'',''+

convert(varchar(max),[rowguid])+'',''+

convert(varchar(max),[ModifiedDate])

FROM [AdventureWorks].[HumanResources].[Employee]',

@attach_query_result_as_file = 1,

@query_attachment_filename= @filename,

@query_no_truncate=1

Different ways to migrate DTS to SSIS 2005

SQL SERVER INTEGRATION SERVICES (SSIS)
Today, I will state various ways to migrate DTS packages (.dts files) to SSIS 2005 packages (.dtsx files)

1) Using Package migration wizard exe "DTSMigrationWizard.exe" located in the following path:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\

A snapshot of the wizard is as follows:


2) Another way to migrate DTS packages is from the SSIS solution.
In the SSIS solution, right click Packages and select "Migrate package" option

3) If you don't want to migrate the DTS packages but want to use them in SSIS 2005, you can execute them from the "Execute DTS package task"

4) Also you can modify/create/execute DTS packages using SQL Server Mgmt Studio.
In SSMS, after connecting to Database engine, select Management->Legacy, right click on "Data Transformation Services" and select the appropriate option.
You must have the DTS Designer component installed to work on the DTS packages.
This can be downloaded from Microsoft site.

Methods 1),2) and 4) would be using Migration wizard to migrate DTS to SSIS 2005 packages.



All achievements, all earned riches, have their beginning in an idea.

Four steps to achievement:
Plan purposefully.
Prepare prayerfully.
Proceed positively.
Pursue persistently.

New features in SQL Server 2008

Microsoft has released SQL Server 2008.. Let's see what's new in it compared to previous version.
http://www.mssqltips.com/tip.asp?tip=1313





All achievements, all earned riches, have their beginning in an idea.

Four steps to achievement:
Plan purposefully.
Prepare prayerfully.
Proceed positively.
Pursue persistently.

All the information you need

Here, on my blog, I will try to post the technical aspects covering Microsoft BI Technologies. Feel free to post your comments and raise your questions.

Script in SSIS script task to download file via HTTP

Public Sub Main()
Dim myWebClient As WebClient
Dim remoteUri As String
Dim localFileName As String
Dim fireAgain As Boolean

Try
myWebClient = New WebClient()

' get the context from variables
'remoteUri = CStr(Dts.Variables("RemoteUri").Value)
'localFileName = CStr(Dts.Variables("LocalFileName").Value)
remoteUri = "http://www.msbicentral.com"
localFileName = "C:\\Multi-ValuedParameters.rdl"
' tell the user what we're downloading where
Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", localFileName, remoteUri), String.Empty, 0, fireAgain)

' do the actual download
myWebClient.DownloadFile(remoteUri, localFileName)

Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
' post the error message we got back.
Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub

More details can be found on this page:
http://www.eggheadcafe.com/community/aspnet/13/10053833/using-ssis.aspx

Difference between BULK INSERT and SQL Server Destination in SSIS

The Bulk Insert task uses the Transact-SQL BULK INSERT statement for speed when loading large amounts of data. The SQL Server destination uses logic similar to but even more optimized than the logic used by the SQL Server built-in BULK INSERT command. Because the SQL Server destination uses shared memory for maximum loading speed, the package containing the SQL Server destination must execute on the same server as the database engine. Both the Bulk Insert task and the SQL Server destination can specify options that would be specified in the Transact-SQL BULK INSERT statement. Using the SQL Server destination is faster than either the Bulk Insert task or using the OLE DB destination with one of the fast load options. The fast load option of the OLE DB destination works by using optional parameters of SQL Server's BCP (bulk copy) utility; these optional parameters include:

· Keep identity values from the imported data file or use unique values assigned by SQL Server.

· Retain a null value during the bulk load operation.

· Check constraints on the target table or view during the bulk import operation.

· Acquire a table-level lock for the duration of the bulk load operation.

· Specify the number of rows in the batch and the commit size. The number of rows in a batch defaults to 100.