DTS Schedule Fails - ODBC Connection to Progress Database

using a Merant 3.70 32-bit Progress ODBC driver, i can append data to an existing table structure manually through a DTS package.  if i try to schedule it as a job within the SQL Server Agent, it fails.  Suggestions? ---ssam

following is the actual error message:

DTSRun:  Loading...   DTSRun:  Executing...   DTSRun OnStart:  Copy Data from Results to [SytelineImport].[dbo].[custaddr] Step   DTSRun OnError:  Copy Data from Results to [SytelineImport].[dbo].[custaddr] Step, Error = -2147467259 (80004005)      Error string:  [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS      Error source:  Microsoft OLE DB Provider for ODBC Drivers      Help file:        Help context:  0      Error Detail Records:      Error:  -2147467259 (80004005); Provider Error:  0 (0)      Error string:  [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS      Error source:  Microsoft OLE DB Provider for ODBC Drivers      Help file:        Help context:  0      DTSRun OnFinish:  Copy Data from Results to [SytelineImport].[dbo].[custaddr] Step   DTSRun:  Package execution complete.  Process Exit Code 1.  The step failed.
LVL 1
ssamAsked:
Who is Participating?
 
debi_melaCommented:
When you schedule the package as job, it will use 'sqlservice' account and will run under that user. Please make sure this account has all rights to the folder in which you are reading / importing the file from/to.

In general when you run the dts package manually it will use your login.

0
 
arbertCommented:
Actually, it will use the SQLAgent service account--not the SQLService account....
0
 
ssamAuthor Commented:
we've tried a number of different setups to get at this and they all fail.  we have tried both named users and system admins and they all give the same error.  is there a specific name to the default SQLAgent user that we could set up as a network admin? more suggestions?  thanks in advance.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
debi_melaCommented:
Right click on SQL ServerAgent, go to properties..

find out what is checked under,  the startup service account..

It should be either system account or user account.. (user account could be sqlservice).. Please verify.

0
 
ssamAuthor Commented:
done this many times - gone back and forth using either system or named account and stopping/starting service each time.  entered a variety of accounts that have domain admin priviledges.  no luck.  also no account called "sqlservice" anywhere.
0
 
arbertCommented:
Does the progress driver use the userid/password of the user that's logged in?

Idealy, you SQL Server is setup to have the SQLAgent service and MSSQLServer service run using a domain user account.  You make sure that this domain user has rights to all files, databases, and resources that are use by your DTS package...
0
 
arbertCommented:
Like I said above, debi_mela was incorrect when the "sqlservice" statement was made--it's SQLAgent
0
 
ssamAuthor Commented:
understood about the disctinction between 'SQLAgent' vs. 'sqlservice'.

no, the progress driver is supposed to use a generic password via the odbc.  we have tried a variety of combinations ranging from no permisisons to all permissions both athte MSSQLServer service and SQL Agent levels.  
0
 
arbertCommented:
My suggest, to get a better message, logon with the Account you have SQLAgent starting up with and run your dts package from designer....
0
 
ssamAuthor Commented:
persmissions was the correct direction, but the solution was actually an update/upgrade to the Progress client installed on the box where the SQL server resided.  Progress persmissions, not Windows permissions were at play here.  In addition, manual configuration of the Progress database wouldn't work without the udpate.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.