June 23, 2010 at 1:23 am
Hello guys, my first post here so please go easy on me.
I'm currently migrating our SQL2000 DTS packages over to our new SQL2005 server. As the migration wizard is a bit hit and miss, I'm rebuilding the packages from scratch (a good exercise in any case).
I'm having a problem with the execution of some of my SSIS packages which I cannot get to the bottom of.
If my package includes a Data Flow Task, then the job seems to fail when I attempt to run it under SQL server agent. I can run other jobs that are just simple SQL tasks.
Also, the package I am having problems with executes just fine from my computer in Visual Studio, just not when I attempt to execute it as a scheduled job under sql agent. I've checked permissions and everything seems ok.
Any ideas?
- - - - - - - -
UPDATE:
Here's the error from when I attempt to run this as a job under SQL server agent:
Message
Executed as user: DS\zz_SQL_DHSX. ror: 2010-06-23 08:32:32.04 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 08:32:30 Finished: 08:32:32 Elapsed: 1.61 seconds. The package execution failed. The step failed.
June 23, 2010 at 1:37 am
At which sort of step are your packages failing?
Using any authentication within these specific packages?
As what are you running these packages under the agent?
Error messages?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 23, 2010 at 1:41 am
Henrico Bekker (6/23/2010)
At which sort of step are your packages failing?Using any authentication within these specific packages?
As what are you running these packages under the agent?
Error messages?
Hi Henrico.
The step appears to be failing at the Data Flow Task.
The dataflow used OLE DB Source and Destination and uses Windows Authentication for the destination and sql authentication for the source which is on another server.
The job is being run as the zz_sql_dhsx account which is a member of the sysadmin role.
June 23, 2010 at 1:44 am
The job is being run as the zz_sql_dhsx account which is a member of the sysadmin role.
on both sides? (source & destination?)
You still didn't provide any sort of error messages or codes.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 23, 2010 at 1:48 am
Henrico Bekker (6/23/2010)
The job is being run as the zz_sql_dhsx account which is a member of the sysadmin role.
on both sides? (source & destination?)
You still didn't provide any sort of error messages or codes.
on both sides? not quite sure what you mean there. The job is run under sysadmin role and the source authentication is a reader sql account and the destination is windows auth, so runs under the sysadmin user running the job.
Here's the error from when I attempt to run this as a job under SQL server agent:
Message
Executed as user: DS\zz_SQL_DHSX. ror: 2010-06-23 08:32:32.04 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 08:32:30 Finished: 08:32:32 Elapsed: 1.61 seconds. The package execution failed. The step failed.
June 23, 2010 at 1:51 am
now I'm getting this error:
Message
Executed as user: DS\zz_SQL_DHSX. ...ion 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 08:50:25 Error: 2010-06-23 08:50:26.40 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2010-06-23 08:50:27.40 Code: 0xC0202009 Source: CMDB_data_import Connection manager "CMDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'cmdbuser'.". End Error Error: 2010-06-23 08:50:27.40 Code: 0xC020801C Source: Data Flow Task OLE DB Source... The package execution fa... The step failed.
June 24, 2010 at 1:56 am
Check whether u have given the correct credentials..
June 25, 2010 at 12:59 am
Problem resolved.
I found that whoever installed SQL on the server had failed to install Intgration Services.
Once Integration Services was installed, I was able to successfully create the package with encrypted password and everything worked fine.
So, it was half a permission problem and half due to an issue with the installation.
Thanks for your help guys.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply