Problem: We need to send email to multiple recipients that are not static and will be changing.
Solution: Below is how our control flow will look
Step1. First we will create a table and populate the table with Emails
create table EmailRecipient
ID int identity(1,1)
We will create two variables as shown below
Step 3. We will start configuring components in the control flow starting with Execute SQL Task. Create an OLE DB connection manager to be included here.
Resultset: Full result set
Step 3. Configure Foreach Loop Container as shown below
Step 4: Configure Script Task
Below will be the one line code inside Script task
public void Main()
// TODO: Add your code here
Dts.Variables["ToLine"].Value = Dts.Variables["ToLine"].Value + "; " + Dts.Variables["TempString"].Value;
Dts.TaskResult = (int)ScriptResults.Success;
After including SMTP Connection Manager to the project, configure Send Mail Task
Since the recepients to whom email will be sent to are configured in Script Task, ToLine will be mapped to the variable
Often we are required to split the file based on various conditions and then either perform several actions to it or send the file to different departments. In this article, we will assume a real world scenario where in a company say ABC Inc. has different departments. The company has HR database where information of all the employees are stored. As an SSIS developer, we are required to collect the Employee details and send to employee's department when his department is "Manufacturing" and his hire date is before Jan 1st 2000. [More]
Packages help in executing many different tasks at once and there are times when there are functionality requirement where package needs to run on a particular event invoked by the end user and we want to handle this by calling a stored procedure. Below is a small demonstration as how we accomplish this scenario using stored procedure using xp_cmdshell. [More]
Say we have a stored procedure that we want to copy across multiple databases. One way would be to create the script and execute in all the databases. But if Databases are many it can be time consuming and efforts to change Database name every time before executing the script will be more. One better way is writing a script that can create the Database object in selected Databases or all Databases at once. [More]
In this article we will see the way execution order works in SQL server, mainly when TOP, Union and order by clause are utilized in a query.
Say we have an Employee table
create table Employees
(EmployeeID int identity(1,1) NOT NULL
,EmployeeName varchar(50) NOT NULL default 'N/A'
,Department varchar(50) NOT NULL default 'N/A'
,Salary float NOT NULL default 0) [More]
Performance is something every Developer struggles with at some point or other. In order to do optimization we can see query plan. This article will demonstrate as how we can create the function that will show us the query plan and how we can use it to see plans of objects that are highly used [More]
So far we have used stored procedure to insert, update and delete of any record in database. But in this article we will be using simple approach to accomplish our task. We will be writing most of our codes in C#. [More]
In this article I am going to explain how we can access the pictures which are stored in some directory in our system and filename is stored in database [More]
Certain Business requirements may involve sending database mail that incorporates company's logo, and other formatting things like thick border, particular font etc...Below article describes how this can be done using Database Mail. [More]
TRY...CATCH block is very efficient mechanism for error handling. In the previous versions of SQL Server @@Error was used, that required checking for error after every SQL statement. [More]