problem in scheduling

Question

problem in scheduling

mamsay

Say Hey Kid

Points: 701
More actions
January 7, 2009 at 1:34 am

#198067

hi,
im having a script task which opens an excel file and extract some data into database. when i run this package it run successfully. but when i schedule this package some errors occuring. i know the problem is with script task only but i don know wat is it.
first when i schedule the package the following error occurs
Programmatic acces to visual basic project is not trusted.
then after sometime when i schedule it, it shows the following error
cannot create ActiveX component
then after sometime when i schedule it, it shows the following error
The script threw an exception: Unable to get the Open property of the Workbooks class
i don know wat is happenning.. the script task is running well if i run it manually..
any help is appreciated
thanks in advance

Viewing 12 posts - 1 through 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

Can you log in to your system interactively as the same user that runs the scheduled job (ie the SQL Server Agent user) and then try running the job again - just to rule out any security / privileges issues?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mamsay Say Hey Kid Points: 701 More actions · Answer 2

hi,

hearty thanks for your reply...

if i disable that script task and schedule means the package is running smoothly. if i enable that task then it is showing the above said errors..

so it means im able to schedule the packag!! rite?

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

I am not suggesting a scheduling problem. I am suggesting the possibility of a user-privileges problem. Maybe your script does something that you (or the user that you were logged in as) are allowed to do and the SQL Agent user is not. Particularly if the SQL Agent service is running under a local system account, rather than a domain user.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mamsay Say Hey Kid Points: 701 More actions · Answer 4

i did the same.. but the same error.. wat i have to do now..

thanks in advance

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

dyana (1/7/2009)
hi,
im having a script task which opens an excel file and extract some data into database. when i run this package it run successfully. but when i schedule this package some errors occuring. i know the problem is with script task only but i don know wat is it.
first when i schedule the package the following error occurs
Programmatic acces to visual basic project is not trusted.
then after sometime when i schedule it, it shows the following error
cannot create ActiveX component
then after sometime when i schedule it, it shows the following error
The script threw an exception: Unable to get the Open property of the Workbooks class
i don know wat is happenning.. the script task is running well if i run it manually..
any help is appreciated
thanks in advance

From the error messages, I'm guessing you are doing some kind of Excel automation. Correct? If that is the case, you have to make sure that the account you are using for SQL Job execution have enough permissions to work with the Excel API.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

mamsay Say Hey Kid Points: 701 More actions · Answer 6

hi,

you are absolutely correct... i'm trying for excel automation only.. how can i check that i have enough permissions or not.. wat i have to do.. now im working with power user previleges.. if i get admin previleges means can i schedule the package?

thanks in advance....

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

dyana (1/7/2009)
hi,
you are absolutely correct... i'm trying for excel automation only.. how can i check that i have enough permissions or not.. wat i have to do.. now im working with power user previleges.. if i get admin previleges means can i schedule the package?
thanks in advance....

What account do you use for execution in SQL Job Agent? Did you try to setup proxy account?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

mamsay Say Hey Kid Points: 701 More actions · Answer 8

hi,

i did not try for proxy account.. actually if i disable the script task, then im able to schedule the package. it is running smoothly. but the problem occurs with script task only while connecting to the excel sheet..

in one thread i saw the problem is due to COM object... wat is that? how can i enable that COm object? is that hte solution?

COZYROC One Orange Chip Points: 28499 More actions · Answer 9

dyana (1/7/2009)
hi,
i did not try for proxy account.. actually if i disable the script task, then im able to schedule the package. it is running smoothly. but the problem occurs with script task only while connecting to the excel sheet..
in one thread i saw the problem is due to COM object... wat is that? how can i enable that COm object? is that hte solution?

COM object is the Excel automation object you are using in your script task. It is an older technology still used by many Microsoft products.

Try to setup a proxy account by following the description in the link I have sent you.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

mamsay Say Hey Kid Points: 701 More actions · Answer 10

hi cozyroc,

i created a proxi account and now it is working.. thousands of thanks for all of you people..

but script is throwing pop up's some times like want to save the file click yes or no. that time scheduling haults. how to avoid this pop up

COZYROC One Orange Chip Points: 28499 More actions · Answer 11

dyana (1/8/2009)
hi cozyroc,
i created a proxi account and now it is working.. thousands of thanks for all of you people..
but script is throwing pop up's some times like want to save the file click yes or no. that time scheduling haults. how to avoid this pop up

To avoid the message boxes generated by Excel you have to setup these Excel.Application properties:

workBook.Application.DisplayAlerts = False

workBook.Application.Interactive = False

---

Because these are Excel specific questions, if you have additional Excel related questions I would recommend that you ask in a different forum. This is SSIS specific forum.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/