I have an SSIS package which accesses a map drive. It works fine when I run SSIS from Visual Studio. However, when I run this from a SQL Server job, it gives an error. What is the reason for this?
You should assign the necessary permission to the map drive for the user account which is used to run the SQL Server Agent. From Visual Studio it uses the logged-in account while when it is running from the agent it uses the SQL Server agent user account.
When it is run from SQL Server Agent, execute the following code before running the SSIS package.
exec xp_cmdshell ‘net use M: \126.96.36.199e$ pwd /user:user’
You will need to run this for each time you run your SSIS package.]]>