Import emailed flat file from outlook 2000 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Import emailed flat file from outlook 2000

Hi, I need to import a daily table dump from a group of older sybase databases (sybase ver 4.91 running on vms with the decnet networking protocol). Because of the age of these servers I have not been able to find odbc drivers for them. I currently have a batch job on the sybase server email the table every day, and I copy it to a text file, remove the header info, and import it by hand into sql server. My sql server has its own email account. Would it be possible to use dts to read these emails strip the header, and import the data into a table based on the message subject? In addition, would someone please suggest a good book on dts. Thanks
John
I haven’t seen any such utility to read emails from SQL server.
As your process includes from sybase to export table to a text file and you can set the DTS to exclude first row, after you import from the table use a simple query to rearrange the data to the originating table in SQL Server. Have you referred thru SYBASE website for the driver help? Chekc thruhttp://www.sqldts.com – website with handful resources about DTS. Take help of Microsoft SQL Server 2000 Data Transformation Services (DTS) by Timothy Peterson and I think ISBN: 0672320118 is the number and check under Amazon for details.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I have this problem about 90% solved. I had no idea you could run executables with DTS until I went to the website you gave me. I wrote a small visual basic program to read the emailed table dumps in outlook, format the data, and export it to a text file. Once the data was in a text file DTS could easily import it to my table. I did talk to Sybase about drivers but they were not much help because the version I’m running is very old. Thanks for the website it has been a big help.
Glad to know atleast some route is found to get the data, and may search under google for the referenced drivers. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Well it works but not when scheduled. The program that reads the email fails. I believe this is becuase scheduled dts jobs are run by the local system account because of the it can’t access the correct email account. Is there a way to run scheduled dts jobs as another user? This will work when I execute the package by hand.
No, only when SQLAgent account is having necessary privileges to execute those job. And when you execute manually it will run under your privileges context. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>