CreateObject fails when package scheduled (have checked permissions)

Question

CreateObject fails when package scheduled (have checked permissions)

SimonF-362472

SSC Journeyman

Points: 98
More actions
April 5, 2007 at 2:53 am

#177560

Good morning
I'm having difficulting running the CreateObject method when a DTS package is scheduled. It runs absolutely fine in development mode on the same server it is to be scheduled on. The problem relates specifically when trying to create an object for an Cognos Impromptu application, and a stripped back version of the code is as follows :
Function Main()
Dim objImpApp
Set objImpApp = CreateObject("CognosImpromptu.Application")
Set objImpApp = Nothing
Main = DTSTaskExecResult_Success
End Function
This will generate the following error :
Executed as user: MBDSQLSRVTEST\sqluser. ...... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error string: Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'CognosImpromptu.Application' Error on Line 13 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'CognosImpromptu.Application' Error on Line 13 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun: Package execution comp. The step failed.
I've done many internet searches and they all point to problems with permissions, but after having tried a number of suggestions I'm now banging my head against a wall. I have tried :
- adding the user (sqluser) that runs the services MSSQLSERVER and SQLSERVERAGENT to the administrators group
- ensured that the user (via the administrators group) has full access to every single folder on the system
- ensured that all referenced components reside on the C drive
- used the dcomcnfg.exe utility to give the administrators group (as well as the sqluser itself) access to all Cognos DCOM objects
The server is running Windows 2003, and a rather dated version of SQL Server (2000).
Any help on this would be gratefully appreciated!!
Cheers, Simon

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 739410 More actions · Answer 1

It could be permissions or some config with this being created.

If you log in as the user (SQLAgent), can you run this as a DTExec command line? Can you run a similar VBSCript file as this user?

I have no idea either. Just thinking fo things to check.

SimonF-362472 SSC Journeyman Points: 98 More actions · Answer 2

Thanks for the input!

dtsexec would appear to be for SQLServer 2005; we're still on SQLServer 2000 Anyway, I logged on to the server as the user "sqluser" (which is the user that runs the SQLAgent service) and at the command prompt I ran dtsrun ........ all appears to be well :

C:\WINDOWS>dtsrun /S "mbdsqlsrvtest" /N "Finance_Inventory_Valuation_TEST" /U "sa" /P "*******"

DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSActiveScriptTask_1

DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1

DTSRun: Package execution complete.

C:\WINDOWS>

Any further suggestions welcome!

Cheers, Simon