April 7, 2010 at 11:25 am
Need some assistance please. I developed an SSIS package to import a couple of Visual Foxpro tables into SQL Server 2005. It is stored in the Integrations Services Store on the server. The package runs perfectly fine in the the following environments:
1. SSIS Development environment
2. SQL Server - Integration Services package store location on the server
The problem comes after I create a job using SQL Server agent. Whether scheduled or run manually the job fails and it fails with the error codes below. The server is 32-bit and that's represented in the error message so that rules out the 64-bit issue I have seen floating around. The package runs normal in the DDID development environment and the Integrations Services package store so the OLEDB driver is working fine. I have changed the Log On account for the agent to the domain admin user and that has had no effect.
At this point I can only conclude the problem is with the SQL Agent but I have no idea as to what it could be. Any help would be greatly appreciated.
Message
Executed as user: OCEANBEAUTY\obsidb. ...9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:36:04 AM Error: 2010-04-07 09:36:07.69 Code: 0xC0202009 Source: Sync_SQLTables_Ops Connection manager "NetYield" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2010-04-07 09:36:08.28 Code: 0xC020801C Source: Load data Source [13885] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "NetYield" 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: 2010-04-07 09:36:08.63 Code: 0... The package execution fa... The step failed.
April 7, 2010 at 11:41 am
Quick update...I took the command line string from the job and ran it manually. Had to remove the SMTP connection line but as you can see below, it ran just fine.
C:\Program Files\Microsoft SQL Server\90\DTS\Binn>dtexec.exe /DTS "\MSDB\OceanBe
auty\SQLTables_Ops" /SERVER "OBSI-DB" /CONNECTION NetYield;"\"Data Source=\\OBS
I-APPS\APPS\NetYield\Data\SeattleOpsetyield.dbc;Provider=VFPOLEDB.1;\"" /CONNE
CTION SQL;"\"Data Source=OBSI-DB;Initial Catalog=SeattleOps;Provider=SQLNCLI.1;I
ntegrated Security=SSPI;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING
E
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: 10:36:24 AM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 10:36:24 AM
Finished: 10:38:01 AM
Elapsed: 97.015 seconds
April 7, 2010 at 4:25 pm
When you stored the package in the MSDB store on the server, what security settings did you select?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 4:29 pm
Rely on server storage and roles for access control.
I can run it fine in MSDB, it's just when the agent gets involved it errors out.
April 7, 2010 at 4:39 pm
Is the agent running on a different service account? I'm thinking there may be a permissions issue.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 4:45 pm
Local System account. I switched it at one point to the domain admin login though and same result.
April 7, 2010 at 4:59 pm
Would this be of any application to your package?
TransactionOption=Supported rather than TransactionOption=Required ?
Another recommendation is to check security settings of MSDTC.
In the sql job, which method are you using to execute the package? Using the ssis option or the operating system (cmdexec)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 5:42 pm
The package is set to TransactionOption=Supported. The SQL job is using SSIS. How do I check the security on MSDTC?
This is the production server. On the development server, the job runs fine. And of course, I see no difference between them.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply