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


 


Execute


 


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