December 10, 2010 at 12:02 pm
Hi all,
I have an SSIS package that runs perfectly from BIDS or from dtexec (command line or gui), but when I run it via SQL Server Agent, the Script task in the package bombs out with the following exception: "Cannot create ActiveX component."
The SQL Agent job step is configured to run under a Proxy/Credential, and those both appear to be set up correctly on the server using a Windows service account.
To rule out permissions issues with the service account, I logged into the server as the user that the Proxy/Credential are mapped to and was able to run the package with no problem using dtexec under that account's credentials.
It seems that this must still be a permissions issue, but I am stuck and am not sure where to look next. Any ideas for me?
TIA,
v / trelundar
December 12, 2010 at 4:04 am
Maybe it has something to do with the problem described in this thread:
If not, then maybe you can try to check if the package really runs under the desired account when scheduled with SQL Server. There is a system variable UserName that you can use for this purpose.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2010 at 10:17 am
Thanks for the reply, da-zero.
I looked at the link you posted but don't think that is the issue here. I should have mentioned this in my original post, but I can successfully run the SSIS package manually on my SQL Server while logged in as the service account, so I know the COM object can be created on that server by that user...it just mysteriously fails when I am running it via SQL Server Agent.
Your suggestion for verifying which user name the job was running the package under was a very good one - something I hadn't done yet in my troubleshooting. I just checked that and see that it is, in fact, running under the expected account, so I suppose that is not the problem here, either.
I do appreciate your suggestions. Can you (or anyone else who may read this) think of anything else that I should look at?
December 13, 2010 at 11:10 am
Maybe this thread can offer a solution:
(I don't know anything about ActiveX, I'm just browsing the net here :-))
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2010 at 11:17 am
What edition of SQL Server are you running it from?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2010 at 11:38 am
da-zero (12/13/2010)
Maybe this thread can offer a solution:(I don't know anything about ActiveX, I'm just browsing the net here :-))
I haven't tried doing the regsvr32 thing yet, but if it were a registry issue wouldn't I get this error no matter how I run it (rather than being able to run it via one method but not another)? I honestly have no idea...like you, I know nothing about ActiveX and am trying to figure out all the different ways to word a Google search in hopes of yielding a new angle to explore. 😀 Anyway, I will look in to this suggestion to see what I could try...
December 13, 2010 at 11:38 am
GSquared (12/13/2010)
What edition of SQL Server are you running it from?
SQL Server 2005 SP2 Standard Edition
December 13, 2010 at 11:39 am
Right now I am looking in to this as a possible culprit, but so far tweaking the Launch/Activation permissions for the SQL Agent group on the server hasn't solved the issue (though I haven't confirmed yet that I'm not just doing it wrong):
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24094485.html
Anyone know anything about that? My intuition tells me that it's something to do with permissions related to SQL Agent - even though the domain account SQL Agent runs under has access to the things it needs to in order to create the object, could something about it being a SQL Agent job override the permissions of the domain account? Wish I could word that more intelligently...I'm obviously speaking from a gut feeling more than an actual knowledge about how anything works. 😉 But does anyone have any thoughts about that possibility? Or is it completely off the mark?
December 17, 2010 at 6:10 pm
Well, I finally figured out a workaround for myself! After learning that I could use the command line to invoke the program I was trying to call (and pass all the same parameters as would have been handled by the COM object), I decided to go that route with my package via a Script task and the Shell() function. At first this did not work via SQL Agent either, but then I found the InteractiveMode property of the package and set it to False...and now it works like a charm when run from the agent! Woo hoo.
I don't know if this would have solved my original problem with the ActiveX error, but the InteractiveMode property was something I had overlooked in my initial package design and thought that information could still be applicable to someone having similar troubles scheduling a package that runs fine manually (only, of course, after eliminating permissions as a suspect... 😉 ).
January 6, 2011 at 4:16 pm
Hey Genius, Where's that Interactive Property of the package.... ? I think I have seen it somewhere .... But I have Not .....been able to find it after your reply...
I am in exactly the same boat as you were... Ditto... the same conclusions & a day spend figuring out the same issues...
January 6, 2011 at 4:24 pm
Mohan.deval (1/6/2011)
Hey Genius, Where's that Interactive Property of the package.... ? I think I have seen it somewhere .... But I have Not .....been able to find it after your reply...
Genius, eh? 😛 You're too nice.
You can set the value of the property by doing the following:
- Open the package in BIDS
- Right-click anywhere in the designer pane and select "Properties"
- On the Properties dialog, click on the Expressions property
- On the line for the Expressions property, click the ellipses ("...") button
- On the Property Expressions Editor dialog, click on a new line
- Select "InteractiveMode" from the dropdown in the Property column
- Type "False" (without quotes) in the Expression column
- Press OK
Did that solve your problem?
January 6, 2011 at 4:51 pm
Nope,...Thanx for your quick reply... Who says NewBie's are Not Genius ?...
I tried doing your soln. InteractiveMode = False.
It Executes independently as a Package & in BIDS, ( It has Excel file creation in Script task, 32 bit , Excel 2010
When Scheduled as a Job, I get the following error in the custom logging I have done in Script task.
( I create XL in a Script task the old style for some reasons. ( When I remove Excel file creation in the script task Viola the package works.. so nothing wrong in job scheduling & step creation....
)
Exception Message = Cannot create ActiveX component.
& the Job history Error Dump is =
1
as user: xxxxDB\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:39:01 PM Error: 2011-01-06 18:39:33.19 Code: 0x00000001 Source: Generate XL FileName Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object. at ST_62cbbd3284894005addbff4625d4c9b0.vbproj.ScriptMain.Main() --- End of inner
January 6, 2011 at 5:07 pm
You're welcome. Bummer that it did not help you with your problem.
When you execute the package in BIDS, are you executing it from the same server on which you're trying to schedule it with SQL Agent?
If not, is Excel installed on your SQL Server?
Also, have you seen this?
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/f682f047-153c-4ced-81f0-0b2c8d68f1ad/[/url]
I'm just taking a stab in the dark here, not having had any experience with that error before... maybe someone else here can provide some actual help?
January 7, 2011 at 12:14 pm
VIOLA !!!
Thank you for all those who have contributed in helping me solve this issue, I chased it like a goose & finally got it working.
Creating a .\SysProxy account on the target server solved the problem. Actually stopping all the other services such as SQL 2005 Agent service / SQL 2000 Agent Service , SQL 2005 Analysis Service / SQL 2000 Analysis service & all the other unwanted garbage services really helped, looks like it was locking the system in some wierd fashion.
So the moral of the story is "Keep your house Clean & Tidy "... something to learn..
Every thing else was in place ( like Excel installed , 32 bit system etc. etc. running on the same server. )
I researched a lot to get it to this place, creating a desktop folder in systemprofile etc. etc...
Thanx , again,
January 7, 2011 at 12:18 pm
Congratulations. Glad to hear you got it working!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply