Date:         Wed, 23 Jun 2004 12:04:07 -0600
Reply-To:     Alan Churchill <EmailMeDirectly@ThisWebSite-erratix.us>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Alan Churchill <EmailMeDirectly@THISWEBSITE-ERRATIX.US>
Subject:      SQL Server and DTS

All,

I've been meaning to get back to the SQL Server and the use of DTS. SAS 9.12
install hosed me for awhile so just got my environment back up.

For those interested, here is what I have found:

- Use the SAS Access to OleDB engine
- Do not use the export function in Enterprise Manager, instead use the DTS
designer directly. This is an issue with DTS, not SAS (from what I have
          found)
- Do not use the SAS Local Provider, it doesn't support SQL statements and
therefore DTS cannot talk to it
- Use the SAS IOM Provider. I used 9.1 and I would urge people to use this
version if possible.
- Define a SAS library to use in your config file so it is allocated when
SAS is started
- Define the following for starters in the 9.1 provider:
        Machine name: use your local machine name
        ProtocolCom
- Make sure you then create the table using the permanent library that you
set in the config file. For example, SASHELP.TESTDATA (no, don't flame me. I
am not suggesting to actually store stuff in SASHELP...)

I was able to get it to work. There would, of course, be nuances to the
above but hopefully this will provide a starting point. The new DTS in
yukon, hopefully, will make it a little better.

--
Alan Churchill

Savian
"Bridging SAS and Microsoft technologies"
(719) 687-5954