SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Package to create Excel Spreadsheet fails in SQL Agent


SSIS Package to create Excel Spreadsheet fails in SQL Agent

Author
Message
charles tisch
charles tisch
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 14
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

Message
Executed as user: XECC\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:49:42 AM Error: 2010-03-19 08:49:46.69 Code: 0x00000002 Source: create_spreadsheet Description: The script threw an exception: Exception from HRESULT: 0x800A03EC End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:49:42 AM Finished: 8:49:46 AM Elapsed: 3.884 seconds. The package execution failed. The step failed.

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 .

Here is my Vb code

sql = "select * from logs order by [0]"
cmd = New OdbcCommand(sql, cn)
rs = cmd.ExecuteReader

Dim app As New Microsoft.Office.Interop.Excel.Application
Dim WB As Microsoft.Office.Interop.Excel.Workbook

app.Visible = False
app.UserControl = False
app.DisplayAlerts = False

WB = app.Workbooks.Add
WB.Application.Cells.Select()
WB.Application.Selection.NumberFormat = "0"

Do While rs.Read = True

k = CType(rs("0"), Integer)

For i = 0 To 170
If IsDBNull(rs.Item(i)) = True Then GoTo finish
j = i + 1
WB.Application.Cells._Default(k, j) = rs.Item(i)
Next
finish:

Loop
rs.Close()

WB.Application.Cells.EntireColumn.AutoFit()
WB.Application.Range("A1").Select()
WB.Sheets(1).Name = "logs"

When I comment this statement out, the SQL Agent job works fine.
==================================================
'WB.SaveAs("D:/excel_folder/logs.xls")
================================================

WB.Close()
WB = Nothing

app.Quit()
app = Nothing


System.GC.Collect(0)
System.GC.WaitForPendingFinalizers()

cn.Close()
cn.Dispose()

Dts.TaskResult = Dts.Results.Success

I am wondering if because Excel is involved now is there something in security that is causing the job to fail?

I am wondering if because Excel is involved now in this package is there something in security that is causing the job to fail?
VG-619426
VG-619426
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1882 Visits: 692
How you are calling the SSIS package from the job, I mean through dtexec? There are issues with support to 32 bot and 64 bit excel... Please read more on this....

VG
charles tisch
charles tisch
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 14
Everything on the server is 32 bit. There is no 64 bit software.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109286 Visits: 22278
If you physically log in to the server as user XECC\Administrator and then execute the package through BIDS, does it execute OK?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
charles tisch
charles tisch
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 14
I physically signed on to the server as Admin and executed the package in BDIS and it worked fine and wrote the spreadsheet to the file as it is suppose to do..

I then opened the SQL Server Management Studio and connected to Integration Services and executed the package from there, and again it worked fine .

I then created a new SQL Agent job being signed on as Admin and when I ran the job which executed the package, it failed with the same error message as before.
CinnamonGirl
CinnamonGirl
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 77
What account does the SQL Agent run under and does that account have the appropriate share permissions to write files?



Muthamil_c
Muthamil_c
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 27
if you use microsoft.interop.excel then only admin and the system user had the permission to generate the excel.so create a proxy account and call the job through that account.
ajagadambe
ajagadambe
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 37
Hiya, I had this problem too.

The way I solved it, was by creating a folder called "Desktop" in "C:\Windows\System32\config\systemprofile\" and I was able to run it without any problems.

I hope that helps Smile
SlowJamuels
SlowJamuels
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 102
I was having this problem too.

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.

Once I did that, the job started working.
skumbale
skumbale
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 77
Hi Charles,

Did you managed to get it working. I have the similar issue.

Thanks
Sree
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search