August 10, 2010 at 8:02 am
I recently started a new gig and they have a job already in place that was running before I started here. The problem is that as soon as they let the other guy go and revoked his access, this job stopped working.
I'm an ace DTS programmer, but I am new to the whole SSIS thing.
When I view the Job History, the error is
[font="Courier New"]Unable to start execution of step 1 (reason: Error authenticating proxy LAMUCO\doug.hesseltine, system error: Logon failure: unknown user name or bad password.). The step failed.[/font]
Doug is the guy I replaced. I opened up the .dtsx file and I opened the package itself in the Business Intelligence Dev Studio. I can't find anyplace that I haven't replaced his creds with my own, but I still keep getting this error.
What am I missing? (besides not knowing what I'm doing with ssis)
August 10, 2010 at 8:31 am
It's not the package that needs to change, it's the job proxy. This is a server config issue. A proxy is used to give an execution environment for a package and is created on a server and assigned to a job at the step level. If you go into the job step you will see it under "Run As". Basically you need to ensure that you have either a proxy with adequate rights or else give those rights to the service account and run it as the Agent account (I prefer this; proxies are mainly needed if non-SA accounts need to run jobs).
August 10, 2010 at 9:53 am
For one reason or another, it has to be Package Proxy. So I went to the Proxies and saw that, yes, Doug was set as the SSIS Package Execution Package Proxy. I tried to change it to myself and figured out fast that I had to set myself up as a Credential 1st in the Security section. When I set myself up as a credential, I used the same password that I use as my network password just for the sake of consistency (was that necessary?).
So now I'm a credential, I set the SSIS Package Execution proxy to me, and tried to manually run the job. FAIL
Something different did happen this time though. Previous failures showed in the log as only 1 line with the red x thing and the error message I previously described. Now I'm getting 2 lines. 1 with a green arrow and 1 with a red x.
GREEN ARROW MESSAGE
Date8/10/2010 10:44:24 AM
LogJob History (Sagitta Sync SSIS)
Step ID1
ServerLMCVSQL1
Job NameSagitta Sync SSIS
Step NameSync from sagitta to SQL
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
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:44:24 AM
Error: 2010-08-10 10:44:25.80
Code: 0xC0047062
Source: Load U2 Data Contacts ADO NET Source [1]
Description: System.Runtime.InteropServices.COMException (0x80131937): Exception from HRESULT: 0x80131937
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
End Error
Error: 2010-08-10 10:44:25.80
Code: 0xC0047017
Source: Load U2 Data Contacts SSIS.Pipeline
Description: component "ADO NET Source" (1) failed validation and returned error code 0x80131937.
End Error
Error: 2010-08-10 10:44:25.80
Code: 0xC004700C
Source: Load U2 Data Contacts SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2010-08-10 10:44:25.81
Code: 0xC0024107
Source: Load U2 Data Contacts
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 10:44:24 AM
Finished: 10:44:25 AM
Elapsed: 1 seconds
RED X MESSAGE
Date8/10/2010 10:44:24 AM
LogJob History (Sagitta Sync SSIS)
Step ID1
ServerLMCVSQL1
Job NameSagitta Sync SSIS
Step NameSync from sagitta to SQL
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: LAMUCO\robin.pryor. The step failed.
August 10, 2010 at 10:34 am
That one WAS an SSIS failure; you are past the previous error. At this point, best I can tell you to do is set the package to log its errors. This is very different in SSIS from DTS. You'll have to bring the package up in BIDS and configure it to log errors and task fails to a log file and then redeploy the package. There's not really enough information in that error to say why the package failed, but if it logs to a file it should tell you what's happening.
August 10, 2010 at 11:01 am
I'm looking at BIDS right now and not seeing where to make this setting
August 10, 2010 at 11:02 am
SSIS menu -> Logging.
August 10, 2010 at 11:18 am
Thanks..
OnProgress,LMCVSQL1,LAMUCO\robin.pryor,Load U2 Data Contacts,{839F3385-A68F-49F6-B357-76A5A0FCE65C},{1D70D3CF-C85E-4619-93C0-EB38CD9F39EE},8/10/2010 12:09:51 PM,8/10/2010 12:09:51 PM,0,0x,Validating
Diagnostic,LMCVSQL1,LAMUCO\robin.pryor,From_U2,{C05CA7A6-1A6E-45FA-99FC-A8F16113FCDF},{1D70D3CF-C85E-4619-93C0-EB38CD9F39EE},8/10/2010 12:09:51 PM,8/10/2010 12:09:51 PM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDbConnection.Open(ConnectionType: System.Data.Odbc.OdbcConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
ConnectionString: uid=sagitta;Dsn=Sagitta;)'.
Diagnostic,LMCVSQL1,LAMUCO\robin.pryor,From_U2,{C05CA7A6-1A6E-45FA-99FC-A8F16113FCDF},{1D70D3CF-C85E-4619-93C0-EB38CD9F39EE},8/10/2010 12:09:51 PM,8/10/2010 12:09:51 PM,0,0x,ExternalRequest_post: 'IDbConnection.Open failed'. The external request has completed.
OnError,LMCVSQL1,LAMUCO\robin.pryor,Load U2 Data Contacts,{839F3385-A68F-49F6-B357-76A5A0FCE65C},{1D70D3CF-C85E-4619-93C0-EB38CD9F39EE},8/10/2010 12:09:51 PM,8/10/2010 12:09:51 PM,-1073450910,0x,System.Runtime.InteropServices.COMException (0x80131937): Exception from HRESULT: 0x80131937
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
OnError,LMCVSQL1,LAMUCO\robin.pryor,Load U2 Data Contacts,{839F3385-A68F-49F6-B357-76A5A0FCE65C},{1D70D3CF-C85E-4619-93C0-EB38CD9F39EE},8/10/2010 12:09:51 PM,8/10/2010 12:09:51 PM,-1073450985,0x,component "ADO NET Source" (1) failed validation and returned error code 0x80131937.
OnProgress,LMCVSQL1,LAMUCO\robin.pryor,Load U2 Data Contacts,{839F3385-A68F-49F6-B357-76A5A0FCE65C},{1D70D3CF-C85E-4619-93C0-EB38CD9F39EE},8/10/2010 12:09:51 PM,8/10/2010 12:09:51 PM,33,0x,Validating
OnError,LMCVSQL1,LAMUCO\robin.pryor,Load U2 Data Contacts,{839F3385-A68F-49F6-B357-76A5A0FCE65C},{1D70D3CF-C85E-4619-93C0-EB38CD9F39EE},8/10/2010 12:09:51 PM,8/10/2010 12:09:51 PM,-1073450996,0x,One or more component failed validation.
OnError,LMCVSQL1,LAMUCO\robin.pryor,Load U2 Data Contacts,{839F3385-A68F-49F6-B357-76A5A0FCE65C},{1D70D3CF-C85E-4619-93C0-EB38CD9F39EE},8/10/2010 12:09:51 PM,8/10/2010 12:09:51 PM,-1073594105,0x,There were errors during task validation.
OnPostValidate,LMCVSQL1,LAMUCO\robin.pryor,Load U2 Data Contacts,{839F3385-A68F-49F6-B357-76A5A0FCE65C},{1D70D3CF-C85E-4619-93C0-EB38CD9F39EE},8/10/2010 12:09:51 PM,8/10/2010 12:09:51 PM,0,0x,(null)
OnPostValidate,LMCVSQL1,LAMUCO\robin.pryor,Sagitta_2_SQL_sync,{E2A2B526-4207-483E-8BAB-54A564FB9889},{1D70D3CF-C85E-4619-93C0-EB38CD9F39EE},8/10/2010 12:09:51 PM,8/10/2010 12:09:51 PM,0,0x,(null)
I have NO idea what any of this means, but I'm starting to think that the Sagitta dsn has issues. So while remoted onto the SQL box, I looked at the system dsn's and opened Sagitta. For one thing, there's nothing in the password field. Of course when I test the connection, it fails. I'll see about fixing that and let you know. Thanks a million for all your help.
August 10, 2010 at 12:15 pm
Running successfully now. Thanks again! BIG TIME!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply