Design this flow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Design this flow

Author

Topic

hrishy
Starting Member

47 Posts

Posted - 2008-05-09 : 16:12:00

Hi

I am SSIS newbie and need help in desigining this flow.

Source Sqlserver2005

Select records based on complex sql statement from a
[InstanceA].[DatabaseA].[TableA]

Target Sqlserver2005

Insert the records into another table
[InstanceB].[DatabaseB].[TableB]
only if these records are not present

Take the records from [InstanceB].[DatabaseB].[TableB]
and insert it into [InstanceB].[DatabaseB].[TableC]
only those records which are not in C

And finally

Take the records from [InstanceB].[DatabaseB].[TableB]
Join them with [InstanceB].[DatabaseB].[TableC]
and insert it into [InstanceB].[DatabaseB].[TableD]
only those records which are not already in D

Can somebody please help me in visualising this solution .

I am having problems populating a target and then using that populated target as a source for subsequent targets.

regards
Hrishy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-10 : 02:11:29

You can use a dataflow task for this.
Inside the daytaflow you will have :-

1. OLEDB Source- where you get data from [InstanceA].[DatabaseA].[TableA]
2.OLEDB Destination- Dump this data into a temporary table created in InstanceB. Maybe [InstanceB].[DatabaseB].[Staging]
3. OLEDB Command - inserting the new records into TableB
command is
INSERT INTO TableB
SELECT your required fields here
FROM Staging s
LEFT JOIN TableB t
ON t.PKCol=s.PKCol
WHERE t.PKCol IS NULL
4.OLEDB Command - inserting the new records into Tablec from TableB
command is
INSERT INTO TableC
SELECT your required fields here
FROM TableB tb
LEFT JOIN TableC tc
ON tc.PKCol=tb.PKCol
WHERE tc.PKCol IS NULL
5.OLEDB Command - inserting the new records into TableD from B & C
command is
INSERT INTO TableD
SELECT your required fields here
FROM TableB tb
INNER JOIN TableC tc
ON tc.PKCol=tb.PKCol
LEFT JOIN TableD td
ON td.Col=tc.Col
WHERE td.PKCol IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources