Create files and folder to network drive using SSIS

Question

I have created SSIS packages which creates new folder using file system task at output location and then creates data files in that folder using data flow task.

I have created one network drive which is mapped to azure blob. So I pass the network drive path like Z:\ to package and the folder and files are created as expected which are also reflected on the azure blob.

Now when I schedule this package through SQL agent job I get error that the cannot find part of the path Z:\folderName from file system task. So I though it is because the sql server agent service was not running through my user id . So I started sql server agent with my credentials but it still gives me same error.

Note: My Id doesnt have direct access to azure blob and the network drive is only accessible by my id.

We are currently using azure blob for dev but we may use separate server to store files due to which I cannot use flexible file system task available in SSIS azure service pack

Answer 1

I was able to resolve this using credential manager. I logged in using a domain user and then added the azure path like \\server, userid and password to windows credentials . Then started the sql server agent service using the same domain user id . Set up the file path as \\server\share similar to what I have provided in windows credentials into the ssis package configurations.After doing this I was able to successfully execute the package through sql agent job.

Answer 2

We had reasons we could not use UNC paths (\server\share\file.txt) and struggled with mapped drives as well. The approach we ended up using was to explicitly map and unmap drives as part of our ETL Process.

Execute Process Task - Mount drive

We used a batch file to mount the drive as it made things easier on us but the command would take the form of something like

net use Z: \\server\share mypassword user@azure

Execute process task - Unmount Drive

Again, execute process task

net use /delete Z:

We ended up with a precursor step that I don't remember how we accomplished it, but we would test whether the drive had been unmounted before we attempted to mount it. I think it was File System Task to see if the drive existed and if so, then we had the Unmount task duplicated.

Answer 3

I was able to solve this issue using script task (C# Code) to move data. I've generated a project parameter for Target ShareFolder and used it as ReadOnlyVariable for script task. The target share folder has been already mapped as a network drive on my local PC as well as on Application Server.