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)

Best Approach at Refreshing large table

I have a large table that needs to be refreshed on a regular basis. This
data is read-only, it will not be modified. I import the refreshed data in
its entirety (all data exists in a fresh table).
I can think of 3 ways to do the refresh but I'm not sure how to evaluate
which option is best:
1. Import the refreshed table
Drop the original table
Rename the refreshed table to original table
2. Import the refreshed table
Truncate the old table
Insert all the records from the refresh table into the original table
3. Import the refreshed table
Delete all records in the original table that do not exist in the
refresh table
Insert all records in the refresh table that do not exist in the
original table
Update all records in the original that are not the same as those in the
refresh table
Method 1 might lead to "invalid object" errors if users attempt to access
the table during the refresh so I do not think this is a good choice.
I think method 2 and 3 need to be evaluated based on the locks they use and
the time they take to execute.
What would happen if a user attempts to access the table during the TRUNCATE
and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
After the INSERT completes?
What about option 3? What kind of access will a user have during these 3
data modification actions INSERT, DELETE and UPDATE?
As I mentioned, the data is read-only and I need to try to maintain maximum
accessibility for the users.
Any comments are appreciated.
DaveHi,
If you need to provide maximum accssibility to user then go for step3.
Ensure that when ever you do a DML, do it row level.
So only that record will be locked and users will be able to view data with
out any problems.
Thanks
Hari
MCDBA
"DaveF" <davef@.comcast.net> wrote in message
news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data

---

in

quote:

---

> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in

---

the

quote:

---

> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use

---

and

quote:

---

> the time they take to execute.
> What would happen if a user attempts to access the table during the

---

TRUNCATE

quote:

---

> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain

---

maximum

quote:

---

> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>

---

|||Dave,
If availability is the concern, and the data is truly read-only, then here's
what I would do...
Let's make believe you're refreshing "ORDERS"
A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
B1.) Set transaction isolation level serializable.
B2.) Begin transaction.
B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
B5.) Commit transaction
C1.) Drop "old" table.
This way, the data is only 'unavailable' for milliseconds, and the side
benefit is that, because of the locks acquired, your users will not receive
the 'invalid object name' errors.
I do this in production environments all the time.
Of course, there's a little more to it if you've got DRI and what-not, but
I'm sure you get the idea.
James Hokes
"DaveF" <davef@.comcast.net> wrote in message
news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data

---

in

quote:

---

> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in

---

the

quote:

---

> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use

---

and

quote:

---

> the time they take to execute.
> What would happen if a user attempts to access the table during the

---

TRUNCATE

quote:

---

> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain

---

maximum

quote:

---

> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>

---

|||Hari,
His data is static.
Step 3 would blow chunks in any sort of VLDB situation.
Not to mention that your suggestion of row-level DML goes against the grain
of set-based RDBMS theory.
This is nothing more than a simple table-swap op.
James Hokes|||Excellent!
Thanks very much.
"James Hokes" <noemail@.noway.com> wrote in message
news:e4$vP846DHA.452@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Dave,
> If availability is the concern, and the data is truly read-only, then

---

here's

quote:

---

> what I would do...
> Let's make believe you're refreshing "ORDERS"
> A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
> B1.) Set transaction isolation level serializable.
> B2.) Begin transaction.
> B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
> B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
> B5.) Commit transaction
> C1.) Drop "old" table.
> This way, the data is only 'unavailable' for milliseconds, and the side
> benefit is that, because of the locks acquired, your users will not

---

receive

quote:

---

> the 'invalid object name' errors.
> I do this in production environments all the time.
> Of course, there's a little more to it if you've got DRI and what-not, but
> I'm sure you get the idea.
> James Hokes
> "DaveF" <davef@.comcast.net> wrote in message
> news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
This[QUOTE]
data[QUOTE]
> in
table[QUOTE]
> the
access[QUOTE]
> and
> TRUNCATE
TRUNCATE?[QUOTE]
3[QUOTE]
> maximum
>

---

Best Approach at Refreshing large table

I have a large table that needs to be refreshed on a regular basis. This
data is read-only, it will not be modified. I import the refreshed data in
its entirety (all data exists in a fresh table).
I can think of 3 ways to do the refresh but I'm not sure how to evaluate
which option is best:
1. Import the refreshed table
Drop the original table
Rename the refreshed table to original table
2. Import the refreshed table
Truncate the old table
Insert all the records from the refresh table into the original table
3. Import the refreshed table
Delete all records in the original table that do not exist in the
refresh table
Insert all records in the refresh table that do not exist in the
original table
Update all records in the original that are not the same as those in the
refresh table
Method 1 might lead to "invalid object" errors if users attempt to access
the table during the refresh so I do not think this is a good choice.
I think method 2 and 3 need to be evaluated based on the locks they use and
the time they take to execute.
What would happen if a user attempts to access the table during the TRUNCATE
and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
After the INSERT completes?
What about option 3? What kind of access will a user have during these 3
data modification actions INSERT, DELETE and UPDATE?
As I mentioned, the data is read-only and I need to try to maintain maximum
accessibility for the users.
Any comments are appreciated.
DaveHi,
If you need to provide maximum accssibility to user then go for step3.
Ensure that when ever you do a DML, do it row level.
So only that record will be locked and users will be able to view data with
out any problems.
Thanks
Hari
MCDBA
"DaveF" <davef@.comcast.net> wrote in message
news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data
in
> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in
the
> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use
and
> the time they take to execute.
> What would happen if a user attempts to access the table during the
TRUNCATE
> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain
maximum
> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>|||Dave,
If availability is the concern, and the data is truly read-only, then here's
what I would do...
Let's make believe you're refreshing "ORDERS"
A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
B1.) Set transaction isolation level serializable.
B2.) Begin transaction.
B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
B5.) Commit transaction
C1.) Drop "old" table.
This way, the data is only 'unavailable' for milliseconds, and the side
benefit is that, because of the locks acquired, your users will not receive
the 'invalid object name' errors.
I do this in production environments all the time.
Of course, there's a little more to it if you've got DRI and what-not, but
I'm sure you get the idea.
James Hokes
"DaveF" <davef@.comcast.net> wrote in message
news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data
in
> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in
the
> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use
and
> the time they take to execute.
> What would happen if a user attempts to access the table during the
TRUNCATE
> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain
maximum
> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>|||Hari,
His data is static.
Step 3 would blow chunks in any sort of VLDB situation.
Not to mention that your suggestion of row-level DML goes against the grain
of set-based RDBMS theory.
This is nothing more than a simple table-swap op.
James Hokes|||Excellent!
Thanks very much.
"James Hokes" <noemail@.noway.com> wrote in message
news:e4$vP846DHA.452@.TK2MSFTNGP11.phx.gbl...
> Dave,
> If availability is the concern, and the data is truly read-only, then
here's
> what I would do...
> Let's make believe you're refreshing "ORDERS"
> A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
> B1.) Set transaction isolation level serializable.
> B2.) Begin transaction.
> B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
> B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
> B5.) Commit transaction
> C1.) Drop "old" table.
> This way, the data is only 'unavailable' for milliseconds, and the side
> benefit is that, because of the locks acquired, your users will not
receive
> the 'invalid object name' errors.
> I do this in production environments all the time.
> Of course, there's a little more to it if you've got DRI and what-not, but
> I'm sure you get the idea.
> James Hokes
> "DaveF" <davef@.comcast.net> wrote in message
> news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
> > I have a large table that needs to be refreshed on a regular basis.
This
> > data is read-only, it will not be modified. I import the refreshed
data
> in
> > its entirety (all data exists in a fresh table).
> >
> > I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> > which option is best:
> >
> > 1. Import the refreshed table
> > Drop the original table
> > Rename the refreshed table to original table
> >
> > 2. Import the refreshed table
> > Truncate the old table
> > Insert all the records from the refresh table into the original
table
> >
> > 3. Import the refreshed table
> > Delete all records in the original table that do not exist in the
> > refresh table
> > Insert all records in the refresh table that do not exist in the
> > original table
> > Update all records in the original that are not the same as those in
> the
> > refresh table
> >
> >
> > Method 1 might lead to "invalid object" errors if users attempt to
access
> > the table during the refresh so I do not think this is a good choice.
> >
> > I think method 2 and 3 need to be evaluated based on the locks they use
> and
> > the time they take to execute.
> >
> > What would happen if a user attempts to access the table during the
> TRUNCATE
> > and INSERT? Will it be locked from any SELECTs until after the
TRUNCATE?
> > After the INSERT completes?
> >
> > What about option 3? What kind of access will a user have during these
3
> > data modification actions INSERT, DELETE and UPDATE?
> >
> > As I mentioned, the data is read-only and I need to try to maintain
> maximum
> > accessibility for the users.
> >
> > Any comments are appreciated.
> > Dave
> >
> >
> >
> >
> >
>