October 2, 2009 at 11:12 am
I have a server running Windows 2008 Enterprise Edition, Sql Server 2008 Enterprise edition. I took a DTS package and converted it to an SSIS package using Visual Studio. Data is pulled from a local database and then written out to a excel file. All occurs on the same server. I have executed the package using "Execute Package Utility" with no problem. I have executed the package in Visual Studio with no problem. I setup a Sql Server agent job and as one of the Steps run this SSIS package. Under the Execution options tab I checked the Use 32 bit option. I understand there are no 64 bit drivers for excel and so the package can only be run as a 32 bit application. When I run the job I get the following error with or without the 32 bit option set:
Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:05:50 AM Error: 2009-10-02 10:05:51.63 Code: 0xC0202009 Source: SSIS - HEAT_AOTX Create SDSD Palm Contacts Connection manager "Microsoft Excel 97-2000" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Failure creating file.". End Error Error: 2009-10-02 10:05:51.63 Code: 0xC020801C Source: DTSTask_DTSDataPumpTask_1 OLE DB Destination [31] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft Excel 97-2000" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2009-10-02 10:05:51.63 Code: 0xC0047017 Source: DTSTask_DTSDataPumpTask_1 SSIS.Pipeline Description: component "OLE DB Destination" (31) failed validation and returned error code 0xC020801C. End Error Error: 2009-10-02 10:05:51.63 Code: 0xC004700C Source: DTSTask_DTSDataPumpTask_1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2009-10-02 10:05:51.63 Code: 0xC0024107 Source: DTSTask_DTSDataPumpTask_1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:05:50 AM Finished: 10:05:51 AM Elapsed: 1.154 seconds. The package execution failed. The step failed.
The user running the job is a domain user and has full access. I saved the package to run under the users credentials, no encryption.
I ahve read all the articles I could find on this and tried everything that was suggested but I am still stumped. I read something about "32-bit application can be run under the WOW64 mode in a 64-bit windows server" but not enough information was provided to see if there was a connection to what I was doing and how to test...Many of the articles were for Sql2005 and mentioned running job using the 32 bit dtsexec.exe but since this is Sql2008 setting the 32 bit option should have taken care of that issue.
Any suggestion would be appreciated.
October 2, 2009 at 11:36 am
Could this apply?
http://support.microsoft.com/kb/933835
Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
October 2, 2009 at 3:45 pm
I checked to make sure the user running the job had write priviliges on the Temp directory and he did. On a lark I decided to write the file out to a local directory on the C: drive and wouldn't you know it the job ran fine. I remote logged in to the DB server and tried to map the drive I was attempting to write to and to my amazement I could not. We use two distinct drives in our organization to save everything. From my desktop I was able to map the drives so I didn't suspect there was any issues with these drives. All my test were run from my desktop but when I ran the job it obviously was running on the server and that was the reason for the failure. The SysAdmin folk are trying to figure out why this and a few other servers are loosing connection since these drives are supposed to be mapped at all times...
Thanks for the help...
October 2, 2009 at 4:42 pm
Ahh - mapped drives.
Don't do those. Drives get mapped when you log on to a system - and Agent jobs "run as" an account don't log on, so drives don't get mapped. You should use UNCs if possible. If not, you'll have to do an explicit "net use" command in a prior jobstep.
Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply