I wrote a SSIS script task where I use VB code to read data from a database using ODBC and then dynamically create an excel spreadsheet using the Microsoft.Office.Interop.Excel object. The code then saves the spreadsheet to a directory folder on the sql server. The server OS is 32 bit Windows Server 2008 Standard, and SQL Database is SQL 2005.

When I execute the package in the SQL Server Business Development Studio on the server, it works perfectly and the package writes the spreadsheet to the folder on the server without any problem.

I then save the package on the server using "Server Storage" and I set up a scheduled job to run the package. Every time the SQL Agent runs this job , I get this error

I have created many other packages before using script tasks and I have used the sql agent to execute them, and I never have had a problem before. I should note that none of my previous packages used the Microsoft.Office.Interop.Excel object to create an Excel spreadsheet, so maybe this is some how causing the problem.

I created the job step for executing the SSIS package using SQL Server Authentication since I saved the package to the server using "Server Storage" with the same login account.

I did a test and I commented out the statement in the VB script that actually saves the workbook to the folder on the server and when I executed the job in the SQL Agent with this statement commented out, the job worked fine without an error. When I remove the comment and try to write the spreadsheet to the folder, I get the that same error .

I fixed it by remote desktop'ing into the server that the SQL Server Agent was running on. Instead of logging in with my own user account, I logged in with the same account the agent service is running under. Then, I opened up Excel.

It ran through all the first time user popups, like prompting me to verify my name and initials, if i wanted to activate excel, if i wanted to get automatic updates, etc.