Move/Copy files between SQL servers.
Hi folks,
I would like to move/copy some files between two SQL servers. The files are consist of .css .png .jpg and .gif files. I've tried to do this using the File System Task but I can't for the life of me work out how I point it to the servers file
system. I'm obviously not an SSIS guru, I had a play and can move files around my own PC from location to location, but can work out my server to server issue, I hope someone can help, or at least point me in the right direction.
I'm going from SQL2005 to SQL2008 if that makes any difference, but as I say its not SQL tables, data, or what not, it's simply moving files that happen to be stored on the two servers.
Thanks in advance
Roger
October 26th, 2010 12:31pm
You are not moving files between SQL servers as you mentioned. You are just moving files between 2 servers.
What is the error that you are getting?? Check if you are able to move the files between the 2 servers without SSIS. Do you have access to the folder you want to move the files to??
Tweet me..
Free Windows Admin Tool Kit Click here and download it now
October 26th, 2010 12:41pm
Best thing to do is set up some shares that you (and SSIS) can get to with appropriate permissions for read/write, etc.
Then set up some File Connection Managers based on these shares.
FYI: It's better to use the \\<Server Name>\<Share Name> convention than to use mapped drives.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
October 26th, 2010 3:23pm
Cheers guys,
I'll get some shares setup and go from there. I have to admit I assumed I'd simply be able to use the File System task to point to the relevant locations on each of the servers, but as you've pointed out without the necessary shares and permissions
this won't work.
As you say Sudeep, I'm simply trying to move files between two servers, it just so happens that each of these servers has an instance of SQL on them, but this I believe is irrelevant, my task is simply to move files from one server to the other and I'm wanting
to include this in an SSIS package which also moves some data between the previously mentioned SQL servers, then have a job to call the SSIS package on an daily basis. Hopefully I'm not asking too much of SSIS...
Thanks again
Roger
Free Windows Admin Tool Kit Click here and download it now
October 27th, 2010 3:47am
Hi Roger,
You are right. In order to move files from one server to anther one, we can use the File System Task. In order to move data from one SQL Server to another SQL Server, please use Data Flow Task(DFT). In the DFT, we need to configure the Source, the Destination,
and other transformations(if need).
I would suggest you having a loot at:
File System Task:
http://msdn.microsoft.com/en-us/library/ms140185.aspx
Data Flow Task: http://msdn.microsoft.com/en-us/library/ms141122.aspx
If you have any more questions, please feel free to ask.
Thanks,
Jin ChenJin Chen - MSFT
October 28th, 2010 1:31am