Best Practices sFTPing files between servers

Hi-

I have a sql 2005 database that stores weekly time and attendance data for 500 users. I need to send the file on a scheduled basis.

I'd alo like to have programmatic control over sending the file via my asp,net application's admin pages. (I have roles and security set up on the site)

My question is two fold:

Can asp.net be scripted to manage a data extraction to a file, (I gotta HOPE so) THEN is there any sFTP functions built into asp.net?

I know I can do this in SQL2005, then have a scheduled event fire off the file via sFTP. but like I said, I'd like to have programmatic control over the file sending. (Of course they have to be sent on SUNDAY!)

Thanks in advance for any advise

You can have a button that when its cliked, you check on the day. If it is Sunday (for example) then call some stored procedures which basically will run a DTS/SSIS package which you can confugure it to take the needed data from the database and dump it in Excel file. Then you send this Excel file (e.g. Weekly Report) to the management though email (as attached file).

How to run the DTS/SSIS package from ASP.NET, please have a look on these links:

http://www.sqlteam.com/article/how-to-asynchronously-execute-a-dts-package-from-asp-or-aspnet

http://www.sqldev.net/dts/ExecutePackage.htm#Visual%20Csharp

Good luck.

|||

4Ever-

First, thank you for helping out so much in these forums.

Second, great idea, but I have a specific file format constraints as well as an sFTP directory my flat file needs to show up in.

So let me repackage my question.

Is there a way to (stored procedures?) script sql server 2005 to export and import data to and from a sFTP directory on another server? I gotta think I am not the only one that ever had this requirement!

Thanks again for your help.

Dan

|||

Harperator:

4Ever-

First, thank you for helping out so much in these forums.

Thanks Dan.

I think this is a good time to use BizTalk, but there might be a less expensive solutoions..

What about createing a C# windows services to do this or if you have a complex logic use VB script (not sure about it)?

So, to make things clear .. You have a sFTP which reside in diffrent machine that your SQL Server , right?

If this is right, so what is preventing from using the suggest solution above (my previous post)? If it is the file strcture .. can you have it in diffrent format (e.g. CSV) then organzing it in your special strcture?

|||

Hum...

here's the deal:

I have asp.net program running on iis on a win server running sql 2005.

I need to SFTP a file weekly, to and from a remote server.

I need to automate the process.

I can do this by creating a ssis job in sql, have it output the file, then write a batch file to send it via sftp using securefx by Vandike software.

I am just wondering if there is not a simple microsoft solution already built into sql, or iis, of asp.net or windows server.

|||

I do not know if this will help but let us see ..

In your "Transformation" you have a source which is SQL and the destination (e.g. Excel file .. select that path which indicate to your file which is already in another server).

Do your transformation -> Data will be in Excel.

Let your client get the file (e.g. Copy & Pase).

Have a batch which will basically copy data from an empty Excel file to your REAL file -> to have a new Excel file ready to receive the data from SQL Server.

I do not know if this will help .. I hope it is.

Good luck.

|||

Thanks!

Dan

(I broke down and bought sql 2005 unleashed)