SSIS: Accessing a network drive using a different username and password

Question

Is there a way to connect to a network drive that requires a different username/password than the username/password of the user running the package?

I need to copy files from a remote server. Right now I map the network drive in Windows Explorer then do I filesystem task. However, eventually this package will be ran automatically, from a different machine, and will need to map the network drive on its own. Is this possible?

Answer 1

You can use the Execute Process task with the "net use" command to create the mapped drive. Here's how the properties of the task should be set:

Executable: net

Arguments: use \Server\SomeShare YourPassword /user:Domain\YourUser

Any File System tasks following the Execute Process will be able to access the files.

Alternative Method

This Sql Server Select Article covers the steps in details but the basics are:

1) Create a "Execute Process Task" to map the network drive (this maps to the z:)

Executable: cmd.exe
Arguments: /c "NET USE Z: "\\servername\shareddrivename" /user:mydomain\myusername mypassword"

2) Then run a "File System Task" to perform the copy. Remember that the destination "Flat File Connection" must have "DelayValidation" set to True as z:\suchandsuch.csv won't exist at design time.

3) Finally, unmap the drive when you're done with another "Execute Process Task"

Executable: cmd.exe
Arguments: /c "NET USE Z: /delete"

Answer 2

Why not use an FTP task to GET the files over to the local machine? Run SSIS on the local machine. When transferring using FTP in binary, its real fast. Just remember that the ROW delimter for SSIS should be LF, not CRLF, as binary FTp does not convert LF (unix) to CRLF (windows)

Answer 3

You have to map the network drive, here's an example that I'm using now:

    profile = "false"
    landingPadDir = Dts.Variables("strLandingPadDir").Value.ToString
    resultsDir = Dts.Variables("strResultsDir").Value.ToString
    user = Dts.Variables("strUserName").Value.ToString
    pass = Dts.Variables("strPassword").Value.ToString
    driveLetter = Dts.Variables("strDriveLetter").Value.ToString

    objNetwork = CreateObject("WScript.Network")
    CheckDrive = objNetwork.EnumNetworkDrives()

    If CheckDrive.Count > 0 Then
        For intcount = 0 To CheckDrive.Count - 1 Step 2 'if drive is already mapped, then disconnect it
            If CheckDrive.Item(intcount) = driveLetter Then
                objNetwork.RemoveNetworkDrive(driveLetter)
            End If
        Next
    End If

    objNetwork.MapNetworkDrive(driveLetter, landingPadDir, profile, user, pass)

From There just use that driveLetter and access the file via the mapped drive.

I'm having one issue (which led me here) with a new script that accesses two share drives and performs some copy/move operations between the drives and I get an error from SSIS that says:

This network connection has files open or requests pending.
   at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
   at ScriptTask_3c0c366598174ec2b6a217c43470f581.ScriptMain.Main()

This is only on the "2nd run" of the process and if I run it a 3rd time it all works fine so I'm guessing the connection isn't being properly closed or it is not waiting for the copy/move to complete before moving forward or some such, but I'm unable to find a "close" or "flush" command that prevents this error. If you have any solution, please let me know, but the above code should work for getting the drive mapped using your alternate credentials and allow you to access that share.

Zach