DTS and stored procedure

Hello there,

I have been reading through Online Books and many postings and still
am not sure how I should go about doing this.

I have SQL Server 2000 table tblENTRY and an AS400 table TKMAST.
TKMAST receives data and is maintained by an outside source. I need
to get the received data from TKMAST into tblENTRY. I can obtain the
data easily using the DTS. But what I can't figure out is how to
insert or update data in tblENTRY.

So, at this point I have a DTS package that creates a table using this
statement:
CREATE TABLE [FromTKMAST] (
[TKFILR] char (3) NOT NULL,
[TKENT#] numeric (7,0) NOT NULL,
[TKCKDG] numeric (1,0) NOT NULL,
[TKCLCD] char (6) NOT NULL,
[TKFILE] numeric (6,0) NOT NULL,
[TKDENY] numeric (2,0) NOT NULL,
[TKDENM] numeric (2,0) NOT NULL,
[TKDEND] numeric (2,0) NOT NULL
)

[TKENT#] is the primary key

I was thinking that I should do a trigger on FromTKMAST to update or
insert into tblENTRY but I feel like that is a slow and poor
solution. I feel like there is a capability in the DTS that I don't
see or understand. Is there a way to execute a stored procedure from
a DTS package? It seems like this must be a common task.

Breaking it down, first I need to get the data (dts package), then I
need to run a stored procedure that will take that data and insert or
update it in tblENTRY. This process needs to be automated...it needs
to check for new or updated records in TKMAST every five minutes or
so. It seems like such a basic operation, but I can't figure out
where and how I go about doing this. Where would I put the stored
procedure, if that is how I should do it? Any help would be greatly
appreciated.Have you read the Books Online that comes with SQLserver, there is a
chapter Using ActiveX Script in DTS. It looks like the solution. I
have a similar task but have not get time to do it.|||I think u r looking for DTS - EXECUTE SQL TASK, you can run any query
using this which run through query analyzer.

-Hayat
www.mysticssoft.com

phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0310021110.3096f1ff@.posting.google.com>...
> Hello there,
> I have been reading through Online Books and many postings and still
> am not sure how I should go about doing this.
> I have SQL Server 2000 table tblENTRY and an AS400 table TKMAST.
> TKMAST receives data and is maintained by an outside source. I need
> to get the received data from TKMAST into tblENTRY. I can obtain the
> data easily using the DTS. But what I can't figure out is how to
> insert or update data in tblENTRY.
> So, at this point I have a DTS package that creates a table using this
> statement:
> CREATE TABLE [FromTKMAST] (
> [TKFILR] char (3) NOT NULL,
> [TKENT#] numeric (7,0) NOT NULL,
> [TKCKDG] numeric (1,0) NOT NULL,
> [TKCLCD] char (6) NOT NULL,
> [TKFILE] numeric (6,0) NOT NULL,
> [TKDENY] numeric (2,0) NOT NULL,
> [TKDENM] numeric (2,0) NOT NULL,
> [TKDEND] numeric (2,0) NOT NULL
> )
> [TKENT#] is the primary key
> I was thinking that I should do a trigger on FromTKMAST to update or
> insert into tblENTRY but I feel like that is a slow and poor
> solution. I feel like there is a capability in the DTS that I don't
> see or understand. Is there a way to execute a stored procedure from
> a DTS package? It seems like this must be a common task.
> Breaking it down, first I need to get the data (dts package), then I
> need to run a stored procedure that will take that data and insert or
> update it in tblENTRY. This process needs to be automated...it needs
> to check for new or updated records in TKMAST every five minutes or
> so. It seems like such a basic operation, but I can't figure out
> where and how I go about doing this. Where would I put the stored
> procedure, if that is how I should do it? Any help would be greatly
> appreciated.