DTS package problem...

[mgould00]

i have a dts package set up to delete data from a group of tables then transfer data back into those tables from a pervasive sql database through an odbc connection. i have the package scheduled to run nightly. for some reason, the delete works, but the new data does not get transferred. if i execute the dts manually, it runs successfully. why is this?

thanks in advance....

 

[tlbroadbent]

Does the SQL Agent startup account have appropriate permissions on the Pervasive DB? When you run the package manually, it runs with your credentials. When run by SQLAgent it runs with the Agent login account credentials. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[mgould00]

well, i doubt it - i didnt think about that. but why would the delete portion run if the credentials were invalid? would i just have to add SQLAgent permissions to my pervasive db?

 

[mgould00]

i have another problem....i went to add the SQLAgentCmdExec (which is what it shows on my sql server) to my pervasive sql database (it is actually timberline) and i am only allowed 8 characters for a username. is there another way?

 

[tlbroadbent]

Doesn't the delete occur on the MS SQL tables while the data is extracted from the Pervasive tables. The SQL Exec should have permissions in the SQL tables but not necessarily in the remote database. The login for the Agent is not SQLAgentCmdExec. Open the SQL Server Agent Properites and see what the startup account is. It should be a domain account. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[mgould00]

thanks for your help....this is starting to make sense. you are correct about the delete function. the startup account is a domain account, in fact it is my administrator account. in order to access my pervasive db, i need to add a user into timberline. i am only allowed 8 characters for username, so my administrator username in timberline is actually 'admin'. SO, can i add a domain account called admin with administrator privledges and change my sql startup to admin as well?

 

[tlbroadbent]

You shouldn't have to change the SQLAgent login account.

What user name and password are used in the ODBC connection properties? Is the DSN defined on the Server? If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[mgould00]

it doesnt say "SQLAgent login" in my properties - it says "startup and run SQL server in the following account"

however.....i can run it manually from my machine because the odbc is setup on my machine. the odbc was not set up on the sql server. when i created my microsoft data link, it was pulling odbc choices from my local machine - not the server. i set up the odbc on the server identical to the one on my machine and i bet that will do it. i will try that and see if runs tonight and post back monday. thanks for your help....

 

[bytehd]

Does your DTS package have any workflow dependencies?
The Pervasive could be failing if it depends on a failed task, or a task you dont set as DTS_SUCCESS etc

 

[bytehd]

Main = DTSTaskExecResult_Success