SSIS file archiving

[mvp]

Hello Everybody,
I do have question abt archive and deleting file from remote server using
SSIS.
I have one database server (payrollproduction) where I pull file from
another server (HRproduction). After pulling xml files to payrollproduction
from HRproduction, I want to archive those files into HRproduction(source)
in archive folder and then delete from original folder. I will run that SSIS
from payrollproduction server.
How can I do it ? Please let me know

[Todd C]

Set up a For Each loop that processes the XML file(s). Inside the loop have a
File System task that moves the processed file from one location to another.
Set the precedence constraint to only move the file if the data flow is
successful.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by mvp
Hello Everybody,
I do have question abt archive and deleting file from remote server using
SSIS.
I have one database server (payrollproduction) where I pull file from
another server (HRproduction). After pulling xml files to payrollproduction
from HRproduction, I want to archive those files into HRproduction(source)
in archive folder and then delete from original folder. I will run that SSIS
from payrollproduction server.
How can I do it ? Please let me know

[mvp]

Yes, But I want to move file in remote server (not in the server where the
job will run). Is is possible doing by File System Task.

Thanks

Post by Todd C
Set up a For Each loop that processes the XML file(s). Inside the loop have a
File System task that moves the processed file from one location to another.
Set the precedence constraint to only move the file if the data flow is
successful.
HTH
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by mvp
Hello Everybody,
I do have question abt archive and deleting file from remote server using
SSIS.
I have one database server (payrollproduction) where I pull file from
another server (HRproduction). After pulling xml files to payrollproduction
from HRproduction, I want to archive those files into HRproduction(source)
in archive folder and then delete from original folder. I will run that SSIS
from payrollproduction server.
How can I do it ? Please let me know

[Todd C]

Yes, you can use the File System task to move files on a remote server. I do
it all the time because our XML files are located on a File Server, and the
package is running on a SQL server, two different machines. You need to make
sure of a couple of things:

a) refer to the remote server with the full UNC path, not a relative drive
letter path. "\\<servername>\<sharename>" not "<drive letter>:\<foldername>"
b) the service account that runs SSIS and/or SQL agent need to have proper
permissions to perform the file operation.
and c) if you are going to build the source or destination path by using an
expression, be sure to double up the back slashes in literal stings so they
are properly escaped:
"\\\\MyServerName\\FolderName\\" + [User::FileName]

Good luck. Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by mvp
Yes, But I want to move file in remote server (not in the server where the
job will run). Is is possible doing by File System Task.
Thanks

Post by Todd C
Set up a For Each loop that processes the XML file(s). Inside the loop have a
File System task that moves the processed file from one location to another.
Set the precedence constraint to only move the file if the data flow is
successful.
HTH
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by mvp
Hello Everybody,
I do have question abt archive and deleting file from remote server using
SSIS.
I have one database server (payrollproduction) where I pull file from
another server (HRproduction). After pulling xml files to payrollproduction
from HRproduction, I want to archive those files into HRproduction(source)
in archive folder and then delete from original folder. I will run that SSIS
from payrollproduction server.
How can I do it ? Please let me know