0

Similar to this post

I have an SSIS Package with a Script Task that creates an Excel file on disk and populates it with data from a SQL Stored Procedure (using Microsoft.Office.Interop.Excel). This works great when testing and when running the deployed package manually through the SSIS Catalog, but when I schedule the task to run automatically through SQL Server Agent, the Package fails in the Script Task step. I have the Job running as a Proxy account that is the same as the account I'm logged into the server with when testing (and the same as the account that works when manually running the packages).

My understanding is that even though the job is running using a Proxy, any desktop interaction occurs within the Profile context of the SQL Server Agent login. Since that profile isn't actively logged in, the interaction fails. Digging in more, there is a bool System Variable in the package called "InteractiveMode" that is set to "False". I have a feeling that if I could switch that to True, everything would be hunky dorey. Trouble is, that variable is only accessible to my Script Task as "ReadOnly"...

Is there any way to set the System:InteractiveMode Variable in an SSIS package manually or programatically at runtime? Please help! I'm having to run these scheduled jobs manually for now, which is a big pain.

Thanks.

Community
  • 1
  • 1
ItJustWerks
  • 551
  • 6
  • 20
  • No, you won't be able to change that value. It's read only as it's taking its value from what the OS is telling it. The linked question is short on details. How do you have your job defined? Is it run in 32 bit mode? My recollection is fuzzy but I do believe I've had SSIS packages interact with the office libraries while run via Agent. – billinkc Oct 27 '14 at 18:20
  • Job is defined as 32 bit, running under a Proxy local admin account. I don't think it's a permissions issue because I have tried giving "Everyone" full access to the folder being written to. – ItJustWerks Nov 11 '14 at 20:22
  • This related to your issue? http://stackoverflow.com/a/26122982/181965 – billinkc Nov 11 '14 at 21:06

1 Answers1

1

I had this problem a few months ago and it turned out that the execution options needed to be set to use 32 bit runtime. If you're using SQL Server 2008 R2, you can open your job and double click on the step. It's under the Execution Options tab. enter image description here

If you continue to have errors, you may want to consider changing the package so that it uses a file system task to create/rename the excel document and then a Data Flow Task to move the data from your stored procedure to your excel document. Depending on your data, you may need to add a Data Conversion step in between. Here's a good article on the topic: http://www.mssqltips.com/sqlservertip/3046/sql-server-integration-services-data-type-conversion-testing/

Edit: I haven't used SQL Server 2012 yet, but according to MSDN, it looks like the option is under the Configuration tab. Here's their article: http://msdn.microsoft.com/en-us/library/gg471507(v=sql.110).aspx

april4181
  • 596
  • 3
  • 8
  • I am using SQL Server 2012 and I don't see the Execution Options tab at all. Is there another way to set the 32-bit runtime option? – ItJustWerks Oct 27 '14 at 21:13
  • So I found the 32 bit option and that didn't help. I can't use a file system task to create the Excel file/Data Flow task to move the data because the Excel files have to be formatted in a very particular manner. Using Microsoft.Interop.Excel I can set column/cell formatting as one could through VBA. This is still really killing me...any other suggestions? – ItJustWerks Nov 11 '14 at 20:20
  • You could create an excel file as a template, use a file task to make a copy with a new name (rename file), copy your data into there, and then save it off. I have several like that where they needed it formatted in a particular manner, so I started a folder on the server just for Excel templates. – april4181 Nov 12 '14 at 14:10