DTS problem (ONE source table to Multiple tables)

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 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS problem (ONE source table to Multiple tables)

Author

Topic

tutufool
Starting Member

14 Posts

Posted - 2006-08-16 : 07:26:31

hello,
I want to do some DTS job which will transfer data from one table to several tables. How can I leverage DTS tasks? I saw that transform data task only support ONE target table.
here's the case:
Source table:[User]
Target table:[Person],[Address],[Userlogin]

I need fetch data of [User], insert some fields into [Person], then get the primary key (auto increment), then insert rest data into [Address] and [Userlogin].

thanks in advance!

tutufool
Starting Member

14 Posts

Posted - 2006-08-16 : 07:27:20

forgot one thing: I'm using sql server 2000

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-16 : 11:36:12

I can't see any benefit in doing this in DTS. Create a stored procedure to do it.

Mark

tutufool
Starting Member

14 Posts

Posted - 2006-08-17 : 00:04:52

quote:
Originally posted by mwjdavidson

I can't see any benefit in doing this in DTS. Create a stored procedure to do it.

Mark

hi, mark
thanks for the input.
The idea of using DTS is because we may use plat file as intermedia between 2 databases. I found that DTS can support text file data source, so I ......
We may do things like: download file from ftp, parse file and do data sync, sending email to somebody, blabla..... DTS seems to support these well

As you said, stored procedure is a better choice. But I'm not familar with how to process filesystem in stored procedure, and I may parse the file myself.Yes,I know I'm a little lazy:)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-17 : 04:34:32

Hi
Could you provide a bit more detail? I.e. are these databases on different servers? Are they using different RDBMSs (i.e. Oracle, DB2, etc.)?
If you need to import a flat file in DTS, I'd suggest using a staging table as the destination and then firing a stored procedure from an ExecuteSQL task that reads from it.

Mark

tutufool
Starting Member

14 Posts

Posted - 2006-08-17 : 09:52:33

quote:
Originally posted by mwjdavidson

Hi
Could you provide a bit more detail? I.e. are these databases on different servers? Are they using different RDBMSs (i.e. Oracle, DB2, etc.)?
If you need to import a flat file in DTS, I'd suggest using a staging table as the destination and then firing a stored procedure from an ExecuteSQL task that reads from it.

Mark

hi mark
thanks for the quick reply:)
these database are on diff servers and both are sql server 2000.
As you suggested, I'm using stored procedure to handle each record.

But I got a problem:
when there's something wrong in the stored procedure, for example, insert null values. I rollback all the insert statments and return "0". While the Execute SQL task still take this procedure execution as a failure, then the loop finished. (I did not check the "fail package on step failure")
I intended to skip the bad record and goto next record, how can I let
the Execute SQL task know that the exec result is success?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-17 : 10:05:09

Hi,
As suggested, don't do this row-by-row. Get the data from your source server into a staging table on your destination server in one hit, then have a stored procedure that performs inserts based on business rules (i.e. exclude rows with a null value in a particular column).

Mark

tutufool
Starting Member

14 Posts

Posted - 2006-08-17 : 10:24:06

quote:
Originally posted by mwjdavidson

Hi,
As suggested, don't do this row-by-row. Get the data from your source server into a staging table on your destination server in one hit, then have a stored procedure that performs inserts based on business rules (i.e. exclude rows with a null value in a particular column).

Mark

Mark, thanks
I'll try your appoach, and by the way, how can I do loop in stored
procedure? using cursor?

Anyway, I still want the answer of my last question if you can help

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-17 : 13:58:00

Hi, I'm not really following... Why do you need to use a cursor? I'm suggesting you use a single stored procedure to update the target tables rather than firing a stored procedure for each row in your source table. Let me know if there's some reason why this is not possible.

Mark

tutufool
Starting Member

14 Posts

Posted - 2006-08-17 : 23:36:17

quote:
Originally posted by mwjdavidson

Hi, I'm not really following... Why do you need to use a cursor? I'm suggesting you use a single stored procedure to update the target tables rather than firing a stored procedure for each row in your source table. Let me know if there's some reason why this is not possible.

Mark

hi,mark
sorry for make you confused
upon your suggestion, I should do something like this in stored procedure:
"
recordset = select * from [user]
for each record in recordset
{
insert into [person]
parse user's address
parse user's phone into areacode and localnumber
some other logic....
insert into [address]
insert into [UserLogin]
insert into some other tables......
}
"
so I can't simply use something like:
INSERT INTO Address
SELECT P.PersonID, U.AddressLine1, U.City, U.State, U.Zipcode
FROM Users U
INNER JOIN Persons P
ON Users.userID = Persons.UserID

I'd like to process the record one by one, if there's some bad data, I'll log it in somewhere and email to somebody.

I'm not so familar with stored procedure, can you give me some advice?

redzex
Starting Member

3 Posts

Posted - 2006-08-23 : 03:10:36

define what 'bad data' is... i might help you

tutufool
Starting Member

14 Posts

Posted - 2006-08-28 : 07:59:22

quote:
Originally posted by redzex

define what 'bad data' is... i might help you

hi
"bad data" is the data that make our data transformation failed.
I need to log the id, error message of the data and then notify somebody to transfer it manually.
for example, the user id may exist in the target database, we need modify the source user id manually and send email to user, tell him his user id changed.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 08:30:54

quote:

Originally posted by tutufool

recordset = select * from [user]
 for each record in recordset
     {
       insert into [person]
       parse user's address
       parse user's phone into areacode and localnumber
       some other logic....
       insert into [address]
       insert into [UserLogin]
       insert into some other tables......
     }

Most operations in SQL Server is COLUMN based, not ROW based. Your code should look something like

recordset = select * from [user]
 for each record in recordset
     {
       insert into [person] select id, firstname, lastname from source
       insert into [address] select id, dbo.fnParseAddess(Address) FROM source
       insert into [UserLogin] select id, dbo.fnParseLogin(SomeCol) FROM source     }

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources