i have developed an web-database enabled application, wherein the admin will be importing the data from a remote machine to the server.
Is it possible to run the DTS remotely?
Also, is there need to install the sqlserver in the remote machine on which the admin is working ? i mean to say, other than server, do i need to install sqlserver on the remote machine too..
Thanx in advance
If you are on the same Network you register the server and it becomes local to you and if needed you remote into the server and do anything it feels local. To use DTS remotely you need SQL Server Agent installed with a service account. Try the links below for configurations. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
|||Hello Kiran,It looks as if solution of problem is not clear to you also or you are moving in wrong direction .
wherein the admin will be importing the data from a remote machine to the server.
With the help of DTS it is not possible.
Possible solution can be uploading the data to server through webapplication and then calling DTS through stored procedures in webapplication.
Other solution can be uploading files using DTS FTP task and then performing transfromation operations.
|||Hi,
thanx for the reply. i will try it out|||Hi Bhatia,
hmm, i think, the way i have put my question sounds confusion.
Lemme clearly explain u what the prob is .
I have developed an webenabled application using ASP.NET,C#,SQL server 2000. The application is all about having students database-all the universities.
Here, the admin will have the privilege to upload students data onto the database and all other users can only retrieve it.
I want to design the application such a way that, admin can upload the data onto the server remotely, mean to say, he can do that, signing in from any remote machine.
While uploading, admin should access the datatables of the server, import the data from the current machine(remote machin thru which hes signed in) onto the server.
How can i design this...hmm, how can i call the DTS of the server, signing in from the remote machine.
Hope this explains better..
|||Hello Kiran,
You must first upload the student data to the server through web application.
For uploading tutorial visit this link
http://www.aspheute.com/english/20000802.asp?PrinterFriendly=True
After the file is uploaded to server then only you can transform file to the server.
you have to create DTS package for transformation and stored procedure for calling DTS package.
1.Create a new package
Right click on package window > Package properties
-Set Global Variables
SourceText String
DestTable String
-Now Drag and drop Text file (Source) and Microsoft OLE DB Provide for SQL Server(Destination).
Connect them with Transform data task.(Add dummy data in text file and SQL Server Table to check it is working fine).
-Now add Dynamic Properties Task from Task.
Edit it.
Now set property
1.Connections > Text File(Source) > Data Source
Set Global Variables (SourceText variable)
2.Tasks > DTS Tasks > DestinationObjectName
Set Global Variables (DestTable variable)
Your workflow will set in this way
Dynamic Properties Task --->On Success--> TextFile(Source)-->Data Transfrom Task-->Microsoft OLE DB Providefor SQL Server(Destination).
Step2. Create stored procedure that will call this DTS package from web application.
CREATE Procedure DtsTransform
@.ServerName nvarchar(30),
@.UserName nvarchar(30),
@.Password nvarchar(30),
@.DtsName nvarchar(250),
@.SourceFile nvarchar(200),
@.DestTable nvarchar(200)
AS
DECLARE @.ERROR int -- For Hold Error Number
DECLARE @.CMD varchar(1000) -- Dts Run Command
DECLARE @.DtsPassword varchar(30)
BEGIN
SET @.ERROR = 0
SET @.CMD = 'dtsrun /S '+@.ServerName+' /U '+@.UserName+' /P'+@.Password+' /N '+@.DtsName +' /A SourceText:8='+ @.SourceFile +'/A DestTable:8='+ @.DestTable
EXECUTE @.ERROR = master..xp_cmdshell @.CMD
SELECT @.ERROR = COALESCE( NULLIF ( @.ERROR, 0 ), @.@.ERROR )
END
RETURN @.ERROR
GO
Hope this will solve your problem.
|||That is not correct a DTS package running with SQL Server Agent populates online back with deposits collected from AS400. The job runs for about three hours. I ran Profiler on it and watched SQL Server Agent deposit 50 transactions every three to five seconds. Search this forum I helped some one less than two months ago. If you cannot do it does not mean it is not possible. In the 1990s I started posting the need to install SQL Server Agent with service account for Replication, today click on the Replication Wizard and you will get a message telling you to do so. Technology is there for you to explore and improve. Hope this helps.|||Hi Bhatia,
well i am very new to DTS, and i dint get that DTS package transformation. I do know how to upload the file though.
can u plz explain me whats the DTS package for and how to create that?
thanks in advance
|||Hi,
i am totally confused with this. What r u trying to explain?
Thanks inadvance|||Hello Kiran,
You cannot develop all this in one flow.
Start with uploading .
See if you are able to upload student data or not .
After this shift to DTS part.( for transformation of text file to Sql server database).
