Wednesday, May 16, 2012

SSIS: Access Network Resources on a Server Not on the Same Domain

I recently ran into a situation where my SSIS server needed access to file shares that were not on the domain but were accessible over the network via UNC path.

Typically I would create a domain-based service account and grant it access to any network resources required by the SSIS process. In this case the SSIS server needed to access files on servers on two other domains and these domains did not have a trust between them and the SSIS domain.

The solution was to dynamically create and delete the mapped drives as needed using Execute Process tasks and the NET USE command. Below is an example of this command.

Not the user parameter. This is where you can pass the credentials from the domain on which the share resides. If, for some reason, you want to use a mapped drive on the same domain (instead of a UNC path) you can exclude the username and password (assuming that the user under which your process is running already has access to the share).

We need to modify this a little bit to use in an Execute Process task. See below.

Note that the Executable property is set to cmd.exe. This is how we tell the task to run this command from a command prompt. Also not the /c switch. This tells the task not to display the command prompt window and to complete execution without any user intervention. If you wanted to debug the task you could replace the /c switch with /k which would display the command prompt window at the time of execution.

After this task runs our process now has the ability to use the newly mapped drive. This is where our Connection Manager comes into play. This could be any Connection Manager that requires network access such as FLATFILE, EXCEL, OLEDB Jet 4.0 for Access, etc. All we have to do is set the Connection Manager's ConnectionString property to a path using our mapped drive.

The key here is to set DelayValidation to True. If we don't do this then the package will fail validation each time because the mapped drive has not been created when SSIS attempts to validate the package. The drive is only mapped after validation is complete and the Execute Process task has run.

You may be thinking, why would validation fail each time? Isn't the mapped drive still there from the last time we ran the package? The answer is no. It's no because of the last step which is to delete the mapped drive once we are done using it. The command for deleting this drive would be the following.

NET USE Y: /delete

All we need here is the drive letter and the delete switch. Below is a screen shot of the Execute Process task.

That's all you need to access file shares with SSIS using dynamically created mapped drives. UNC paths are typically a more reliable method and what I consider to be a best practice when accessing a file system over a network. However, you might find yourself in a situation similar to mine where a mapped drive is necessary to pass credentials, other than the ones being used to run your process, to access a file system resource.

19 comments:

Excellent post, thank you so much! I really hope someone reads this because I am hoping for some assistance. I was able to get this to work, but now for some reason it's not anymore. The command prompt pops up, but it doesn't seem to be passing my argument. When I copy and paste the argument into the command prompt manually, it works like a charm. Any ideas how to fix/troubleshoot this?

Even if it's already mapped you still need to map at runtime unless your process is running under the same user profile. Even then I think there can be some issues. I initially went down the route you're suggesting but found it was not possible.

Good question. Unfortunately I don't have an answer for you. In general, this is not a very secure practice because, as far as I know, the credentials will be sent over the network in plain text anyway. The best I can suggest is to secure the parameter values in the SSISDB catalog by only allowing the appropriate people to access that database.