Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Can't import data using DTS with C# from remote

Author  Topic 

MTech8
Starting Member

10 Posts

Posted - 2007-05-14 : 14:56:06
Please help:

I created a DTS that imports data from a DBase file and puts it in a table on my SQL2000 Server. (It is able to access the DBase file via a local DNS that I created in my AdministrativeTools->DataSource).

I wrote the following C# code that is able to call and execute the DTS.

object pVarPersistStgOfHost = null;
DTS.Package pkg = new DTS.Package();
pkg.LoadFromSQLServer("VServer","VUser","VP888",DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, null,null,null,"DTSUpdateDbaseFilet",ref pVarPersistStgOfHost);
pkg.Execute();
pkg.Steps.ToString();

This code works for all my DTS that transform data between tables within my database. This even works on the computer where the SqlServer/Applicaiton/DBAse files reside.

My problem is when I try to execute this package via an instance of the application that is on a remote computer. The data doesn't get imported. (Note that the connection string is fine and that other DTS that transform data within other tables in the database works fine when executing from the remote computer/application. The remote application that calls the DTS for DBase imports seems to execute, but no data is imported).

I'm guessing it's a permissions issue with the connection with the DBase files. But I'm really stumped here.

Thanks for the help.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-14 : 23:09:43
Enable package logging, you may find details there. Possible permission issue?
Go to Top of Page

MTech8
Starting Member

10 Posts

Posted - 2007-05-15 : 13:24:13
Thank you for input rmiao. Not sure how to do package logging (I'm not that good with Databases yet, more of a .NET programmer).

Found a solution from a post I placed on www.sswug.org


** my post from www.sswug.org **

S Maloney: Thank you for pointing me in the right direction!

I took your advice and tried to execute the package from the remote computer in Enterprise Manager. (It didn't occur to me to try that step, since I thought the DNS package was getting executed on the SqlServer on the primiary computer).

I received an error message that stated the dbase file couldn't be found/accessed. (Don't remember the exact message).

I have a good understanding of networking & security. So with my limited knowledge, this is what I did to make it work:

On the primary computer, I mapped a network drive to the folder where the dbase files are located. I then created an ODBC connection to that network drive.

On the remote computers, I mapped a network drive (same drive letter) to the folder where the dbase files are locaed. And created a exact replicate of the ODBC connection on the remote computer as that of the primary computer. This allowed the DNS package to be able to access the files to perform the import.

I don't really know what is happening underneath or if this is the best solution. But it works for anyone that is having this same problem.
Go to Top of Page
   

- Advertisement -