Accessing a remote file share in SQL Server Integration Services

Imagine the following scenario or something close to it:

  • You have an IPSEC VPN to another network from yours.
  • There are some CSV (or other) files on a file share that you need to access.
  • The computer owning the file share is not a member of your Windows domain.
  • For some reason (just pick one) the idea of installing a FTP service or other thing on the remote computer is not an option.

This presents a problem because, unless the person who owns the remote computer is willing to seriously compromise security (and you have the guts to ask him to do so), you aren’t getting to those files using SSIS – at least not in a completely automated fashion.  Or are you?

Using SQL Server Integration Services, you can access a remote file share with a username and password provided by the remote computer owner while SSIS is running as a scheduled task using the SQL Agent – completely automated.  Check out the following screen shot.

image

The rest of the SSIS package is cut off as it is irrelevant to our discussion.  We are interested in, specifically, the Execute Process Task.  Let’s double-click on that task to see what’s up.

image

In the execute process task we are doing something I am certain most of you have done before – use the command interpreter – cmd.exe – to map a network drive using the NET USE command.  For those that need a refresher on the NET USE command, visit this TechNet web page.

Let’s go through the fields to see what we have.

  • RequireFullFileName – False

Indicates whether the task fails if the executable is not found in the specified path. We aren’t worried about this since we know CMD.EXE is in %windir%\system32. 

  • Executable – c:\windows\system32\cmd.exe

The fully qualified path to the executable you want to run.  I assume you have installed Windows to the default directory. Use %windir% if you are not sure or have Windows installed some place else.  So that would be %windir%\system32\cmd.exe.

  • Arguments – /C net use y: machineIP\sharename /USER:username password

Specifies the arguments for the executable. In this case, the NET USE command and its arguments.  Also, notice that we are throwing the “/C’” switch to cmd.exe. That means to carry out the command specified by string and then terminate.  We don’t want a command prompt lingering around after we are done.  Basically, do your job and leave is what we are saying.

  • WorkingDirectory – c:\windows\system32

Specifies the working directory of the executable. Set to the path that cmd.exe is found in. Use %windir% if you are not sure or have installed Windows elsewhere.

  • FailTaskIfReturnCodeIsNotSuccessValue – True

If we get a failure code for the return value of the command, then fail the task.  You, in most cases I’m sure, want this to be true.

  • SuccessValue – 0

Obviously, we must spell out the success value to look out for.  CMD.EXE usually returns a value of 0 if a command is successful.

  • TimeOut – 0

It should take no time at all to map a drive.  The default value is fine.

  • WindowsStyle – Hidden

Remember, we are running this fully automated.  You probably will not even be logged on the computer when this SSIS package runs.  Therefore, you do not want a Window.

Once this task runs in SSIS, you should then be able to access any files in the remote file share.  Do keep in mind that what you can do with those files is up to the remote administrator.  If you cannot write to the files and need to, you may need to ask the remote administrator for write privileges.  Also, accessing this drive will work as long as the SSIS package is executing.  Once execution of the SSIS package is complete, the file share access goes away as it is not persistent. 

I’d be interested to know if this helps anyone.

James

3 thoughts on “Accessing a remote file share in SQL Server Integration Services

  1. Hi James,
    I’m using SSIS 2008. I want to open flat files (existing on a remote server), place the data in SQL Server tables and than move the files,
    The user that is running the package from Microsoft visual studio differs from the user that connects to the remote server.

    First I made a package to realize this functionality. Path’s that I use are all placed in variables and are all using the remote server’s IP address (not a drive letter).
    I (manually) connected to the mapped remote server with the different user (password is blank). Then I ran the package as my own user, while this mapping existed to the remote server and everything worked out fine.

    Next step is to log on to the remote server in a SSIS task. So I tried the solution you explained above. ( I haven’t much experience with the net use command.) I disconnected from the mapped network drive. I tried to run the package (sill using Microsoft Visual Studio)
    The first task is a Execute process task aimed to connect to the remote server. It colors yellow and that’s it. The rest of the package is not executed. Since the password is blank, I’m not using a password in the Arguments section. Since I’m not using a drive letter, t’m not using a drive letter in the arguments section either.
    This is my arguments string:
    /C net use \\ipaddress\sharedfoldername /user:username

    Do you have a suggestion why the task doesn’t finish?
    In advance Tanks for your repy,
    Kind regards,
    Elma Molthoff

  2. Hi James,
    With help of a collegue of mine I found the cause; the empty password. Using two double quotes solved the issue.
    So the argumentsection would be ( in the meantime I added also the domainname):
    /C net use \\ipaddress\sharedfoldername “” /user:domainname\username

    Thanks,
    Elma

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