Sql Server Agent Problem With Excel Automation
Mar 19, 2008
H...
I have a problem to run a package with a scrip task containing the code in this page. I try to create a new excel workbook, change some cells, and save it to the harddrive. When designing the package in BI dev studio, I can execute it without a problem. I can even run it with DTexec.exe. The problem is that when I run this package from the SQL server agent, then it breaks. It breaks on a machine running Windows server 2008 with SQL server 2008 on it and it also breaks on a machine running Vista with sql server 2005, but it runs fine on a windows server 2003 machine with sql server 2005 on it.
I can also mention that if I dim out the one line after the comment in the code, then It runs fine on any one of my machines. The problem seems to be with saving or opening an excel file. I am also making use of a proxy with a credential using my login name. I am part of the administrators on these machines.
Imports eXcel = Microsoft.Office.Interop.Excel
Public Sub Main()
Dim objExcel As New eXcel.Application
Dim objSheet As eXcel.Worksheet
Dim strFname As String = "c: empBook3.xls"
objExcel.DisplayAlerts = False
objExcel.Application.Interactive = False
objExcel.Workbooks.Add()
objExcel.Cells(1, 1) = "0"
'I dim out the next line and it works from sql server agent on any machine
objExcel.ActiveWorkbook.SaveAs(strFname)
objExcel.DisplayAlerts = True
objExcel.Application.Quit()
objSheet = Nothing
objExcel = Nothing
Dts.TaskResult = ScriptResults.Success
End Sub
The error that I receive is this one:
Executed as user: BS-PROCESS1dlangec. ...0.0.1300.13 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:25:16 PM Error: 2008-03-18 22:25:22.40 Code: 0x00000001 Source: Script Task Description: System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC Server stack trace: at Microsoft.VisualStudio.Tools.Applications.RemoteTypeAdapter.System.AddIn.Contract.Automation.IRemoteTypeContract.InvokeMember(String name, BindingFlags bindingFlags, IRemoteObjectContract target, IRemoteArgumentArrayContract remoteArgs, Boolean[] remoteArgModifiers, Int32 lcid) at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 metho... Process Exit Code 1. The step failed.
View 9 Replies
ADVERTISEMENT
Aug 11, 2007
Hi all,
I have a task which needs to be automated.
1) Excel spreadsheet load into sql server database as a table.
2) Then , there will be some quering of data joining this table ( imported from excel) with other tables.
3) Then, output should be back into excel spreadsheet.
All these steps 1,2,3 need to be automated.
Could anybody give me the different steps(if not,, any links which guide me doing so), for doing this and any pre setup required for this.
Thanks for all your help..
View 1 Replies
View Related
Jul 20, 2005
Hi,I tried to add column headers and delete all rows to excel databasewhich is a linked server to my sql server db.I got an error message, "delete data through linked server is notsupported by Isam".I tried to use FSO to write to the excel file, but seems like FSOsupport write to only text file.the last time I tried to create an excel applicaiton object and temperwith its cell, I hang my Excel application and had to reinstall it.Have any ideas,Thank you very much
View 1 Replies
View Related
May 8, 2006
We are not certain if this has happened due to the SSIS FTP Task, but incidently the Excel file that is being copied from the FTP site using an SSIS FTP Task got corrupted a couple hours after the package was scheduled as a SQL Server Agent Job on SQL Server 2005.
I had a SQL Server 2000 DTS package doing the same thing, but it was never an issue then. I was using the FTP Task there along with an Excel Data source in that and has been working for a couple years atleast with never any corruption related issues.
In the SSIS SQL Server 2005 package I am using an FTP Task with an Excel Connection Manager and Excel source and the Excel file got corrupted within a couple hours of the package being scheduled as a SQL Server Agent job.
Has anyone experienced this issue? Any inputs will be appreciated.
Just as an fyi, the excel file has a lot of vlookups.
Thanks,
MShah
View 1 Replies
View Related
Oct 19, 2010
I have a SSIS package which reads an excel file and loads data into a table using script component(C#) as a source. The package runs without any errors when I manually run it on my machine and on the server. But the package fails when run as a SQL Server Agent job.
I tried all the possible fixes I found on the web but still can't get it to work.
View 14 Replies
View Related
Nov 30, 2004
Hello Experts,
I am a New Bee in SQL.I would like to know,As a System Admin I would like to automate the task of keeping the SQL Server up to date with latest Service Packs and Patches.
Is there any procedure / Software where I can automatically download the patch / sp's from the site and install on the servers or maybe schedule the task for every month.
I would appreciate a reply from the experts
Best Regards
SunilVKulkarni
View 1 Replies
View Related
Jul 8, 2006
I'm a newbie to SQL Server, trying to make the leap from Access because I'm starting out grow it for my needs. I have a lot of automation stuff in Access where it does a query/code driven process for us via a macro. Sometimes the output is a report, or a form with assembled data that we use. I'm wondering if SQL Server 2005 is capable of doing automation of processes like these as well. Even if it's not necessarily a macro, something I can program, compile and have use off of my SQL Server database to do the tasks that the Access macro did.
View 1 Replies
View Related
Jun 4, 2015
Overview of Automation with SQL server.
View 2 Replies
View Related
Jun 20, 2007
Hello! I have an SSIS package that basically collects Excel files from an FTP server, deposits them to a file share on a SQL server and then processes each Excel file into a SQL 2005 database using the ForEach enumerator.
The package runs flawlessly when executed directly on the SQL server. The problem starts when I set this package to run via the SQL Agent. I have looked through these forums and other places and have done what this article suggested http://support.microsoft.com/kb/918760; changing my package protection to
"Dont Save Sensitive" and setup the package to store its configuration in a SQL table. Despite all this I still continue to get this error when the package is run via the Agent:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ExcelSourceFile" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
The Excel files are not password protected, no funky settings, nothing complicated, its just a plain Excel file with a few rows of data on one worksheet. The package transfers the files from the FTP server to the share on the SQL server with no problem, I can see that the files exist on the share, so it isnt a case of the Excel connection manager not finding any files to process.
The Agent account is running under administrator privileges and has full access to the share, so it shouldnt be a permissions issue either.
I have no idea what else to check, any ideas?
Thanks!
View 11 Replies
View Related
Mar 23, 2015
I have a 2 node cluster with 2 standalone 2k14 instances having alwayson setup. As per client requirement we have created a client access point with a cname alias in dns to connect to secondary replica. Now, everytime whenerver the roles switch over one has to manually move this resource from the previous secondary node to the new secondary node. This is tedious, and should not be done manually either, so I am looking for a way to automate it so that as soon as the role switches over, the resource group after some time should also switch over to the current secondary.
Env details
xa (current primary)
y2a (secondary)
client access point (resource group): aglreadtest
View 2 Replies
View Related
Jun 15, 2007
Hello kindly help me in this issue , i have an ssis package with excel data source , when i run it outside the sql
agent job it run normaly when i create a job to execute it from the sql server agent it fail.
can any body help me.
also i had same problem with packages that call the execution of other packages.
View 7 Replies
View Related
Nov 30, 2006
when I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Thanks,
Larry
View 1 Replies
View Related
Aug 13, 2015
I have a package from SQL Server 2008 R2, that loads data from .xlsx file to database table.There are total 15 columns and 14000 rows in the .xlsx. The package runs fine in BIDS. But the same package in SQL Agent fails with error "omponent "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN".
When I tried to run the package by deleting the half of the records for first 7000 rows it ran successfully in agent. Then the second half (last 7000 rows) also succeed from agent job. So, there is no issue with the data/datatypes.The agent job is able to run with record upyo 11000 rows in .xlsx. When I am running for 12000 rows it is failing.Is there any problem with the number of records in .xlsx or size through SQL Agent?
I am running the package from a Proxy account in sql agent job.
ERROR:
Error: Executed as user: PROXY_ID. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:36:09 AM Error: 2015-08-10 10:36:10.87 Code: 0xC0202009 Source:
XX Connection manager "Excel Connection Manager 1"
[code]....
View 5 Replies
View Related
Feb 19, 2007
We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?
View 4 Replies
View Related
Oct 13, 2000
I have three problems related to Sql Server
a.I am developing a database application using VB5/VB6, SQL server 7 and Crystal reports.When I try to generate reports I get the message 'Automation error'. Please help-urgent.
b. I am using temperorary tables to generate reports.ie Table is created by user.Tables are generated runtime ie created as well as deleted. Sometimes
Sometimes i get the message 'SQLServer error' What should I do.What are the rights to be assigned to LOGINS?.
c.Is there precaution while installing SQLserver in WIN98.
View 1 Replies
View Related
Dec 1, 2007
Is there a way to automation SQL on an SQL Server to perform
impports and exports at a certain of the night. The server is
always running. Thank Dee
View 6 Replies
View Related
Feb 5, 2001
I'm trying to bring down a file from the AS/400 to update a SQL database table. Either thru the configuration limitations on the SQL server or ??? I cannot reach the as/400 from my SQL box so I'm running the data down to a Citrix server to a text file via client access.
I need to automate the DTS package run on the SQL box to pull the data from the Citrix server over to the SQL box.
How do I set up the DTS package to run on a schedule?
View 2 Replies
View Related
Apr 30, 2008
One of my developers needs on enable Ole Automation on production? What security loop holes we are creating, by enabling this feature in SQL2K5?
------------------------
I think, therefore I am - Rene Descartes
View 5 Replies
View Related
Dec 3, 2007
How can I import an xml file to SQL 2005 at the same time every night? I
will need to create a new database first via the import after that I will be
appending to the dataabase. Then I will need to xport the data into a
different xml file and upload to my hosting system which is yahoo. All so the
original xml file is on the yahoo. store server.
I have SQL 2005 standard install on my personal work stations. When I iron
the bugs out I will be running this from our SQl 2005 Enterprise server.
Thank you
Dee
View 11 Replies
View Related
Mar 18, 2001
We've an a VB application connecting to SQL Server 7. Recently the log files show 'Automation error' in one of the routine. I've scan thru the code & pass in some test case & it is ok.
Any idea what could have cause the 'Automation error'?
Thanks in advance.
Ben
View 2 Replies
View Related
Jul 9, 2007
Hi,
I have just taken over the DBA role for an application which at best you can describe their DR plans as woeful! So basically i am starting from scratch and trying to automate the database re-creation as much as possible. I have managed to write some bespoke scripts for backup devices, and user ids etc., but the main bulk of the work is in the re-creation of the 300 databases they have (boy i wish i had been here when they did this database design!!).
Anyway, onto my question. I know that i can go to each database in SQL Enterprise manager (SQL 2000 by the way), and right click and select generate scripts and it will do it for me, but i was wondering (and hoping) that there was a API interface that i could utilise which would allow me to pass in the database name and for it to generate the scripts automatically. Height of laziness i know, but it would allow me to generate the scripts for the databases on a regular basis in order to keep the DR recovery scripts up to date.
Thank you in advance for your help, even though i am guessing the answer will be "Tough, you have to do it by hand!"
Regards
Mark
View 15 Replies
View Related
Feb 2, 2007
Hello,
Last night was the first night we used Sql Server Agent to run our Warehouse ETL packages. We use the built in logging of SSIS as well as our own event logging mechanism as it goes through all of the packages. Unfortunately, when we checked this morning the agent showed failure but in both logs from the packages no errors were reported. I checked the Windows event log and the Sql Server logs but found nothing that would appear to report an error.
Does anyone have any ideas what I might be missing, any recommendations on things to check, we have manually been running these packages for weeks with no problems. Any help would be greatly appreciated! Thank you in advance!
View 2 Replies
View Related
Nov 17, 2006
In MSS 2000, it was possible to have a stored procedure that used OLE Automation to execute a DTS package. E.g.
declare @oPKG int
exec sp_OACreate 'DTS.Package', @oPKG OUTPUT
declare @pkgName varchar(128)
declare @server varchar(20)
declare @cmd varchar(1100)
declare @userid varchar(20)
declare @ServerPWD varchar(20)
set @pkgName = 'PKTestOuter'
set @Server='myServer'
set @userid='myuser'
set @ServerPWD='myPW'
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + @userid + '", "' + @ServerPWD + '", 0, , , , "' + @PkgName + '")'
EXEC sp_OAMethod @oPKG, @Cmd,NULL
exec sp_OAMethod @oPKG, 'Execute'
When I move - but not migrate - packages to 2005, this still works. I don't have a problem with permissions or any of the other problems that I have seen posted in this forum. But if I migrate a package to SSIS, the package is now seen in sysdtspackages90 instead of table sysdtspackages (assuming I remove the old version). But "LoadFromSQLServer(...)" can't find it anymore. So it seems like one of the following is true:
a) There is a different item that I should use in sp_oacreate besides DTS.Package. I tried SSIS.Package or DTS.Package90 which seemed like reasonable possibilities but they don't work. Is there something that will work? Or is "LoadFromSQLServer(...)" not supported for SSIS packages?
b) Or OLE Automation doesn't work with SSIS packages. OLE Automation is enabled and it works on non-migrated packages. Is it true that it was decided by Microsoft that this capability was not needed in SSIS?
I see numerous postings saying not to use the sp_OA routines. I understand there are other ways to execute an SSIS package from a SP and that there may be more secure ways. But I need to know if it is an absolute requirement to use these other methods.
Paul
View 1 Replies
View Related
Jun 18, 2007
hi
i have created a rss script for report deployment which i working pretty good but icheckout my all reports on report server and from another machine i mapped that drive from command line and run rss script then i got following error
================================================================
:Automation>rs -i PromotionAutomation.rss -s http://fsaleem/reportserver -v en
v=Dev -v DBServer=fsaleem
Unhandled Exception: System.TypeInitializationException: The type initializer fo
r "Microsoft.ReportingServices.ScriptHost.ScriptHost" threw an exception. ---> S
ystem.Security.SecurityException: Request for the permission of type System.Secu
rity.Permissions.FileIOPermission, mscorlib, Version=1.0.5000.0, Culture=neutral
, PublicKeyToken=b77a5c561934e089 failed.
at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet granted
Set, PermissionSet deniedSet, CodeAccessPermission demand, PermissionToken permT
oken)
at System.Security.CodeAccessSecurityEngine.Check(PermissionToken permToken,
CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 checkFrames, Int32
unrestrictedOverride)
at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission cap, S
tackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Demand()
at System.Reflection.Assembly.VerifyCodeBaseDiscovery(String codeBase)
at System.Reflection.Assembly.GetName(Boolean copiedName)
at System.Reflection.Assembly.GetName()
at Microsoft.ReportingServices.ScriptHost.ScriptHost..cctor()
The state of the failed permission was:
<IPermission class="System.Security.Permissions.FileIOPermission, mscorlib, Vers
ion=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
PathDiscovery="Z:AutomationRS.exe"/>
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ScriptHost.ScriptHost.Main(String[] args)
================================================================
View 1 Replies
View Related
Jan 11, 2007
hello
is it possible to generate PDF files from reports programmatically ?
View 7 Replies
View Related
Aug 16, 2006
Hey,How can I schedule a stored function to run atleast once a day, other than by having it as a scheduled task on the server?Sql Server 2000Thanks
View 3 Replies
View Related
Jul 23, 2007
I have spent the last couple of weeks going through about 15-20 different examples/tutorials on running DTS packages from inside a C# Windows app (and even asp.net). I must have a mental block, because I can't seem to make it click in my head. I have hundreds of DTS packages saved as .dtsx files. I can double click on them and run them perfectly. What I need to do is be able to run them conditionally from inside a windows app. Could anyone point me to a tutorial or example that could help me with this? I've even looked at some VB source to see if I could work it out, but I keep running up against a wall. I'd appreciate any help anyone can provide.
View 1 Replies
View Related
Oct 24, 2000
I have been trying to execute the following code:
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @output varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)
--Instantiate a Network Object
EXEC @hr = sp_OACreate 'WScript.Network', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a property using an output parameter.
--EXEC @hr = sp_OAMethod @object, 'userDomain' --,@property OUT
EXEC @hr = sp_OAGetProperty @object, 'userDomain'--, @property OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
--PRINT @property
-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
I get the same behavior stated in microsoft's support site related to BUG
55840 for Sql Server 7.0.
I get an error message when trying to retrieve with @property OUT parameter.
And I get an Empty record set when NO Output param is supplied.
http://support.microsoft.com/support/kb/articles/Q236/4/40.ASP
I am running Sql Server 7.0 SP2 on Windows 2000 AS.
SP2 has this BUG listed as being Fixed. Am I missing something?
Thanks in Advance for your Help,
Chris
View 3 Replies
View Related
Apr 8, 2002
Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay
View 11 Replies
View Related
Sep 18, 2007
Hello everybody ,
I need to create a automation job for 2005 analysis services database to backup a database every night . i dont have sql server agent to create a job. I was told about a method to create a batch file and than use ssis or something similar to execute the job.
can anyone please help me out in guiding me with proper documentation to perform this procedure so that i can create a automation job without the help of sql server agent to create a backup job every night for anaylysis services 2005 database ......analysis services 2005 only
Thanks in advance
Yukon DBA
View 1 Replies
View Related
Jul 20, 2005
Hopeing this is something I can accomplish. I use MS-SQL for close to100 CMS web servers. Everytime I create a new website I have to createa new database and then use the export from SQL to SQL option and copyall data / SP's / etc... Just no security information. My question iscan I automate this? Can I create a script that will run all this?Thanks for any help, and thanks for reading.Chris AuerJoin Bytes!
View 2 Replies
View Related
Jul 20, 2005
I have written quite a complex query in Query Analyser, now need toknow how to automate the running of the query to dump the results intoa flat file at scheduled times. Am an experienced Unix user, where itwould be quite simple but new to sql server, is there a relativelysimple way to do this?
View 1 Replies
View Related
Mar 17, 2008
can any one send me a useful link which is easy to undersatnd Step by Step for Aumaotion of Reports Deployment?
View 4 Replies
View Related