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
Problem: We need to download the most recent file in past 7 days from FTP.
Solution: This can be done in various different ways, in this article we will be using script task to accomplish this goal.
Our Control Flow tab would look like this [More]
In a company say ABC Inc. you have stored data in the database for each department. Data includes Location, total salary, # of Paid vacation, and total #of employees for each department. We have a department head for every department and once in every month we need to send these department head's a summary report reflecting this data in an excel file. So our goal is to create different excel reports for each department. [More]
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]
In this article, we will be using FTP task to download an .xls file and then use that file to upload it in SQL Server using Data Flow Task base. So let's create a new package and name our package as DownloadFile. [More]
There is a common need where we are required to generate a report from a database and send that report to group of people. In this article we will see as how we can use SSIS to design a work flow to first create an excel report and then send in an Email. [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]
Recently, we had a server migration from 32 bit to 64 bit, once the transfer was complete, my packages developed in BID's started giving an error that read as "SQL Server Destination" failed the pre-execute phase and returned error code 0xC0202071. After searching for resolution I found help on Microsoft here http://support.microsoft.com/kb/2009672 [More]