Chapter 5 Quick Reference


Open table as spreadsheet

To

Do this

Change DelayValidation property

In the Control Flow pane, right-click an open area and click Properties to open the Properties pane. In the Execution set, change DelayValidation to True or False.

Add a Sequence Container control flow item

On the Control Flow page, drag Sequence Container from the Toolbox to the grid. Drag existing tasks into Sequence Container. Drag tasks together.

Add a variable

Right-click the background of the package and choose Variables. This brings up a window that you can dock on the side. Click the Add Variable button in the Variables toolbar. Change the Name and Data Type as necessary. Note that the scope for a new variable is the container that is active when you create the variable; in this case, it is the package.

Add a Script Task

Drag Script Task from the Toolbox to the grid. Double-click the Script Task to assign its properties. Click the Design Script button to add code to the Main() function.

Add a connection manager for Fuzzy Lookup input

Create a new Data Flow, and then double-click to open it. Drag Flat File Source from the Toolbox to the grid. Double-click the Flat File Source data adapter, and then click the New button to create a new connection manager. Use the Browse button to navigate to the source text file. You have to verify that the column width of the source is not different from the reference column because Fuzzy Lookup gives a warning if the column width of the source is different from the reference column. Click Advanced in the Flat File Connection Manager Editor to change the OutputColumnWidth to a value equal to the reference column in the lookup table. Then, in the Flat File Source Editor, click Columns. This will set the mapping between the External Column and the Output Column. Click OK.

Add a Fuzzy Lookup transformation

Drag a Fuzzy Lookup transformation from the Toolbox to the grid. Connect the output from the Flat File Source to the Fuzzy Lookup by clicking and dragging the green arrow. Double-click the Fuzzy Lookup task to open the transformation editor and set the properties for Connection Manager and Reference Table Name. Click the Columns tab, and then create a join by clicking and dragging the Input Columns to the Lookup Columns. Check Pass Through in the Available Input Columns box so that the source file name is included in the output file. Finally, select the Lookup Column and click OK.

Add a SQL Server Destination

Drag SQL Server Destination from the Toolbox to the grid. Drag an output arrow from the Fuzzy Lookup transformation to the SQL Server Destination. Double-click the SQL Server Destination and select a connection manager. Create a new table and notice that extra columns are created to hold statistics for the transformation. Click Mappings in the SQL Destination Editor to set the correct mappings between the input and destination columns, and then click OK.

Add an Execute SQL task

On the Control Flow page, add a new Execute SQL task to the container and set properties for Name, Connection, and SQL Statement. Click OK.

Add a Foreach Loop container to a Sequence container

Drag Foreach Loop Container from the Toolbox into Sequence Container. Double-click the new container to open the editor, and then click Collection to change the properties for Folder and Files (Ex. FuzzyInput*.txt). Click Variable Mappings in the Foreach Loop Editor and, in the Variable list box, select <New Variable>. Change the Container property to the name of the package (at the top of the hierarchy) to set the scope of the variable to the entire package. Change the variable's Name property and click OK twice. Click Connection Manager for the input files and display its properties window. Click the ellipsis in the Expressions property and set Property to ConnectionString; add the following expression: @[User::<variable name>. This expression references the user variable you specified when you set up the Variable Mappings in the Foreach Loop Editor.

Apply Precedence constraints

Drag the output arrow from Script Task to Sequence Container. Double-click the constraint to edit the Precedence constraint options: Evaluation Operation, Value, and Expression. When you have more than one constraint on a task, you will need to specify the Multiple Constraints option.




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

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