SSIS Project: Updating PersonContact
Source: One flat text file and one Excel file
Destination: SQL Database Campaign
Purpose: to load new data from two sources into a database
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
Citrix SQL Server. )
To accomplish the above project you need:
The source datasets
The demonstrative project files
Stage 1: Preparation
subdirectory \Downloaded\SSIS\PROJECTS\BACKUP\DATA FLOW TRANSFORMATIONS in
the shared ISQS 3358 network drive \\TechShare to your local drive.
the data tables of Campaign in oredb
Stage 2: Hand-on: Updating PersonContact
Exercise 2.1: Add data Sources
a new project “Updating PersonContact data”
and drop a Data Flow task to the Control Flow Designer surface
- In the
Data Flow tab, drag and drop a Flat File source and Excel source from the
Toolbox onto the Designer surface
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)
Excel source is PersoneDetails02.xls. Define the Excel Source node.
Exercise 2.2: Combine Two Data Stream
- Drop a
Data Conversion transformation from Toolbox onto the data flow surface.
Join the Excel Source with this one using the green arrow.
the task to follow the Data Type in the above table.
the Union All transformation from the Toolbox onto the Data Flow surface.
Check its configuration
Exercise 2.3: Derive Salutation
Derive Column transformation. Connect green arrow of Union All to this
one. Check its configuration.
need to add in the following expression in the Expression field of Derived
Column Transformation Editor:
== “m” ? “Mr.” : (Gender ==
“f” && Married == “y” ? “Mrs.” : “Miss”)
strig[DT_STR] as the data type
Exercise 2.4: Enrich Address Details
- Drop a
Character Map transformation from the Toolbox on the Data Flow Designer
surface. Join it from Deriving Salutation using the green arrow.
- Check the
Postcode column of the configuration for Character Map. Change the data in
the column to uppercase.
a Lookup transformation after Character Map. You need to do:
Campaign in Reference Table
tab. Select [dbo.PostalCity] from the list of
- Check City column in column tab to add
this column as a new one in the output
records not having matches of cities will fail the Lookup transformation.
The error messages will be directed to a flat file for review
a txt file as the error output file.
red arrow to a newly created Flat File task. Configure the task.
Exercise 2.5: Delete Duplicates and Load PersonContact
OLE DB command tasks to configure the Delete Duplicates.
- In SqlCommend under Component Properties (see the
figure), type in
PersoneContact Where Firstname
= ? and Lastname = ?
outcomes of next figure show how the parameter in the SQL command is
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