DTS fails to import data when called from VB6

#1

I have created a DTS package that imports an Access
database file into a SQL 2000 database. I call this DTS
package via VB6 and it imports all the data successfully
on my laptop that contains the MDb file ,VB6 app and the
local SQL database.

After copying the MDB file to a directory on the server
(not my laptop), setup the DTS package on the server then
test the DTS manually it imports the data as well, however
once I call the server and DTS package via VB6 it does not
import the data but it does creates the tables?

What is causing it to work on my laptop but fails when run
with the server connection?

Any Ideas.

Thanks

J

#2

Hi,

I have set all the necassary permission on the server and
have also installed MS Access on the server.

Within the VB code I execute 2 DTS packages

1) deletes(drops) the Access tables. This executes fine
and I can see that it drops the tables.
2) Create (Access tables) in SQL and then imports the
data. This step creates the Access tables in SQL but fails
to import the data. Tables are empty.

Note that if the DTS packages are executed manually on the
server it runs successfully.

Why does it not import the Access data into the SQL
tables? I am slowly running out of options, any help will
be much appreciated.

Thanks

J

Quote:

-----Original Message-----
Hi There.
I have seen this error before with my colleagues. I don't
have the exact
answer, but I think know what the problem is.
Its all about the account that is executing the package.
when you save the
DTS package within SQL Server it is saved as the current
user. In your case
as its your laotop you have Admin rights. What I ahve
seen is that when the
Package is exected, sometimes it is executed under a
different account to the
one in SQL. However because you are using VB n your own
laptop against the
local server both the application and SQL have full admin
rights abd the
package will execute. However when this is on the server
this is probably
different. It is either the DTS on the server or the
rights and username etc
of your VB app calling the DTS package...or both I hope
that steers you a bit
closer
Tim

"Jerome" wrote:

I have created a DTS package that imports an Access
database file into a SQL 2000 database. I call this DTS
package via VB6 and it imports all the data
successfully
on my laptop that contains the MDb file ,VB6 app and
the
local SQL database.

After copying the MDB file to a directory on the server
(not my laptop), setup the DTS package on the server
then
test the DTS manually it imports the data as well,
however
once I call the server and DTS package via VB6 it does
not
import the data but it does creates the tables?

What is causing it to work on my laptop but fails when
run
with the server connection?

Any Ideas.

Thanks

J

.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DTS fails to import data when called from VB6

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

DTS fails to import data when called from VB6 - 08-23-2004 , 08:19 AM

RE: DTS fails to import data when called from VB6 - 08-25-2004 , 06:30 AM