Sql2005-an in-depth understanding of the application of the recordset in SSIS

sql2005| Record Set

Sql2005-an in-depth understanding of the application of the recordset in SSIS

In this article, I'll describe how to produce a recordset and take advantage of the rows and Liegan in the recordset, such as when you want to perform an operation based on a row traversal, which is useful

The resulting recordset is very simple, as described in the ExecuteSQL task component of SSIS

All right, let's get to work now.

1, now we define 4 variables
Closuredate Date data with initial value of 1/12/1999
ContactName String Type
EmailAddress String Type
Rsdetails Object Type
The first 3 of these 4 variables will be used to establish a connection to the recordset's columns, and the latter to save the recordset
Display the results as shown in figure:

2, establishing a connection
  in this task package, we will establish 2 connections, one for obtaining data, one for sending mail, and the  
  establishing a connection is very simple, and the following figure is clicked "New OLE DB Connection "renders
 
  establishes an SMTP connection by clicking" New Connection "and then selecting SMTP to render
 
 
 
3, Data Flow Task
  We want to populate the variables with data, first we have to extract the data
  Of course, this is very simple thing, in the Data Flow Task panel Drag the following 2 components
  ole DB Source Component
  recordset Destination Components
  Then set the connection information and data information for the OLE DB source component, as shown in figure:
 
  then put OLE DB The source component and the recordset destination component are connected to set the properties of the recordset destination component
  Of course, we want to save the record in a variable, by setting the recordset Destination the properties of the component
  as shown, we save the data from the OLE DB source component in the rsdetails variable
 
  Okay, When the above settings are complete, our Data Flow task pane looks like this
 

4, split recordset
  So far, the recordset is a total, and the next task is to break it down into multiple individuals (rows)
  This will be done with this great "ForEach Container" component
  This component allows us to traverse the collection, of course, there are many ways to iterate, you can be the right to the enumerator, such as "ADO Enumerator", is specifically used to traverse the ADO recordset, enumerated values directly through the variable mapping to achieve the
  well, Drag this component up, set its properties, as shown in the diagram, set the data source for the "ForEach Container" component and the enumeration type
 
  Set the enumerated object to a variable mapping relationship
 

5, Send mail
Now that the recordset is split into individual rows and their values are stored in the variable, you must use the Send Mail task component to access these values and treat them as relevant information about the message.
It's important to note that you have to drag this component into the "ForEach Container" component, otherwise it's not a parent-child relationship.
Here's how to set up message components.
The first of course is to set who sent over, mail is not express mail, such as the basic general information of a series of messages, such as the figure

And then, of course, the following information is dynamically set
Recipient
Theme
Content
Because of the need for dynamic settings, this requires setting the value of the expressions property to be implemented, and basically every component has this property for dynamically setting the component's properties
For example, dynamically set the message Source property of a mail component, as shown in

Can be found very convenient, you need to refer to the list of things, considerate ah, really Microsoft
Topics, recipients, and so on, as shown in


Finally, the message component expression has the following display interface:


All right, the whole mission is done, as pictured.



Summarize
Well, that's it, I hope you like it.
The English of this article originates from http://www.sqlis.com/default.aspx?59