SSIS Error Cannot find part of the file path

1

Hi experts,
I have a Script task (VB script) that counts the number of files in a folder. Pretty simple, right?

It works fine when I run in Debug mode on my laptop.

I deployed the package to prod and ran the job and it fails in the script task.

image

The W drive has been mapped on the prod server.

This is the code for the VB script:
Dim FileCount As Int32

    Dim foldername As String = Dts.Variables("User::vFolderName").Value
    Dim dirs As String() = System.IO.Directory.GetFiles(foldername, "*.txt")
    FileCount = dirs.Length
    Dts.Variables("User::FileCount").Value = FileCount

Any ideas?
Thanks.

2

I don't use drive letters, it's better to use a relative path like \server01\files\EDIWMBR214. The difference between dev and prod is when you deploy it on prod, the user who runs the package is different then locally. You should map the W drive for this user if you want to use

1 Like
3

Thank you, @RogierPronk. I have had success in other environments using
a drive letter but this time it was failing even after I granted the sql server agent service account full permissions. So I changed to UNC and it now works beautifully. Thanks for the help.
Happy Friday.

4

I know this has been stated before - but it is worth stating it again.

Mapped drives are not visible to the service accounts running SQL Server or SQL Server Agent. If you map a drive under you account when logged into the server - that mapped drive is only available to your login.

If another person logs into the server - and then maps a W:\ drive to a different location, that has zero effect on your mapped drive.

This is the same regardless of server/workstation. If you create a set of mapped drives on your workstation - and then I login to your workstation with my domain credentials, those mapped drives that you created would not exist for me.

2 Likes