sql server dts: I have created a DTS package and scheduled to run. When I execute the DTS package manually it works great.
It deletes all the rows in a SQL server Table and then imports query specific data from a Visual Foxprot Free table and appends to the SQL server Database Table.

The same task does not import the data form the foxpro table when the DTS package is scheduled to run at any time frame.

Any idea why this is happening and how to fix it?

Note: The foxpro Table is in a network share.
I created a DSN with unc path
I also tried to use Fiel DSN and System DSN. With login credentials and without login credentials.
Connection 1 to foxpro table looks like this: Other(ODBC Data Source)
C:\Program Files\Common Files\ODBC\Data Sources\FoxproUNC.dsn
Connection2 look like: Microsoft OLEDB Providerfor SQL server

I do net get any error message. I have gone through the kbid 269074 earlier and tried the recommendations

I used the UNC path and also tried with or without login id and password for each connection
I also tried running the job on the sql server itself.
But none seems to wor

Previous attempts

I scheduled a job that drop the existing table in SQL and then recreate it in SQL and then appends the selected data from foxpro table
What happens is the new table is created. But the records are not inserted into the table.

any other thoughts? Whether I am missing something. Where should I look for the failure log?

And what are the errors you recieve. Permissions problems will be indicated
by a 80040005 message. The following article usually explains why packages
work manually but not when scheduled.

http://support.microsoft.com/?kbid=269074

--

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


[quoted text, click to view]
specific data from a Visual Foxprot Free table and appends to the SQL server
Database Table.
[quoted text, click to view]

You can enable logging at the package level (right click whitespace |
properties)

If nothing works then we have a fundamental problem that is not related to
JOb or Manual execution I think.

Do you have any cdx or idx (i think they are the extensions in foxpro)
associated with the Foxpro table. When you created the package it must have
seen the data ?

--

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


[quoted text, click to view]

I checked, there is no .cdx fil
Do you think this is the casue of the problem
Again, when I run manually it runs fine though there is no .cdx fil
Do you want to me create a .cdx file and then try scheduling the job