DTS - How would I achieve this?

Question

Post reply

DTS - How would I achieve this?

SATISD9X

Ten Centuries

Points: 1226
More actions
August 1, 2008 at 1:13 am

#72774

Hi,
I want to create a DTS package which has the following requirement:
I have a JOIN Query for 3 tables which fetches few records from Source DB.
I want to loop through the result set which is fetched from the Source DB.
At each record it should check for a specific condition. (COND 1)
If it doesn't satisfy the condition (COND 1) then, it should ADD record (Row of fetched Resultset) in two different tables in the Destination DB.
If the condition (COND 1) satisfies then, it should check for another condition (COND 2).
If it doesn't satisfy the condition (COND 2) then, it should Update the details (Row of fetched Resultset) in two different tables in the Destination DB.
If the condition (COND 2) satisfies then, it should ADD record (Row of fetched Resultset) in one table in the Destination DB.
How would I achieve this?
(I've created two connection [one for Source DB and another for Destination DB] connected with a 'Transform Data Task'.
Also, I've created 'Execute SQL Task' in which I've written the JOIN query of 3 tables which will fetch me the desired output.)
Regards,
Satish

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Matte-382450 SSC Veteran Points: 211 More actions · Answer 1

Hi Satish,

you could perhaps try to achieve it using set-based statements, something like :

--------------------------------

--rows that do not satisfy COND1

--------------------------------

insert into dest_table_1 (colname1, colname2, ...)

select sourceqry.column_x, sourceqry.column_y, ...

from ( ==>insert your join query from the sourcedb here<== where not COND1) as sourceqry

insert into dest_table_2 (colname1, colname2, ...)

select sourceqry.column_x, sourceqry.column_y, ...

from ( ==>insert your join query from the sourcedb here<== where not COND1) as sourceqry

---------------------------------------

--rows that satisfy COND1 but not COND2

---------------------------------------

update dest_table_1 set .....

from dest_table_1 as destin, ( ==>insert your join query from the sourcedb here<== where COND1 and not COND2) as sourceqry

where ....

update dest_table_2 set .....

from dest_table_2 as destin, ( ==>insert your join query from the sourcedb here<== where COND1 and not COND2) as sourceqry

where ....

----------------------------------------

--rows that satisfy both COND1 and COND2

----------------------------------------

insert into dest_table_3 (colname1, colname2, ...)

select sourceqry.column_x, sourceqry.column_y, ...

from ( ==>insert your join query from the sourcedb here<== where COND1 and COND2) as sourceqry

Depending on the speed of your join query and the number of rows returned you could insert your recordset in a temporary table first which you would then use in stead of the subqueries.

Hope this helps

Matte

SATISD9X Ten Centuries Points: 1226 More actions · Answer 2

Hi Matte,

ThanX for your prompt reply.

I need to do this since the Source and the Destination Servers are not on the same box and are faar away.

I want this to be resolved using DTS only.

ThanX in anticipation.

Regards

Satish

Matte-382450 SSC Veteran Points: 211 More actions · Answer 3

That's what I meant,

the code should be put in your 'Execute SQL Task' where you access one DB as local and the remote as a linked server (see sp_addlinkedserver).

Matte

SATISD9X Ten Centuries Points: 1226 More actions · Answer 4

ThanX Matte,

I'll be trying this and get back to you in case need be.

ThanX again. 🙂

SATISD9X Ten Centuries Points: 1226 More actions · Answer 5

Hello,

I'm trying to achieve this by the above probable solution ..

but I found one things that my DestinationDB is connected to a different connection ...

and when I am writing SQL query in 'Execute SQL Task' .. it is pointing to any one of the Connection (in this case Connection 1 ==> Source DB) and I am trying to insert in the a different Connection (in this case Connection 2 ==> Destination DB)

How would I write a SQL query which will insert the query by pointing on Connection 1 (Source DB) and still be able to insert in Connection 2 (Destination DB)?

ThanX

Satish

Matte-382450 SSC Veteran Points: 211 More actions · Answer 6

Hi Satish,

You can do this the same way you would do it in a query analyzer session

I don't know the name of your instances but let's call them 'farawaydb' for the source and 'nottofarawaydb\instance1' for the destination server.

Just to test the way to do it try this :

Start a Query Analyzer session connecting to your destination server (nottofarawaydb\instance1)

SELECT * FROM Northwind.dbo.Employees

this should give thecontents of the given table on the destination-server

now try

SELECT * FROM [farawaydb].Northwind.dbo.Employees

-- this probably results in an error like

Server: Msg 7202, Level 11, State 2, Line 1

Could not find server 'farawaydb' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

if not the server was already added and te results are the rows on the source-server

if so execute

exec sp_addlinkedserver @server = 'farawaydb'

go

SELECT * FROM [farawaydb].Northwind.dbo.Employees

go

this time, if the sp_addlinkedserver worked, it should give the contents of the given table on the source-server

if you want you can drop the linkedserver using

exec sp_dropserver [farawaydb]

but you can leave it for future use

With this (sqlserver2000) example and a reading of the BOL topic on the sp_addlinkedserver procedure you should be able to work it out, if not, post again

Matte

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

s_chilkury (8/1/2008)
I want this to be resolved using DTS only.

Why?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)