dbTalk Databases Forums  

Questiuons to build a simple DTS Package

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.dts Questiuons to build a simple DTS Package

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts


Discuss Questiuons to build a simple DTS Package in the microsoft.public.sqlserver.dts forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Michael Papenhagen
 
Posts: n/a

Default Questiuons to build a simple DTS Package - 09-14-2003 , 01:59 PM





Hallo,



I read a lot of documentation and found no soloution. I want to create an DTS Package. First I give you an overview over the actual and the target state. At the end there are the todo taks.



How can give me some tips? Thank you very much.



Michael



actual state


Source

dBase file +----------+----------+------+--+------+----+----+

Quote:
Artikel-Nr|HERSTELLER|PREIS1|AB|PREIS2|LIEF|TEXT|
+----------+----------+------+--+------+----+----+

Quote:
4711 |VW |10,00 | | |C1 |A |
+----------+----------+------+--+------+----+----+

Quote:
4712 |VW |20,00 | | |C1 |A |
+----------+----------+------+--+------+----+----+

Quote:
4713 |IBM |10,50 | | |C1 |B |
+----------+----------+------+--+------+----+----+

Quote:
4714 |VOLVO |40,50 | 5|30,00 |C1 |B |
+----------+----------+------+--+------+----+----+



Destination

Hersteller +--+----------+

Quote:
ID|HERSTELLER|
+--+----------+

Quote:
-1|KEINER |
+--+----------+

Quote:
1|VW |
+--+----------+

Quote:
2|BMW |
+--+----------+



Artikel +----------+--+----+----+

Quote:
ARTIKEL-NR|ID|LIEF|TEXT|
+----------+--+----+----+

Quote:
4708 | 2|C2 |B |
+----------+--+----+----+

Quote:
4709 | 2|C1 |C |
+----------+--+----+----+

Quote:
4710 | 1|C1 |B |
+----------+--+----+----+

Quote:
4712 | 1|C1 |B |
+----------+--+----+----+



Preis +----------+---+-----+

Quote:
ARTIKEL-NR|PID|PREIS|
+----------+---+-----+

Quote:
4708 |1 |1 |
+----------+---+-----+

Quote:
4708 |2 |1,10 |
+----------+---+-----+

Quote:
4709 |1 |1 |
+----------+---+-----+

Quote:
4709 |2 |1,10 |
+----------+---+-----+

Quote:
4710 |1 |5 |
+----------+---+-----+

Quote:
4710 |2 |5,50 |
+----------+---+-----+

Quote:
4712 |1 |30,00|
+----------+---+-----+

Quote:
4712 |2 |33,00|
+----------+---+-----+



TAB1 +----------+----+

Quote:
ARTIKEL-NR|TEXT|
+----------|----+

Quote:
4710 |B |
+----------+----+

Quote:
4712 |B |
+----------|----+



TAB2 +----------+----+

Quote:
ARTIKEL-NR|TEXT|
+----------|----+

Quote:
4710 |B |
+----------+----+









target state








Hersteller +--+----------+

Quote:
ID|HERSTELLER|
+--+----------+

Quote:
-1|KEINER |
+--+----------+

Quote:
1|VW |
+--+----------+

Quote:
2|BMW |
+--+----------+

Quote:
3|IBM | NEW
+--+----------+

Quote:
4|VOLVO | NEW
+--+----------+





Artikel +----------+--+----+----+

Quote:
ARTIKEL-NR|ID|LIEF|TEXT|
+----------+--+----+----+

Quote:
4708 | 2|C2 |B | (without changes, LIEF = C2)
+----------+--+----+----+

Quote:
4709 | 2|C1 |C | DELETE (LIEF = C1 AND NOT IN TAB1 AND / OR TAB 2)
+----------+--+----+----+

Quote:
4710 | 1|C1 |B | (without changes, Artikel not in Sourcedatabase)
+----------+--+----+----+

Quote:
4711 | 1|C1 |A | NEW
+----------+--+----+----+

Quote:
4712 | 1|C1 |A | UPDATE OF FIELD TEXT
+----------+--+----+----+

Quote:
4713 | 3|C1 |B | NEW
+----------+--+----+----+

Quote:
4714 | 4|C1 |B | NEW
+----------+--+----+----+





Preis +----------+---+-----+

Quote:
ARTIKEL-NR|PID|PREIS|
+----------+---+-----+

Quote:
4708 |1 |1 | (without changes, Lieferant = C2)
+----------+---+-----+

Quote:
4708 |2 |1,10 | (without changes, Lieferant = C2)
+----------+---+-----+

Quote:
4709 |1 |1 | DELETE (LIEF = C1 AND NOT IN TAB1 AND / OR TAB 2)
+----------+---+-----+

Quote:
4709 |2 |1,10 | DELETE (LIEF = C1 AND NOT IN TAB1 AND / OR TAB 2)
+----------+---+-----+

Quote:
4710 |1 |5 | (without changes, Artikel is in TAB 1 und / oder TAB 2
+----------+---+-----+

Quote:
4710 |2 |5,50 | (without changes, Artikel is in TAB 1 und / oder TAB 2
+----------+---+-----+

Quote:
4711 |1 |10,00| NEW
+----------+---+-----+

Quote:
4711 |2 |11,00| NEW
+----------+---+-----+

Quote:
4712 |1 |20,00| UPDATE OF PREIS (SOURCE.PREIS*1)
+----------+---+-----+

Quote:
4712 |2 |22,00| UPDATE OF PREIS (SOURCE.PREIS*1,1)
+----------+---+-----+

Quote:
4713 |1 |10,50| NEW
+----------+---+-----+

Quote:
4713 |2 |11,55| NEW
+----------+---+-----+

Quote:
4714 |1 |40,50| NEW
+----------+---+-----+

Quote:
4714 |2 |44,55| NEW
+----------+---+-----+







1. DELETE * FROM ARTIKEL WHERE LIEF="C1" AND ARTIKEL-NR (NOT IN TAB1 OR NOT IN TAB2)

2. DELETE * FROM PREIS WHERE LIEF="C1" AND ARTIKEL-NR (NOT IN TAB1 OR NOT IN TAB2)

3. Fill Database Hersteller (Redout highest ID + 1) without setting column identity to YES!!!!!

4. Fill (INSERT & UPDATE) of database Artikel including readout of the ID from datase Hersteller

5. Fill (INSERT & UPDATE) of database Preise. Every Artikel-Nr must be write twice into database with PID 1 + PID 2.

i. PID 1 = PREIS from source database

ii. PID 2 = PREIS from source database * 1,2





Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top