Need to create a Workflow Decision Tree

[mkal]

I have a dts package that processes 3 data files, each of the three files need to be loaded into 3 corresponding tables. The three tables have relaionships where one table is the parent and the other two are children.

So here's my problem: If the parent file exits and both child files exist I will need to truncate both children first, then truncate the parent, then load the parent, then load both child tables. But all three files are rarely sent on the same day so I have to plan for any eventuality.

Here are the possible combinations WHERE 0 = File doesn't exist and 1 = File does exist.

P C1 C2
0 0 0
0 0 1
0 1 0
0 1 1
1 0 0
1 0 1
1 1 0
1 1 1

What I am trying to figure out is a workflow for the SQL Tasks that truncate and load the tables, without creating duplicate tasks for the package.

Example:

DTSTask_TrunctateChild1 & DTSTask_truncateChild2>>on successOfBoth>>DTSTask_TruncateParent>>on success>>DTSTask_LoadParent>>on success>>DTSTask_LoadChild1 & DTSTask_LoadChild2. This would take care of the last condition where all 3 files exist. I can create 7 more just like it but it seems to me there should be another way. I've looked into using the execute method of the Task/Step objects at run time but I my understanding is this is probably a bad idea.

Any suggestions are welcome.

 

[JohnDTampaBay]

How about this?

1-Create a import status table including file type (P,C1,C2) and import status (1,0).

2-Create 3 import packages (1 for each file type) to check the status table to see if the file has been imported. If not, check for the existence of the file. If exists, import the file and update the status table.

3-Create a 4th package that runs each of the 3 file packages, then checks the status table. If all 3 file have been imported, process the data in the 3 tables and reset the status table.


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[mkal]

I like the idea of the status table. Had hoped to get away with just the a single package but in the long run this may work out better.

Thanks!