SSIS Script Task does not see File on network drive

2
votes

My SSIS package uses Script Task which calls File.Exists() to determine if file is present.

I have to servers on same domain, e.g. THISDOMAIN, SERVER1 and SERVER2

I have user THISDOMAIN\ADMIN which is on both servers.

SERVER1 needs to access SERVER2 folder C:\ which is mapped to SERVER1 as D:\

On SERVER1 there is SQL Server 2008R2 which has SQL Server Agent with Account THISDOMAIN\ADMIN

If I log on to SSIS on SERVER1 with windows authentication as THISDOMAIN\ADMIN package executes successfully.

But if I execute the same package with SQL Server Agent, it does not see the network drive D:\ on 'SERVER1`

What I tried:

1
filenetworkingpermissionsssissql-server-2008-r2
Mapped drives get flaky when it comes to service accounts. The mapping via Windows Explorer generally only works when it's not running as a service. Is there a reason you don't use UNC paths, which is recommended for situations such as this? - Taegost
Just to ensure I have it correct, thisdomain\admin is the account for SQL Agent. You have the credentials to log in as this user and when you do so, you see the drive mapped as D. When you execute your package through SQL Agent it does not see the D drive? - billinkc
@billinkc correct, but if I execute manually trough SQL Integrations Server it does see the drive - skmasq
@Taegost I will try this but I'm not sure this is the real issue, because manually executing this package it does find this drive. - skmasq
And no stored credentials are involved with this, correct? - billinkc

1 Answers

3
votes

When going across the network to access files/folders, it's always recommended to use the UNC path.

Especially in a situation like this where the drive may be mapped via Windows Explorer and works when you're logged in, but doesn't work when you're trying to access that same path from a service that isn't logged in.