SSIS Project: Updating PersonContact data


Source: One flat text file and one Excel file

Destination: SQL Database Campaign

Purpose: to load new data from two sources into a database


Reference: SQL Server 2005 Integration Services, McGraw Hill Osborne, 2007


ETL data flow diagram

(Note: PersonDetails01 & PersonDetails02 are in the network drive ~\ISQS3358\Downloaded\SSIS\RawFiles.

PersonDetails02 could be saved as a txt or csv file for the project because the Excel files have problem in current

Citrix SQL Server. )


Executed outcomes


To accomplish the above project you need:

1)      Database Campaign

2)      The source datasets

3)      The demonstrative project files


Stage 1: Preparation


  1. Copy subdirectory \Downloaded\SSIS\PROJECTS\BACKUP\DATA FLOW TRANSFORMATIONS in the shared ISQS 3358 network drive \\TechShare to your local drive.
  2. Check the data tables of Campaign in oredb


Stage 2: Hand-on: Updating PersonContact Data


Exercise 2.1: Add data Sources


  1. Create a new project “Updating PersonContact data”
  2. Drag and drop a Data Flow task to the Control Flow Designer surface
  3. In the Data Flow tab, drag and drop a Flat File source and Excel source from the Toolbox onto the Designer surface
  4. The source text file is PersonDetail01.txt in the newly copied directory. The DataType of output of Flat File must be string[DT_STR]. The length of each column must follow the same as that in Campaign database (see below)



  1. The Excel source is PersoneDetails02.xls. Define the Excel Source node.
  2. Save the results.


Exercise 2.2: Combine Two Data Stream


  1. Drop a Data Conversion transformation from Toolbox onto the data flow surface. Join the Excel Source with this one using the green arrow.
  2. Configure the task to follow the Data Type in the above table.
  3. Drop the Union All transformation from the Toolbox onto the Data Flow surface. Check its configuration


Exercise 2.3: Derive Salutation


  1. Use Derive Column transformation. Connect green arrow of Union All to this one. Check its configuration.
  2. You need to add in the following expression in the Expression field of Derived Column Transformation Editor:


Gender == “m” ? “Mr.” : (Gender == “f” && Married == “y” ? “Mrs.” : “Miss”)


  1. Select strig[DT_STR] as the data type



Exercise 2.4: Enrich Address Details


  1. Drop a Character Map transformation from the Toolbox on the Data Flow Designer surface. Join it from Deriving Salutation using the green arrow.
  2. Check the Postcode column of the configuration for Character Map. Change the data in the column to uppercase.
  3. Connect a Lookup transformation after Character Map. You need to do:
    1. Define Campaign in Reference Table tab. Select [dbo.PostalCity] from the list of the tables
    2. Check City column in column tab to add this column as a new one in the output
    3. Those records not having matches of cities will fail the Lookup transformation. The error messages will be directed to a flat file for review
  4. Create a txt file as the error output file.
  5. Connect red arrow to a newly created Flat File task. Configure the task.







Exercise 2.5: Delete Duplicates and Load PersonContact


  1. Use OLE DB command tasks to configure the Delete Duplicates.
  2. In SqlCommend under Component Properties (see the figure), type in


Delete PersoneContact Where Firstname = ? and Lastname = ?


  1. The outcomes of next figure show how the parameter in the SQL command is processed.


  1. Drop an OLE DB destination to the editor. Connect it from the last task. Select Campaign as the destination database and PersonContact is the table to be loaded.


Exercise 2.6: Execute the Package


F5 = Execution

Shift-F5 = Stop debugging