Partager via

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

  • Article

 

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

  • Anonymous
    April 14, 2005
    Been a bit quiet on here as have been getting ready for our SQL Server April 05 MSDN Update across...
  • Anonymous
    August 08, 2005
    Been a bit quiet on here as have been getting ready for our SQL Server April 05 MSDN Update across...