Home > .NET Development, Microsoft SQL Server > SSIS How to copy files from one server to another

SSIS How to copy files from one server to another


The common question is how to copy/move/delete files across shared folder using SSIS.
Quick answer is to use the File System Task for the actual file operations, but how about the permissions?
By default it assumes that the account the you’ll use to execute the package (SSIS) either the SQL Server Agent identity (using sql job) or account that executes DTExec have appropriate permissions on the source/destination server.

To get the identity of SQL Server Agent:

1. In the command prompt, type services.msc, this would open the list of all services on the server.

2.  In the list of services, look for SQL Server Agent, Right Click -> Properties. Go to Logon Tab

Using Proxy. If it’s other identity than the Agent:

1. In the Security -> Credentials -> Create new credentials (username and password)

2. then go to SQL Agent -> Proxies -> SSIS Package Execution -> Create a new Proxy and assign the credentials. Then on the sql job itself assign the Run as = proxy.

Using NET USE command, If you need to connect to multiple resources (shared folder) or doesn’t use SQL Server Agent.

Issue the NET USE command before the File System Task. Example syntax:

net use RPAULO\\SharedFolder /user:RPAULO\SSISUser p@assword

for NET USE command refrence, click here.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: