Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lemondash
Posting Yak Master
159 Posts |
 Posted - 2009-07-08 : 10:50:09
|
| Hello all,Not too sure where to post this, but have a small project that i have been left to deal with and any help would be great.I have a DB that has a main table called tbldocuments and every day over 100 inserts every day, each record is a entry to a file on a storage system. Now every day i have to run a query to find certain records and then create a batch file to copy the files to oversea server.What would be the easy way to do this. I also i have a table to record the files that i'm copying. At the moment i have a sql script that i run in certain stages to complete the task but i would like to automate it. I tried looking doing a ssis package to do the whole thing but with no luck.Lee |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-08 : 11:07:29
|
| you can use ssis pacakge with flat file source and oledb destination. and you may schedule a job in sql server agent to execute this daily |
 |
|
|
lemondash
Posting Yak Master
159 Posts |
Posted - 2009-07-08 : 12:01:40
|
| What i do is a run a query that puts my results in to a temp table then i cam insert them in to a logging table, and then i run a query to produce a batch file e.g.SELECT 'Copy ' + ' ' + Location + ' ' + DestinationFROM ##FinAnnCan i still do that in SSIS |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-08 : 12:06:35
|
| you can. what does batch file do by the way? |
|
|
|
lemondash
Posting Yak Master
159 Posts |
Posted - 2009-07-08 : 12:11:29
|
| when it runs it copies files. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-08 : 12:15:30
|
| do you mean copy files to some folder? then you may use ftp task in ssis for that |
|
|
|
lemondash
Posting Yak Master
159 Posts |
Posted - 2009-07-09 : 05:13:16
|
| Yes, it copy the files from one folder on a server to another folder on another server. |
|
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2009-07-09 : 07:57:59
|
| infact in SSIS file system task has pre built functionalities to copy/delete the files, you wont have to create batch scripts to do that.steps would be an execute sql task which reads tbldocuments and saves the result in object variable. then loop through the variable and copy the files one by one. |
|
|
|
|
|
|
any help would be great