I am sort of new at DTS. We use SQL Server 2000.

I have created a DTS package that 1) using a Data
Transform Task queries our Interaction table for employee
data and inserts into iCreate_Author1.

2) Then using an Execute SQL Task runs the following
insert query:

INSERT INTO iCreate_Author2
(AuthorID, FirstName, MiddleName, LastName, Initials,
OfficeID, Department, UserID, Closing, ClosingName,
FontPreference, DirectNo, DirectNoExt,
GeneralNo, FaxNo, MobileNo, PagerNo, Email, EmployeeType,
BillingNo, DefaultBarID, Title, User1, User2, User3, User4)
SELECT NULL, FirstName, MiddleName, LastName, NULL,
(SELECT OfficeID = CASE WHEN AreaCode = '612' THEN 6
WHEN AreaCode = '651' THEN 2 WHEN AreaCode = '303' THEN 1
ELSE 6 END), NULL, NULL,
NULL, NULL, NULL, DirectNo, NULL,
(SELECT GeneralNo = CASE WHEN AreaCode = '612'
THEN '(612) 371-3211' WHEN AreaCode = '651' THEN '(651)
312-1300' WHEN AreaCode = '303'
THEN '(303) 573-5900' ELSE '(612) 371-3211' END),
(SELECT FaxNo = CASE WHEN AreaCode = '612' THEN '(612)
371-3207' WHEN AreaCode = '651' THEN '(651) 223-5332' WHEN
AreaCode = '303' THEN
'(303) 573-1956' ELSE '(612) 371-3207' END), NULL, NULL,
Email,
(SELECT EmployeeType = CASE WHEN Title = 'Paralegal'
THEN 3 WHEN Title = 'Equity
Partner' THEN 1 WHEN Title = 'Non-Equity Partner' THEN 1
WHEN Title = 'Retired Partner' THEN 1 WHEN Title = 'Of
Counsel' THEN 1 WHEN Title = 'Summer
Associate' THEN 1 ELSE 2 END),
BillingNo, NULL, Title, NULL, NULL, NULL, NULL
FROM iCreate_Author1

Then I want to run two different queries depending on if
the record yet exists in the Author table (the final
destination table). If the record exists (based on
BillingNo), then update fields (because certain fields
will have data directly entered into the Author table).
If the record does not exist, add a new record including
some other fields where I want to fill in defaults but not
overwrite if the Employee exists.

The second query I created in an Execute SQL Task because
it doesn't transform data - it inserts it. I cannot
figure out a way to connect it to my Author table and to
insert and update the data. My connections are:

Two 'execute SQL Tasks' - one to create author1 and one to
create author2. Each have a workflow to a connection for
my Interaction server. I then have two connections for
Interaction with a Transform Data Task between them where
I run my first query. Then between the 2nd connection and
the 2nd SQL (where I insert to author2) I have a
workflow. I have been unable to connect my 2nd SQL (where
I insert to author2) to another connection for
Interaction, to one for my Access Author table, - I don't
know what to use - a work flow? I think I have to run my
2nd query in an execute SQL Task because it inserts and
doesn't transform data.

Can anyone point me in the right direction on what to do
now?

Thanks in advance!
Mary

OK

You have your Working tables and your final table

You want to test the records in your final table to see if the records in
your working tables already exist.

This should get you started

--New Records
INSERT INTO FinalTable
SELECT <col list> FROM WorkingTable WT LEFT OUTER JOIN FinalTable FT ON
WT.<key> = FT.<key>
WHERE FT.<key> IS NULL

--UPDATES
UPDATE FT
SET <col list>
FROM FROM WorkingTable WT JOIN FinalTable FT ON WT.<key> = FT.<key>




--

----------------------------
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 Methven" <mmethven (AT) lindquist (DOT) com> wrote

Thanks for the information. That does help. Do you know
if, to do an INSERT or UPDATE query, if I need to do it in
an Execute SQL Task? Or if I should do it in some other
type of task? The SQL Task is fine but I am having
trouble connecting it to my Access connection. That is
the problem I'm now running into.