Destinations


Inside the data flow, destinations accept the data from the data sources and from the transformations. The architecture can send the data to nearly any OLE DB-compliant data source, a flat file, or Analysis Services. Like sources, destinations are managed through the Connection Manager. The configuration difference between sources and destinations is that in destinations, you have a Mappings page (shown in Figure 4-11), where you specify how the inputted data from the data flow maps to the destination.

image from book
Figure 4-11

You won't be able to configure the destination until it is connected to the data flow. To do this, select the source or a transformation and drag the green arrow to the destination. If you want to output the bad data to a destination, you would drag the red arrow to that destination. If you try to configure the destination before attaching it to the transformation or source, you would see the error in Figure 4-12.

image from book
Figure 4-12

Data Mining Model Training

The Data Mining Model Training destination can train an Analysis Services data mining model by passing it data from the data flow. You can train multiple mining models from a single destination and data flow. To use this destination, you would select an Analysis Services Connection Manager and the mining model.

Note

The data you pass into the Data Mining Model Training destination must be presorted. To do this, you would use the Sort transformation, which will be discussed in the next section.

DataReader Destination

The DataReader destination is a way of extending SSIS data flows to external packages or programs that can use the DataReader interface, such as Reporting Services. When you configure this destination, you should make sure that the name of your destination is something that's easy to recognize later in your program, because you will be calling that name later. You can see in Figure 4-13 that the destination's name is RowSampling Output. After you've configured the name and basic properties, check the columns you'd like outputted to the destination in the Input Columns tab.

image from book
Figure 4-13

If you'd like to use this destination now in Reporting Services, you'd have to ensure that it has this type of connection (SSIS) available to it. In Reporting Services, ensure that the SSIS extension is in the <DATA> section of the RSReportDesigner.config file. In the Report Designer, you'll now see a new connection type called SSIS. Select this data source, and then type the fully qualified name of the package like this:

 -f C:\Packages\RSSPartnerFeed.dtsx 

In the Query box for the connection, specify the DataReader destination that you'd like to retrieve data from. Every time the report needs data, the DTS package will be executed, and you will see DataReader destination data wherever it is exposed in the data flow. As you can imagine, you won't be able to use this feature unless you have a data flow in your package. It is also important to performance-tune your package before implementing this enterprise-wide, as your program will be awaiting a response from a SSIS package before it can proceed.

Using SSIS as a data source would be especially useful when you'd like to display non-SQL Server data in Reporting Services or in your application after it was transformed. For example, if you'd like to display an RSS feed (a news feed standard) in a clean tabular format in Reporting Services, this type of function would be especially useful. If you were to design this, users could then subscribe to the RSS feed in Reporting Services and have their data also pushed to them using subscriptions. This will be discussed in much more detail in Chapter 17.

Dimension and Partition Processing

The Dimension Processing destination loads and processes an Analysis Services dimension. You have the option to perform full, incremental, or update processing. To configure the destination, select the Analysis Services Connection Manager that contains the dimension that you'd like to process on the Connection Manager page in the Dimension Processing Destination Editor. You will then see a list of dimensions and fact tables in the box, as shown in Figure 4-14. Select the dimension you'd like to load and process and go to the Mappings page, where you'll map the data from the data flow to the selected dimension. Lastly, you can configure how you'd like to handle errors such as unknown keys in the Advanced page. Generally, the default options are fine for this page unless you have special needs for error handling.

image from book
Figure 4-14

The Partition Processing destination has identical options, but it processes an Analysis Services partition instead of a dimension.

Excel Destination

The Excel destination is identical to the Excel source, except that the destination accepts data instead of sending data. First, select the Excel Connection Manager from the Connection Manager page and then specify which worksheet you wish to load data to.

Flat File Destination

The commonly used Flat File destination sends data to a flat file and can be fixed-width or delimited. The destination uses a Flat File Connection Manager. You can also add a custom header to the file by typing it into the Header option in the Connection Manager page. Lastly, you can specify on this page that the destination file will be overwritten each time the data flow is run.

OLE DB Destination

The most commonly used destination for you will probably be the OLE DB destination (Figure 4-15). It can write data from the data flow to OLE DB-compliant data sources such as Oracle, Access, and SQL Server. It configures like any other destination and source, using OLE DB Connection Managers. A dynamic option it does have is the Data Access Mode. If you select Table or View — Fast Load, or its variable equivalent, then you will have a number of options below, such as Table Lock. This Fast Load option is available only for SQL Server database instances. A few options of note here are the Rows Per Batch option, which specifies how many rows are allowed before a batch is committed. The other option is the Maximum Insert Commit Size, which specifies how large the batch size will be when inserting using the Fast Load option. The Table Lock option will place a lock on the destination table to speed up the load. As you can imagine, this will cause grief for your users if they're trying to read from the table at the same time. The other important option is Keep Identity. This option allows you to insert into a column that has the identity property set on it.

image from book
Figure 4-15

Raw File Destination

The Raw File destination is an especially speedy data destination that does not use a Connection Manager to configure. Instead, you point to the file on the server in the editor. The destination is written to typically as an intermediate point for partially transformed data. Once written to, other packages could read the data in by using the Raw File source. The file is written in native format and so is very fast.

Recordset Destination

The Recordset destination populates an ADO record set that can be used outside the transformation. For example, you can populate the ADO record set, and then a Script task could read that record set by reading a variable later in the control flow. This type of destination does not support an error output like some of the other destinations.

SQL Server and Mobile Destinations

The SQL Server destination is the destination that is optimized for SQL Server. It gains its speed advantages by using the bulk insert features that are built into SQL Server. What's nice about this destination is that you can perform transformations earlier in the data flow and actually load data quickly in bulk into SQL Server after it has been transformed. Through the Advanced tab in the destination, you can configure the same features that are available in the bulk insert feature, such as executing triggers or locking the table. Note that this destination can be used only if the package is running on the same machine as SQL Server, because it uses an interface that's in-memory. Lastly, the SQL Server Mobile destination is a destination that can direct data to a Pocket PC device.



Professional SQL Server 2005 Integration Services
Wireless Java : Developing with Java 2, Micro Edition
ISBN: 189311550X
EAN: 2147483647
Year: 2006
Pages: 182

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net