Best apporch to load related tables based on a TransID

[Fsb]

Best approach to import tables.



I have the following databases

1. Current (only hold current month)

2. Archive

Each of these databases has the following tables;

1. Transaction Header

2. Transaction Line

3. Merch details

4. Return details

Now the header table is the only table that has transaction date in it. I
need to import a date range of data (that may be bigger than the current
month, so I need to pull from both tables). So I would need to query the
header table for a date range, get the transaction Id's and use them in all
other tables to get its data.



What is the most effect way of getting this data using SSIS 2008. I was
thinking of creating some sort of cache tables(if it possible) that holds
the transaction ID's from the header then somehow join this table of
transaction ID's with ALL tables so they can all load at the same time. Am I
on the right path?

[Todd C]

Am I on the right path?

Yes. But may I suggest a different approach:

Set up an Execute SQL task with the following:
SELECT MIN(TransactionID) as MinID, MAX(TransactionID) AS MaxID
FROM [Transaction Header]
WHERE [Date] between ? and ?
GROUP BY ...(you get the idea)

Map your two parameters to two Date variables
Set up a Single Row result set in the task and map the results (Indexed 0
and 1) to two Integer32 Variables for MinID and MaxID.

Now design your your Source adapters to use a SQL statement that utilizes
the MinID and MaxID as needed.

HTH
--
Todd C
MCTS SQL Server 2005

Best approach to import tables.
I have the following databases
1. Current (only hold current month)
2. Archive
Each of these databases has the following tables;
1. Transaction Header
2. Transaction Line
3. Merch details
4. Return details
Now the header table is the only table that has transaction date in it. I
need to import a date range of data (that may be bigger than the current
month, so I need to pull from both tables). So I would need to query the
header table for a date range, get the transaction Id's and use them in all
other tables to get its data.
What is the most effect way of getting this data using SSIS 2008. I was
thinking of creating some sort of cache tables(if it possible) that holds
the transaction ID's from the header then somehow join this table of
transaction ID's with ALL tables so they can all load at the same time. Am I
on the right path?
.