SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Can't import data using DTS with C# from remote
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MTech8
Starting Member

10 Posts

Posted - 05/14/2007 :  14:56:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

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

MTech8
Starting Member

10 Posts

Posted - 05/15/2007 :  13:24:13  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000