FME Extension for SSIS Tutorial

Safe Software Inc. makes no warranty either expressed or implied, including, but not limited to, any implied warranties of merchantability or fitness for a particular purpose regarding these materials, and makes such materials available solely on an “as-is” basis.

In no event shall Safe Software Inc. be liable to anyone for special, collateral, incidental, or consequential damages in connection with or arising out of purchase or use of these materials. The sole and exclusive liability of Safe Software Inc., regardless of the form or action, shall not exceed the purchase price of the materials described herein. This manual describes the functionality and use of the software at the time of publication. The software described herein, and the descriptions themselves, are subject to change without notice.

Copyright

© 2008 Safe Software Inc. All rights are reserved.

Revisions

Safe Software Inc. assumes no responsibility for any errors in this document or their consequences, and reserves the right to make improvements and changes to this document without notice.

Trademarks

FME is a registered trademark of Safe Software Inc. All brand or product names are trademarks or registered trademarks of their respective companies or organizations. Document Name: FME Extension for SSIS Tutorial Version: FME 2008 April 2008

Contents Chapter 1 ...................................................................................................................................................................................................................................... 1

Installation................................................................................................................................................................................................................................. 1

Chapter 2 ...................................................................................................................................................................................................................................... 2

Getting Started .......................................................................................................................................................................................................................... 2

Chapter 3 ...................................................................................................................................................................................................................................... 3

Performing a Simple FME Source to FME Destination Transformation .................................................................................................................................. 3

Chapter 4 ...................................................................................................................................................................................................................................... 6

Combining FME Source and Flat Files with Demographic Data ............................................................................................................................................. 6

Chapter 5 ...................................................................................................................................................................................................................................... 9

Making Regions from Original Features ................................................................................................................................................................................... 9

Chapter 6 .................................................................................................................................................................................................................................... 11

Replacing Geometry Types and Visual Representation ........................................................................................................................................................... 11

Chapter 7 .................................................................................................................................................................................................................................... 16

Geometry Creation and Spatial Analysis ................................................................................................................................................................................ 16

Appendix A ................................................................................................................................................................................................................................ 22

Enabling Package Logging for the Data Flow ........................................................................................................................................................................ 22

Appendix B ................................................................................................................................................................................................................................. 24

Improving Performance of Synchronous Spatial Transforms ................................................................................................................................................. 24

Contents iii

Chapter 1

Installation

The FME Extension for SSIS extends Microsoft® SQL Server with spatial data integration capabilities.

This document contains all the information you need to evaluate the FME Extension for SSIS. The common tasks that a SSIS user would perform using the FME Extension are described in detail. It is assumed that evaluators have a working knowledge of SSIS. For detailed instruction in FME please contact Safe Software about the Introduction to FME course. Before you get started, you will need the following software installed on the evaluation computer:

• SQL Server 2005 Integration Services • FME 2007 build 4102 or higher

Note: Please note that FME Extension for SSIS is not supported in a 64-bit Windows environment. Please refer to the SSIS installation documentation for instructions on installing and licensing SSIS. For information on installing and licensing FME, please refer to the FME Tutorial, available on the FME Tutorial and Demo page.

Installation and Licensing You can verify that FME is properly installed and licensed by using the FME Universal Viewer to read some of your source data. After FME is installed and licensed, follow these steps: 1. Open the FME Administrator by clicking Start > All

Programs> FME > FME Administrator.

2. Extend the FME Extension for SSIS by selecting it in the Applications list and clicking on the Extend button.

3. Click OK to close the FME Administrator.

Chapter 1: Installation 1

Chapter 2

Getting Started

1. Create a new folder called SourceData and move the tutorial files to this folder. This will be your working directory from now on.

2. Run SQL Server Business Intelligence Development Studio and create a new Integration Services Project. Call it Tutorial and click OK.

3. Select Tools > Create FME SSIS Custom Tabs. This will add all FME tools to the toolbox:

l

4. From the Control Flow Items tab in the Toolbox, drag a Data Flow Task object to the package area. Call it Shape2Tab.

Chapter 2: Getting Started 2

Chapter 3

Performing a Simple FME Source to FME Destination Transformation

Objective In this chapter, you will learn how to translate the source data from Shapefile format to MapInfo Tab (MITAB) format. This type of data exchange is very common in business-related tasks.

In order to see what is being translated, you are also going to add an FME Visualizer.

Steps 1. Double-click on the Shape2Tab Data Flow task. This will

bring you to the Data Flow tab.

2. From the Toolbox, drag an FME Source object (located under Data Flow Sources) onto the canvas.

3. Rename it Shapefile.

4. Right-click on the object and select Edit from the menu. The FME Source Editor Dialog will appear.

5. Press the New button—the Add FME Source Connection dialog will appear.

6. Start typing “shapefile” in the Format field. When only the ESRI Shape format is displayed, select it. Then click the

Browse button next to the Dataset field and select your source shapefile, states.shp (located in your SourceData folder). Press the OK button.

Chapter 3: Performing a Simple FME Source to FME Destination Transformation 3

7. Select states from the Feature Type Name dropdown menu and click OK.

8. From the Toolbox, drag an FME Destination (located under Data Flow Destinations) onto the canvas and rename it MapInfo.

9. Right-click on the object and select Edit. Press the New button. The Add FME Destination Connection dialog will be displayed.

10. Start typing MapInfo in the Format field until the format MapInfo Tab (MITAB) appears, then select it.

11. Set the Dataset path to your preferred destination folder (it can be the same as your source in this case; however,

it is a good practice to use a separate folder for output data). Press OK.

12. In the Feature Type Name box, type States and press OK.

13. Connect your source, Shapefile, to the destination, MapInfo.

14. Now you can run your translation by pressing F5. This will create a MapInfo Table and a set of four files (States.tab, States.id, States.map and States.dat) in the output folder.

15. After the translation is finished, press the Stop Debugging button .

Now let’s change our translation slightly in order to see exactly what is being written to MapInfo format. 16. Add an FME Visualization from the Toolbox’s Data Flow

Destinations tab.

17. Add a Multicast from the Data Flow Transformations tab.

Chapter 3: Performing a Simple FME Source to FME Destination Transformation 4

21. In the Viewer, use the Select Features tool to examine some of the features. They should have several attributes, including STATE_NAME, STATE_FIPS, AREA and more.

5Chapter 3: Performing a Simple FME Source to FME Destination Transformation

20. Run the translation (F5). This will simultaneously write the MapInfo Tab (MITAB) dataset and open it in the FME Universal Viewer.

19. Connect Multicast to MapInfo and to FME Visualization.

18. Delete the old connection, and connect Shapefile to Multicast.

Chapter 4

Combining FME Source and Flat Files with Demographic Data

Objective To provide some value to your data, you will add demographic data from a statistics bureau to each state.

Steps 1. (Optional) Make a backup copy of the project, as you will

add more sources and transformations to it.

2. Delete the data flow path between the Shapefile Data Flow Source and Multicast.

3. Add a Flat File Source object from the Toolbox’s Data Flow Sources tab. Right-click on it and choose Edit. Click New, then browse to USAData.csv, a file containing USA demographic data (you may have to change the file filter to All files (*.*). Click Open.

4. Check the “Column names in the first data row” box, then select the Columns tab and click OK to accept the default column definitions.

5. Rename the object CSV.

6. Add a “Sort” transformation and connect the Shapefile source to it. Double-click to open the Sort Transformation Editor, then set ascending sorting by STATE_ABBR. Click OK and rename the object Sort Shape.

7. Add a second Sort and connect the CSV source to it. In this case, set up ascending sorting by State. Rename the object Sort CSV.

8. Add a Merge Join transformation and connect Sort Shape to it. In the Input field of the Input Output Selection dialog, select Merge Join Left Input and press OK.

Chapter 4: Combining FME Source and Flat Files with Demographic Data 6

9. Connect Sort CSV to Merge Join.

10. Open the Merge Join Editor. In the Sort Shape table, mark all columns except POP2000 and POP1990. In Sort CSV, mark all columns except State, State_Name and Total_Area. Set Join type to Inner Join. Click OK.

11. Connect Merge Join transformation to the existing Multicast.

12. Delete the data flow path from Multicast to MapInfo destination and create a new path.

13. Delete the FME Visualization and add a new one. Connect Multicast to it.

Chapter 4: Combining FME Source and Flat Files with Demographic Data 7

Chapter 4: Combining FME Source and Flat Files with Demographic Data 8

14. Run the translation (F5). This will write to MapInfo Tab (MITAB) and open the FME Universal Viewer.

15. Using the Select Features tool , examine some of the features—they should show demographic data taken from the CSV file:

Chapter 5

Making Regions from Original Features

Objective For further analysis you will group states by region and calculate regional totals to see population dynamics.

Steps 1. (Optional) Make a backup copy of the project, since you

will add more transformations and a new destination to it.

2. Delete the old FME Visualization.

3. Add a Dissolver transformation from the FME Geometric Operator Transformations group.

4. Connect the Multicast to the Dissolver.

5. Edit the Dissolver by setting the Group By field to SUB_REGION and selecting Pop_1980, Pop_1990 and Pop_2000 in the Attribute(s) to sum field. In the Dissolve Member Count Attribute field, enter StatesPerRegion. Press OK.

6. Right-click on the Dissolver and select Show Advanced Editor. Go to the Input Columns tab and leave only FME_GEOM, SUB_REGION, Pop_2000, Pop_1990 and Pop_1980 selected. Press OK.

7. Add a new FME Visualization object. Connect the Dissolver to it. This will display the Input Output Selection dialog. Select Area for Output and click OK.

Chapter 5: Making Regions from Original Features 9

8. Edit the FME Visualization and select SUB_REGION as the

Attribute(s) to Group By.

9. Run the translation. The FME Universal Viewer should start and show the map of the United States with randomly colored regions.

Chapter 5: Making Regions from Original Features 10

Chapter 6

Replacing Geometry Types and Visual Representation

Objective Now you will modify your package to visually reflect population dynamics for each region in the continental USA. You will build bar diagrams showing populations in 1980, 1990 and 2000.

Steps 1. (Optional) Make a backup copy of the project, since you

will add more transformations and a new destination to it.

2. Delete the FME Visualization, delete the connection between the Multicast and the Dissolver and insert a Conditional Split transformation after the Multicast. Right-click on the Conditional Split, select Edit and add the following condition: STATE_NAME != "Hawaii" && STATE_NAME != "Alaska" Call it Continental States, then connect Continental States Output to Dissolver. This step will isolate the continental part of the USA by filtering out Hawaii and Alaska.

3. Add Multicast after Dissolver. Select Area as output in the Input Output Selection dialog, then press OK.

4. Add an FME Visualization and connect it to Multicast 1. Right-click on the FME Visualization, select Edit and call it Regions. Group features by SUB_REGION field.

Chapter 6: Replacing Geometry Types and Visual Representation 11

5. Add an Aggregator transformation from FME Collector Transformations and connect it to Multicast 1. Go to Parameters and group features by SUB_REGION, then select all population fields in “Attribute(s) to average”. This is necessary because, while the Dissolver eliminates boundaries between adjacent features, it splits those not having adjacent boundaries into separate features.

6. Add a CenterOfGravityReplacer transformation from FME Manipulators Transformations and connect it to the Aggregator. This transformation has no parameters. It will replace regions with points in the center of gravity (average x and y).

7. Insert one more Multicast after CenterOfGravityReplacer.

8. Now add three Offsetters from FME Manipulator Transformations and connect them to Multicast 2. Set X-Offset for the first one to -1, do not touch the Offsetter in

the middle and set the third to 1. This step will create three points for each region with a 1 degree interval along the x axis.

9. Add three Copy Column transformations.

Chapter 6: Replacing Geometry Types and Visual Representation 12

10. In the first Copy Column, select Pop_1980 and name its Output Alias “PopBarHeight”.

11. In the next two Copy Column transformations, copy Pop_1990 and Pop_2000 to PopBarHeight accordingly.

12. Combine all three data flow paths into one by connecting them to a Union All transformation.

13. Add a CoordinateFetcher from FME Calculator Transformations after Union All. It will extract the coordinates of each point to attributes.

14. Now make a few calculations. Add a Derived Column transformation and connect it to the CoordinateFetcher.

15. Set four new fields as follows: MinX = (DT_R4)_x - 0.5 MaxX = (DT_R4)_x + 0.5 MinY = (DT_R4)_y MaxY = (DT_R4)_y + (DT_R4)PopBarHeight / 5000000

MinX, for example, will be set as the Derived Column Name, while its Expression will be “(DT_R4)_x - 0.5”.

Chapter 6: Replacing Geometry Types and Visual Representation 13

16. Your last step before visualization is creating new geometry for each point. Add a 2DBoxReplacer transformation from FME Manipulator Transformations after Derived Column and set its minimum (MinX and MinY) and maximum coordinates (MaxX and MaxY) in the transformation parameters.

17. Add an FME Visualization and call it PopulationGrowth, then run the translation (F5).

Chapter 6: Replacing Geometry Types and Visual Representation 14

Chapter 6: Replacing Geometry Types and Visual Representation 15

18. The FME Universal Viewer will start and show the following picture with randomly set colors:

Chapter 7

Geometry Creation and Spatial Analysis

Objective For project purposes, you will order ortho imagery. Your area of particular interest is Missouri. (An Imagery provider has sent a list of available ortho images in Excel format.) The tile name consists of five digits, where the first three represent longitude, and the last two represent latitude of the lower left corner of a tile. We want to figure out which tiles we need—that is, which images cover the state of Missouri.

Steps 1. Create a new Integration Services project and add a new

Data Flow Task.

2. Add a Shapefile FME Source (states.shp) as described in Chapter 2.

3. Add an Excel Source and make a new connection to TilesList.xls. Select TileList sheet.

4. Add a Derived Column transformation from Data Flow Transformations. Rename it Derive Min XY and connect it to the Excel Source.

5. Right-click on the transformation and select Edit.

Chapter 7: Geometry Creation and Spatial Analysis 16

6. Create two new columns – MinX and MinY. For the first column, add this expression: -((DT_I1)SUBSTRING(TileName,1,3)) For the second column, add this expression: (DT_I1)SUBSTRING(TileName,4,3)

7. Add another Derived Column transformation, connect it and rename it “Derive Max XY.”

8. Open the Transformation Editor and add two new columns: MaxX as (MinX + 1) and MaxY as (MinY +1).

9. From FME Manipulator Transformations, add a 2DBoxReplacer. This will create 2D rectangles from two pairs of coordinates.

10. Connect Derive Max XY to the 2DBoxReplacer and open its Editor.

Chapter 7: Geometry Creation and Spatial Analysis 17

11. Fill all the parameters with the appropriate columns and press OK.

12. Add a Conditional Split transformation and connect the Shapefile source to it. Set the following condition: [STATE_NAME] == “Missouri” and call it PASSED.

Note: If you copied and pasted the above condition, you may need to manually enter the quotation marks in order to avoid errors.

13. For quality control (QC) purposes, add a Multicast transformation and FME Visualization. Connect the PASSED output of Conditional Split to them in series.

Chapter 7: Geometry Creation and Spatial Analysis 18

14. Set the Visualization name to Missouri.

15. Add a SpatialFilter transformation from FME Filter Transformations. This transformation analyzes spatial relationships between features.

16. Connect the Multicast from the Shapefile flow to the SpatialFilter.

17. In the Input Output Selection dialog, pick BASE, since tiles will be tested against the state.

18. Connect the 2DBoxReplacer with the SpatialFilter.

19. Open the Spatial Filter parameters. Click the Browse

button next to the Tests to Perform field to bring up the Select Tests to Perform Iterms dialog. Select CONTAINS and OVERLAPS, since we want to select all tiles that the state contains entirely or overlaps in any proportion.

Chapter 7: Geometry Creation and Spatial Analysis 19

20. Set Merge Attributes to No and leave the default settings for the other parameters. Press OK to close the dialog.

21. Add one more FME Visualization destination, connect SpatialFilter to it and pick PASSED output in the Input Output Selection dialog. Press OK.

22. Set the Visualization name to OrthoTiles.

Chapter 7: Geometry Creation and Spatial Analysis 20

Chapter 7: Geometry Creation and Spatial Analysis 21

23. Run the transformation. The FME Universal Viewer should start and show Missouri with all containing and overlapping tiles.

As the last step, the Visualization can be replaced with an Excel Destination in order to create a list of required tiles. Create a new connection, set Data Access Mode to Table or View and press the New button for the Name of the Excel sheet. Use the following statement in the SQL dialog that appears: CREATE TABLE `TileList ` ( `TileName` VARCHAR(5) ) Close all dialogs by pressing OK.

24. Run the update translation to get the list of required ortho tiles in Excel format.

Appendix A

Enabling Package Logging for the Data Flow

Objective In this section, you will learn how to enable package logging for the data flow. Package logging provides additional information that may be helpful in debugging spatial data flow issues.

Steps 1. Click on the SSIS menu and select Logging. This will open

up the Configure SSIS Logs: Package dialog.

2. Click on the Add button to add a log provider (such as the

SSIS log provider for Text files). In the Configuration field, create a new connection to a text file. The Usage Type should be set to Create File.The following example

Appendix A: Enabling Package Logging for the Data Flow 22

shows that a connection to the fmessis.log file has been created and that the Data Flow Task and Log Provider are enabled (as indicated by the check marks).

3. Now click on the Details tab and ensure that the

OnInformation event is selected. By doing this, additional FME log messages will now be written to the fmessis.log file.

Appendix A: Enabling Package Logging for the Data Flow 23

Appendix B

Improving Performance of Synchronous Spatial Transforms

Objective In this section, some helpful tips to improve the performance of your synchronous spatial transforms are provided. As an example, we will use the LengthCalculator transformation.

Steps 1. Connect a spatial FME source to the LengthCalculator.

2. Right-click on the LengthCalculator and select Show

Advanced Editor, then click the Input Columns tab. You will notice that, when you connect an upstream component (like the FME source) to the LengthCalculator, all available input columns have the Usage Type of READWRITE by default. All upstream columns from the FME source are made available to the LengthCalculator for transformation purposes.

Appendix B Improving Performance of Synchronous Spatial Transforms 24

3. Since we know that the LengthCalculator transformation

simply calculates the length of the geometry, we know that only the FME_GEOMETRY field is required. Therefore, it would be fine to remove the other available columns. Furthermore, since there is no geometry modification, we can set the Usage Type to READONLY. By making these optimizations, the LengthCalculator will now do less processing. Since the LengthCalculator is a synchronous transformer, the columns removed from the Available

Input Columns list are actually still available to downstream components.

4. Now if you connect the output of the LengthCalculator to

a downstream component and double-click on the connected data flow path, the Data Flow Path Editor will open. Clicking on the Metadata option will show that the columns that were removed from the Available Input Columns list in the Advanced Editor of the LengthCalculator are still available for downstream components to use.

Appendix B Improving Performance of Synchronous Spatial Transforms 25

Appendix B Improving Performance of Synchronous Spatial Transforms 26

In conclusion, for synchronous transformers, simply changing the Usage Type to READONLY and removing available input columns in the Advanced Editor can improve performance by reducing unnecessary processing.