Stock Foreach Enumerators
| Microsoft provides the following stock Foreach Enumerators. Each one has a unique function and provides access to a different type of resource. Although limited in number, this set of enumerators provides access to a broader set of collections than might be immediately obvious. Foreach FileThe Foreach File Enumerator is for enumerating the files in a file system folder. Figure 13.6 shows the designer for this enumerator with the default settings. Figure 13.6. The Foreach File Enumerator enumerates files in folders The Foreach File Enumerator returns strings that contain the name of a file. The Folder setting is the folder that contains the files you want to enumerate. The Files setting indicates which files to enumerate and supports wildcards. Depending on the settings you specify, the filename can be returned as the fully qualified name (C:\Temp\YourFile.txt), the filename with extension (YourFile.txt), or just the base filename (YourFile). Check the Traverse Subfolders option to enumerate all files that meet the Files specification in the Folders directory and all subdirectories. Tip The sample package uses a property expression to set the value of the Folder property to the value of the WORKINGFOLDER variable. To see the expression, open the Foreach Loop designer, select the Collection tab, and select the Expressions property. The Directory property, which is the same as the Folder field in the Foreach Loop designer, comes from the WORKINGFOLDER variable configured with the SSISSAMPLESWORKINGFOLDER environment variable package configuration. Foreach ItemThe Foreach Item Enumerator is a matrix enumerator that enumerates over a collection of settings you create within the Enumerator designer. It provides a way to create a custom collection and then drive the Foreach Loop with that collection. You specify the columns and column types and then enter data into the columns. The Enumerator designer modifies the entry grid to match the columns you create and checks value types as you enter them. This Foreach Enumerator is useful when you have a fairly fixed list of nonuniform inputs you want to enumerate repetitively over time. For example, if you need to process the same files, FTP data from the same locations, or perform maintenance tasks on the same servers, you can use the Foreach Item Enumerator to build a custom collection to hold those values. The Foreach Item Enumerator also has the benefit of being independent of connections or outside input, which is useful if you're trying to decrease package dependencies. Figure 13.7 shows the designer for the Foreach Item Enumerator with some data already entered. Figure 13.7. The Foreach Item Enumerator lets you create custom collections The Remove button removes rows. The Columns button allows you to add and remove columns. Depending on the type of the column, you might also get a Type Editor. For example, Column 1 is a DateTime column and so has a Date Type Editor. The example in Figure 13.7 shows a hypothetical collection you might create if you needed to perform maintenance work on databases every seventh day from the first date in Column1. Column 2 tracks if you want to send email when the loop processes the conditional work. Because the Foreach Item Enumerator is a matrix enumerator, the Variable Mappings tab looks a little different than nonmatrix enumerators such as the Foreach File Enumerator. Figure 13.8 shows the variable mappings for a sample Foreach Loop with a mapping for each column in the enumerator. Figure 13.8. Matrix enumerators provide more than one variable value per iteration Notice that three variables are defined and that the value from the three columns is mapped to each of them. This one-to-one mapping is typical, but not enforced. You can create multiple mappings per column, which might be useful in rare cases. These variables are used within the loop to control its behavior, and this is what is called a variable-driven loop. For example, the package references the SENDMAIL variable in a precedence constraint to determine if the Send Mail Task should execute. The precedence constraint between the first task and the Conditional Work Task also uses the PROCESSDATE variable to conditionally execute a task on certain dates, but not for all days. Tip Notice that the variables have been defined at the Foreach Loop scope. It's good practice to define variables at the scope where they are used. If you define the variables at a more global scope, for example at package scope, then tasks outside the loop can modify the variables and alter the behavior of the loop, possibly causing the loop to have timing-related bugs. Microsoft provided this enumerator as a way to enumerate a collection when there is no default way to generate the collection and to simplify enumerating over custom collections. The Foreach Item Enumerator isn't the right solution for every problem, but it can come in handy when you've got a few manual settings you want to have in a collection and don't want to establish a more formal collection. Try the ForEachItem.dtsx package in the sample solution S13-Loops to better understand the Foreach Item Enumerator. Figure 13.9 shows the workflow of the sample package. Figure 13.9. Matrix enumerators enable complex conditional workflow Using matrix enumerators such as the Foreach Item Enumerator, you can drive the behavior of some quite complex Foreach Loops, as you can see in the sample package. The sample package simulates a package that
Foreach ADOThe Foreach ADO Enumerator iterates over rows in an ADO recordset or ADO.NET dataset. This is a powerful enumerator for controlling a package with tables. These types of packages are called table driven because you can essentially drive a package's execution with the values from rows in a table. Figure 13.10 shows the Foreach ADO Enumerator designer from the sample package. Figure 13.10. The Foreach ADO Enumerator supports iterating over an in-memory dataset The sample shows how to enumerate over a set of rows in the first table. It's also possible to iterate over multiple resultsets. The key to setting up this enumerator is creating the resultset. You can do it in a number of ways. The sample package, ForEachADO.dtsx, uses a SQL Task to query the Employee view in the AdventureWorks database for employees with a first name that starts with "C." The SQL Task places the results into a variable of type Object named RESULTSET, which the Foreach ADO Enumerator references. You can also use a Data Flow Task with a Recordset Destination to generate the in-memory results. The Foreach ADO Enumerator is also a matrix enumerator. Figure 13.11 shows the Variable Mappings tab from the sample package with the mappings for each of the columns in the query results. Figure 13.11. The Foreach ADO Enumerator is a matrix enumerator The Execute SQL Task has the following T-SQL query: USE ADVENTUREWORKS GO SELECT FirstName, LastName, JobTitle, Phone, EmailAddress FROM HumanResources.vEmployee WHERE FirstName LIKE 'C_____' Notice that five columns are selected which correspond to the five columns in the Variable Mappings tab in Figure 13.11. Figure 13.12 shows the resulting message box with the five values from the five mapped variables. Figure 13.12. The message box shows the results from the SQL Task The Foreach ADO Enumerator is a powerful way to drive workflow with data in a table, the results of a query, the results of a data flow, or even a flat file or XML document with the appropriate source adapters in the Data Flow Task. Although the sample is a bit contrived, most users seem to struggle getting the settings on the resultset variable and the mappings correct. So, it's useful as a pattern that you can easily modify to fit your needs. Foreach ADO.NET Schema RowsetThe ADO.NET Schema Rowset Enumerator provides a way to enumerate the schema for a data source. For example, you can enumerate the tables in a database, catalogs on a server, or columns for a table. For each schema, you can filter the results in a number of ways. Figure 13.13 shows the Foreach ADO.NET Schema Rowset Enumerator Editor with the Catalogs schema selected. Figure 13.13. The Foreach ADO.NET Schema Rowset Enumerator provides a way to enumerate database schema. This enumerator is especially useful for maintenance work. For example, you can use this enumerator in combination with the Shrink Database Task to shrink each of your databases within a certain group on an automated regular schedule. Because the enumerator uses a filter to dynamically select the schema instead of a hard-coded set of database names, it would dynamically pick up any new databases and skip any databases that you remove or that happen to be temporarily offline. You can also use it to do auditing of your databases to check security settings, access rights, existence of tables, and so on. Any time you need to perform the same operation on multiple server objects, you can use this enumerator to do it. Each schema type returns a different set of values, for example, the Catalogs schema contains only one data point, the CATALOG_NAME, and the Column Privileges schema contains TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, GRANTOR, and GRANTEE. You can filter on each of these data points with what are called restrictions. To see the restrictions, click the Set Restrictions button on the Collection tab of the Foreach Loop designer. Figure 13.14 shows the Tables Schema Restrictions dialog box for the Tables schema. Figure 13.14. You can add restrictions to filter the results In this example, the enumerator is referencing the AdventureWorks sample database and enumerating only the tables in the HumanResources schema. Because there are four data elements showing, on the Variable Mappings tab, you can map each of those data elements to variables. The resulting values for tables would be names of tables in the AdventureWorks.HumanResources schema. You can add restrictions for each data point independently as well. For example, in addition to the HumanResources table schema restriction, you could also add a restriction on TABLE_NAME of Employee. This would return one row with the following columns:
Note There is no way to specify a wildcard for restrictions. For those ADO.NET:OLEDB providers that support schema rowsets, the typical collection of supported schema include those in Table 13.1.
To see the Foreach ADO.NET Schema Rowset Enumerator in action, open the sample package ForEachADOSchemaRowset.dtsx in the S13-Loops sample solution. It uses two restrictions to return only the names of the views in the HumanResources schema and shows each of them in a message box. Try changing the restrictions to see how it affects the results. Foreach From VariableThe Foreach From Variable Enumerator is a little odd because, unlike the other Foreach Enumerators, it doesn't actually represent a domain. In fact, you can create one of the other enumerators in the package with a Script Task, save it to a variable, and use the Foreach From Variable Enumerator to get the Foreach Enumerator you just built. Also, there are certain types of objects in .NET code, such as the String type, that implement the IEnumerator interface. One way the Foreach Loop retrieves elements from collections is through the IEnumerable interface. Because of this, you can enumerate through just about any collection you build in the Script Task with the Foreach Loop. If you build a string with the value "Merry Christmas" and use the Foreach From Variable Enumerator, the Foreach Loop iterates 15 times, each with the next letter in the string starting with 'M' and ending with 's.' This makes the From Variable Enumerator an excellent choice when you want to build collections dynamically. Figure 13.15 shows the Foreach Loop Editor with the From Variable Enumerator selected. Figure 13.15. The Foreach From Variable Enumerator builds enumerators dynamically As you can see, the setup is quite simple. The From Variable Enumerator requires the variable name where the enumerator is stored. The real work is in the package before the ForEach Loop that builds the collection. Note The variable must be of type Object and the object must support either the managed IEnumerable .NET interface or the IEnumVariant COM interface. The Sample package ForEachFromVariable.dtsx shows how to build a simple array of strings, pass it to a variable, and show it in a message box. Foreach NodeListXML documents are basically hierarchical collections of nodes comprised of other nodes, elements, and values. Often, the nodes have a repeating pattern with similar types of information. For example, you might have an XML file that contains a work list. Maybe it's a list of files or tables you need to process. In reality, the types of problems you solve with this enumerator are the same as the other enumerators; the only difference is that the Foreach NodeList Enumerator allows you to drive the Foreach Loop with an XML file using the XML concept of the nodelist. The Foreach NodeList Enumerator can function as an array enumerator, providing only one value per iteration, or a matrix enumerator, depending on how you configure it. It can also return different types of values, such as an XML DOM node object, collection, or string. To use this Foreach Enumerator, you'll need a basic understanding of XML and XPath. Printed and web references about these technologies abound, so they aren't explained in detail, but here's a quick review just to get everyone on the same page. An XML node is a valid, complete structure in an XML document. A node can be very simple, such as this: <simplenode/> or as complex as an entire XML document. XPath is a way of selecting parts of an XML document. Using simple XPath queries, you can select a set of nodes or values from an XML document. For example, you can write an XPath query to return only one node or one that returns a set of nodes. XPath queries can contain conditional logic or wildcards. Nodelists are collections of XML nodes and can be returned from an XPath query. XML navigators are objects that use the cursor model to iterate over nodelists. So, it is possible to write an XPath query that when executed returns a nodelist and then use a navigator to iterate over the nodelist. If you want to find out more, do a search for XPath on MSDN or your favorite search portal. The sample package, ForEachNodelist.dtsx in the sample solution S13-Loops, shows how to configure the Foreach NodeList Enumerator as a matrix enumerator returning two values for each iteration. The XML file that drives the enumerator, LastProcessed.xml, is in the SampleData subfolder and looks like this: <LastProcessedDate> <Info> <ID>3</ID> <Date>2005-12-13 11:20:00</Date> </Info> <Info> <ID>14</ID> <Date>2005-11-12 10:22:00</Date> </Info> </LastProcessedDate> Figure 13.16 shows the Foreach NodeList Enumerator designer with the settings for retrieving the ID number and date as text. Figure 13.16. Use the Foreach NodeList Enumerator to retrieve text from XML files The Document section describes where the enumerator will find the source XML document. The DocumentSourceType and DocumentSource properties work together to describe to the enumerator where to retrieve the XML source. The XPath section is where you define the XPath strings the Foreach NodeList Enumerator will use to select nodes from the XML source. The EnumerationType property specifies what type the enumerator should return when it finds the selected nodes. The most commonly used setting is likely NodeText because it returns a string that other package components can use directly. The ElementCollection option is the setting that makes the Foreach NodeList Enumerator a matrix enumerator and enables the properties that are prefixed with "Inner." The "Inner" properties often confuse people, but they are really quite simple. If EnumerationType isn't set to ElementCollection, you can ignore the "Inner" properties and the Foreach NodeList Enumerator ignores them in that case as well. The enumerator iterates over the nodes returned from the XPath query defined in the OuterXPathString. For example, using the source XML shown previously, if the OuterXPathString was set to /LastProcessedDate/Info/ID and the EnumerationType was set to NodeText, there would only be one value returned per iteration. The first value returned from the enumerator would be 3, and the last value would be 14. If the OuterXPathString was set to /LastProcessedDate/Info/Date, the first value returned from the enumerator would be 2005-12-13 11:20:00, and the last value would be 2005-11-12 10:22:00. This is great if all you want to retrieve is one value per iteration. But, what if you need to return multiple nodes per iteration? Well, you could use the XPath query /LastProcessedDate/Info to return the node object and use a Script Task to shred the Date and ID values from the node, or you can specify an inner XPath query. On the first iteration, the XPath query /LastProcessedDate/Info would return the following XML: <ID>3</ID> <Date>2005-12-13 11:20:00</Date> Now, you need to apply another XPath query to get at the result. The sample uses the simple wildcard character *. However, you can use any valid XPath query to retrieve only the nodes you want. This is how you use the InnerXPath properties. The InnerXPath properties are a way for you to process the results of the OuterXPath properties. The result of the second XPath query on the first iteration is two strings: "3" and "2005-12-13 11:20:00." Caution To map the results, the index on the Variable Mappings tab corresponds to the order of the nodes in the XML document. For example, the ID node in the sample XML is at index 0 and the Date node is at index 1. If the nodes are not always in the same order, the mappings are confounded and produce incorrect results. You must ensure that the nodes are always in the same order for the entire resultset. Foreach SMOThe Foreach SMO Enumerator is very similar to the Foreach ADO.NET Schema Rowset Enumerator. It enumerates objects such as tables, databases, servers, views, instances, and logins, but it only supports SQL Server databases and uses a SMO connection. SMO is an abbreviation for SQL Server Management Objects and is the new management API for SQL Server. Figure 13.17 shows the Foreach SMO Enumerator UI. Figure 13.17. Use the Foreach SMO Enumerator to iterate over SQL Server objects As you can see, the designer is fairly simple because SMO allows you to specify the objects you want to process with a URN. If you know enough about the SMO object model and your servers, you can directly type the URN into the editor in the designer. If you, like the rest of us mortals, need a little help constructing the URN, you can use the URN designer. Click on the Browse button to see the Select SMO Enumeration dialog box, shown in Figure 13.18. Figure 13.18. Build SMO URN strings using the designer Simply selecting the item type you want to enumerate builds the URN for you. Later, if you want to further refine the URN to only return a subset of the selected objects, you can do so directly in the Enumerate field by adding qualifiers. For more about this, search Books Online for SQL Management Objects or SMO. The URN designer also allows you to specify in what form you want the object returned. The Objects option returns the SMO objects the URN returns. SMO has the option of lazy populating objects so that objects don't attempt expensive enumeration or possibly wait for timeouts until absolutely necessary. To disable this feature and populate the objects immediately, select Prepopulate. If you select Names, the enumerator returns strings containing the names of the objects. The URNs option returns a string that contains a URN that uniquely describes the enumerated object. You can use the URN to later retrieve the object using SMO. Finally, some objects such as data files and log files have associated filenames where they are stored. Selecting Locations returns a string with the filename of the object. This enumerator is very useful for building day-to-day maintenance, validation, and auditing packages that automatically pick up new machines and robustly ignore offline machines. SMO is the same API upon which SQL Server Management Studio is built and is very powerful. The sample package, ForEachSMO.dtsx in the S13-Loops solution, allows you to enumerate various SMO objects. As long as the returned enumerated value is a string, the package functions correctly and shows you the resulting objects. Try changing the package to enumerate different SMO types and select URNs, Locations, and Names.
|
EAN: 2147483647
Pages: 200