I have looked at Books Online and am now reading through SQL Server DTS
from Magenic. I have used DTS to do simple imports, but now I have need
to make use of the tasks, I just can't figure out how to go about it. I
have done quite a bit of messing with it, but I am running out of mess
time and am not much closer to figuring out my solution.

I have broken down each requirement and am trying to figure out how to
use a task to meet each one. But since I have no working examples to
look at, I am not being very successful. If someone has any time to
look at the following and give me some ideas, I would be very, very
grateful.

My first requirement is to pull data from an AS400 table called TKMAST.
This is actually where my first wall comes up. Connection 1 is using a
DSN and an ODBC datasource. The destination connection is a SQL Server
table.

My select query starts SELECT TKENT#, TKFILR, TKDENY, TKDENM, TKDEND,
TKFILE, TKCKDG, TKCLCD from TKMAST... (This is where I am first
baffled). I need to get records from TKMAST for customers that exist in
a SQL Server table other than my destination table.

I thought I should finish the above select statement with "WHERE TKCLCD
= ?" and then make a global variable for TKCLCD which is a CustomerID.
If this IS what I should do, how do I make the task know where to look
for each CustomerID? How do I make the SELECT statement look at another
table to get valid values?

Once I get a clean record set from the datasource, how do I go about
inserting and updating records into tblENTRY and at the same time insert
TKENT# into another sql table called tblFILE if TKENT# doesn't exist for
that TKFILE? Would I write a stored procedure and put it in an
ActiveScript task or should it go in the Execute SQL task?

Or should I first put my records from my source into a temp table and do
stuff from there? I am sorry to post such a long question, but I am
trying to understand this concept and have no one else to ask.

A version of this question posted from Google Groups into Dev Dex
sqlserver. I noticed this forum and decided to post here.





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

OK Couple of ways to do what you want

1. Bring the table (AS400) down lock stock and smoking barrel to SQL
Server. Use TSQL to do everything from there.

Good:

TSQL = Super fast

Bad:

If the AS400 table is large it may take some time.

2. Use Lookups.

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277)

You can use as many as you want and they do all the statements you need

Good:

One pass of the data, does not require bringing the whole table to SQL
Server

Bad:

operates on every row, row by row. So can be slow.


--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Mary Rowan" <phantomtoe (AT) yahoo (DOT) com> wrote