Export to remote server on a different domain

[ecobb]

I have a DTS package that runs nightly, exporting information from various tables into two CSV files. These files need to be placed on another server, which IS on our Network but IS NOT on our Domain. I have a mapped drive set up from my SQL Server to the remote server, which I connect to using it's local "Administrator" account. Every time I log on to the SQL Server, I have to provide the password to the remote server to reconnect the mapped drive. When I run the DTS package manually, it places the CSV files on the remote server with no problems.

Now, here's my problem. I want to schedule this package to run nightly. If I'm not mistaken, whenever the SQL Server is logged off, the mapped drive to the remote server is not connected, right? So, even though the scheduled job will run without the SQL server being logged on, it won't be able to place the files on the remote server.

One way around this is to leave the SQL Server logged in all of the time, which I would rather not do. I thought about using the actual network path instead of a mapped drive (\\0.0.0.0\folder\file.csv instead of E:\folder\file.csv), but I'm not sure how to send the login to the remote server. Since the two machines are not on the same domain, I can't create a user that has permissions on both machines, can I?

Does anyone have any ideas as to how I can automate the exporting of these files?



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[john76]

Is there a posibility of FTPing the file to the remote server?

Just a thought...

John

 

[ecobb]

Thanks John! I thought about that but everything I read about the DTS FTP says that it's for download only. Is there a way to use the DTS built in FTP for uploads? If there is that would be great!



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[john76]

I haven't actually used the FTP task yet, but here is a link to an SQLDTS.com download for a custom task that offers more features than the SQL 2k FTP task.

http://www.sqldts.com/default.aspx?302

Hope this helps you,

John