April 28, 2009 at 6:50 am
[font="Courier New"]Question regarding SSIS 2005 Lookup Task:
Using an XLS spreadsheet as input, I need to populate child table CHILD_TBL_T.
During the population, I need to reference Parent table PARENT_TABLE_T to determine the FK value for the Child table column called TEST_CATEGORY_ID --
the join would be on: PARENT_TABLE_T's Description = XLS's test_category
Is it possible to create a Data Flow, utilizing an XLS input and a 2nd table in a LOOKUP task?
Or will I need to first create a #temp table then use this in the join process?
PARENT_TABLE_T
CHILD_TBL_T
***
*** PARENT TABLE:
***
CREATE TABLE [dbo].[PARENT_TABLE_T] ([TEST_CATEGORY_ID] [int] IDENTITY(400,1) NOT NULL,
[TEST_CLASS_ID] [int] NULL,
[DESCRIPTION] [varchar](255) NULL
PK= [TEST_CATEGORY_ID]
DATA in: PARENT_TABLE_T
test_category_id test_class_id description
---------------- ------------- -----------
1 21 ExploreAA
2 21 EDIT
3 21 NonTength
4 21 Other
5 21 APPLE
6 21 Category ABC123
7 21 Tength
8 22 Water
***
*** CHILD TABLE:
***
CREATE TABLE [dbo].[CHILD_TBL_T]([TEST_TYPE_ID] [int] IDENTITY(1,1) NOT NULL,
[DESCRIPTION] [varchar](255) NOT NULL,
PK= [TEST_TYPE_ID] [TEST_CATEGORY_ID] [int] NULL,
[varchar](50) NULL
DATA (XLS) to be loaded into CHILD_TBL_T
********************************************
>>> CHILD_TBL_T <<< (XLS)
********************************************
description test_category code
----------- ------------- ----
APPLE TSA - Wall/Bench Tength APPLE
SMA/SAS TSA Tength Tength
Discount NonTength NONTength
Required Water CON
TrainingABC Water TPC
Category ABC123 Category ABC123 APPLE
[/font]
April 28, 2009 at 8:15 pm
Sounds like a MERGE JOIN transformation might give you what you want, taking as inputs the spreadsheet and the parent table and configuring the join as you have detailed ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 28, 2009 at 9:46 pm
What is wrong with using the SSIS Lookup task ? It does exactly what you need.
April 28, 2009 at 10:04 pm
happycat59 (4/28/2009)
What is wrong with using the SSIS Lookup task ? It does exactly what you need.
I agree - Lookup is better - why didn't I think of that? Think I need caffeine.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 29, 2009 at 6:07 am
Does anyone have a good link describing how to create the LOOKUP Task?
Again, I need to populate my Child table FK by joining a Parent table's DESCRIPTION col to my spreadsheet COL X where Spreadsheet col Y = Child table col Z
April 29, 2009 at 6:26 am
It's really easy.
On your Data Flow pane, add a lookup transformation.
Connect the output of your flat file source to it.
Connect it to the next task on your data flow (possibly OLE DB destination)
Double click on the lookup. Configure the connection manager to connect to your SQL Server db and select the parent table as the 'Use a table or view'.
Click the Columns tab. Click and drag between the relevant fields in the 'input' and 'lookup' tables to create a join.
Then drag the FK field down from the Lookup table to the 'Lookup Column' in the lower part of the screen and set an alias if required.
That's it. This new field will now be available for mapping when you double click your destination component - so go ahead and map it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply