SQL Server 2005 (IDW 13 – April 05 CTP) Demo Notes


SQL Server 2005  (IDW 13) Integration Services - Demo Crib Notes for the Australia Partner SQL 2005 Roadshow - April 2005


Demo I


  1. Create a new package called Presidents


  1. Add a new Data Flow: Import Presidents


  1. Add a new Flat File Data Source - point at c:\data\presidents.txt


  1. Click "New" to set up a new data connection


  1. Name: Presidents Source

Set "Column names in first row"

Set "Skip one Header row"


Advanced:  Change name to Presidents and set column width to 250



  1. Clear out unwanted Rows


Add a new Conditional Split


Connect Conditional Split to Flat File Data Source

Double click Conditional Split


From Columns drag President column to List of Conditions

Change name of condition to "Discard Rows"


Set the condition clause to: SUBSTRING([Presidents],1,1) == " "

Change the Default Output Name to: "Valid Rows"


Add a Derived Column

Link to Conditional Split - "Valid Rows"

Set Derived Column to "Replace 'Presidents'"

Set Expression to "SUBSTRING([Presidents],1,FINDSTRING([Presidents],"(",1) - 2)"



Write the Cleansed Rows to PresidentsDestination.Txt


Create a new Flat File Destination


Create a new Connection manager: Presidents Destination

Set file name: c:\data\presidentsDestination.txt

Set the Mappings President -> President



Demo II


In SMS Create a DB called Presidents -

set recovery model to: Simple


Disconnect the link between Derived Column Transformation


  1. Add a Multicast Transformation


Name it: MasterNames


Connect the Multicast to the Flat File Output


Create a new: OLE DB Destination


Link multicast to OLE DB Destination


Create a New connection to (local) that references Presidents


Click on Mappings: President to President


On Control Flow page


Create a Execute SQL Task

  1. Call it "Clear Master Names"

  2. Link to (local).Presidents


SQL Statement:  Delete FROM MasterNames


Link Clear Master Names tasks to Import Presidents




Demo III


Branch Precedence based on a script


Add a Variable (right mouse click "Control Flow" designer) called: FileFound set type to Boolean



Add a "Script Task" (note, not an ActiveX Script Task)

Name it: Check Master List


Double click the Script Task

Set Name: CheckFile


Add code from "Expressions.txt" to Design Script


Add a Sequence Container named: File Doesn't Exist


  1. Double click the Constraint Link between the Script Task and the Sequence Container

  2. Set the Evaluation Operation: Expression and Constraint

  3. Set Expression:  @FileFound == False  and test


Create another Sequence Container called "File Exists"


  1. Set the Evaluation Operation: Expression and Constraint

  2. Set Expression:  @FileFound == True and test


And run


Should execute the File Exists Sequence Container


Link the "File Doesn't Exist" Sequence Container to the "File Does Exist" Sequence Container


On the constrain from the Script Task to the "File Does Exist Sequence Container

Set the Constraint to be a Logical OR


Delete the PresidentsDestination.txt and rerun to show execution of both branches




Demo IV


Add a Data Flow Task to the "File Exists" Sequence Container

Name it: Lookup Names


Create a new Flat File Source object

Name it: Input Names

Point it to c:\data\InputA.txt

Colums: Set Column Name: Name

Set column width to: 250


Add a new Fuzzy Lookup Transformation

Link to Input Names Flat File Data Source


Double click the Fuzzy Logic Transform

Link it to (local).Presidents

Generate a new index based on the MasterNames table


Click columns tab and link Presidents  - Check this...


Create  a new OLE DB Destination

Name it: MatchedNames

Create a new table: [MatchedNames]


Click on Mappings to set up the source and destination


Discuss Dataviewers - but don’t run as currently knackered




Show single execution


Back in the File Exists Sequence container


Add a Foreach Container


Set file path to c:\data

Set file names: Input*.Txt


Click Variable mapping; Create a new Package Scope Variable called FileName of type String


On "Input Names" Connection Object - go to properties, Expressions and add a Expression for ConnectionString, click on variables, select User:FileName and drag in to the Expression page and then close..


Execute the package, you'll now see the back go yellow and cycle green on the foreach loop container...




Comments (4)

  1. Been a bit quiet on here as have been getting ready for our SQL Server April 05 MSDN Update across…

  2. Been a bit quiet on here as have been getting ready for our SQL Server April 05 MSDN Update across…

Skip to main content