|
|
You're in the
sql server dts
group:Scheduled SSIS jobs now fail after installaing SQL Server 2005 SP2
sql server dts:
development instance both running on the same server. On installing SP2, the jobs now fail with the error below. The packages referenced by the jobs are executed successfully using Visual Studio or the Management Studio. Executed as user: CAMPDEN\SQLService. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 09:59:04 Error: 2007-04-19 09:59:06.01 Code: 0x00000000 Source: SERVER11_LIVE_SERVER11_DEV_Transfer Objects Task Description: Source SQL Server instance was not specified. End Error Error: 2007-04-19 09:59:06.04 Code: 0xC0024107 Source: SERVER11_LIVE_SERVER11_DEV_Transfer Objects Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 09:59:04 Finished: 09:59:06 Elapsed: 1.094 seconds. The package execution failed. The step failed. Any ideas on resolving the problem would be appreciated. Thanks
Thanks for replying. The packages are also stored as files so I can execute
from Visual Studio. I deleted the existing packages and exported these file based packages to the development instance of SQL Server and tried executing the job again. Got the same error which is when I decided to post it on this forum. I assume this is what you mean by re-deploy the package. I have tried re-creating the package and the job using the 'Copy Database' task and I am still getting the same error. The error message says "Source SQL Server instance was not specified", but I dont get this error when I manually execute the package direct from the Integration Services server using Management Studio. The file based packages do not report an error either when manually executed using Visual Studio. This make me think the package or the environment in which it runs is not the problem, and that the job scheduler is the problem. Perhaps it is not reading the source server instance correctly from the package or something similar. This error has been occurring since SP2 was installed. Let me know if you need more information. -- Ade [quoted text, click to view] "Wei Lu [MSFT]" wrote:
> Hello Ade, > > Could you please redeploy the SSIS package into the server and run the job > again? > > Also, please let me know the detailed steps you did in your package and > which step raise the error. > > Sincerely, > > Wei Lu > Microsoft Online Community Support > > ================================================== > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Hello Ade,
Could you please redeploy the SSIS package into the server and run the job again? Also, please let me know the detailed steps you did in your package and which step raise the error. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Hello Ade,
I would like to suggest you first check whether your schedule job is refer the correct SSIS instance on your server. And, please check the SQL Agent or Proxy you use to run the SSIS package. When I say redeploy the package, I mean you deleted the existing package in the SSIS and then use the Visual Studio to generate the deployment Manifest and redeploy to the SSIS. Since you get the same error in the Visual Studio, please check the connections to the Source SQL Server. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Wei,
Yes the job is referring to the correct SSIS instance. I selected it again from the "Select SSIS package" browse window. I do NOT get an error when the packages are executed from Visual Studio or Management Studio so the connections are OK. All the packages were deleted from the SQL Server; the file-based versions were executed successfully in Visual Studio and then exported to the SQL Server. The SQL Agent service runs as a user with administrator privileges. The error message returned by the job scheduler says the "Source SQL Server instance was not specified". I only started getting this error after installing SP2. I think the job scheduler is not reading the source server parameter correctly from the package, perhaps leaving out the instance name i.e. \\Server11\Live is read as \\SERVER11. I may be wrong but I think this is a bug in SP2. -- Ade [quoted text, click to view] "Wei Lu [MSFT]" wrote:
> Hello Ade, > > I would like to suggest you first check whether your schedule job is refer > the correct SSIS instance on your server. > > And, please check the SQL Agent or Proxy you use to run the SSIS package. > > When I say redeploy the package, I mean you deleted the existing package in > the SSIS and then use the Visual Studio to generate the deployment Manifest > and redeploy to the SSIS. > > Since you get the same error in the Visual Studio, please check the > connections to the Source SQL Server. > > Sincerely, > > Wei Lu > Microsoft Online Community Support > > ================================================== > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Hi Wei,
Job and step log as follows: Date 23/04/07 09:21:33 Log Job History (COPY_THERMCAL_FROM_LIVE_TO_DEV) Step ID 0 Server SERVER11\DEV Job Name COPY_THERMCAL_FROM_LIVE_TO_DEV Step Name (Job outcome) Duration 00:00:03 Sql Severity 0 Sql Message ID 0 Operator Emailed Software Developers Operator Net sent Operator Paged Retries Attempted 0 Message The job failed. The Job was invoked by User CAMPDEN\ade. The last step to run was step 1 (COPY_THERMCAL_FROM_LIVE_TO_DEV_Step). Date 23/04/07 09:21:34 Log Job History (COPY_THERMCAL_FROM_LIVE_TO_DEV) Step ID 1 Server SERVER11\DEV Job Name COPY_THERMCAL_FROM_LIVE_TO_DEV Step Name COPY_THERMCAL_FROM_LIVE_TO_DEV_Step Duration 00:00:02 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: CAMPDEN\SQLService. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 09:21:34 Error: 2007-04-23 09:21:36.09 Code: 0x00000000 Source: SERVER11_LIVE_SERVER11_DEV_Transfer Objects Task Description: Source SQL Server instance was not specified. End Error Error: 2007-04-23 09:21:36.10 Code: 0xC0024107 Source: SERVER11_LIVE_SERVER11_DEV_Transfer Objects Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 09:21:34 Finished: 09:21:36 Elapsed: 1.703 seconds. The package execution failed. The step failed. -- Ade [quoted text, click to view] "Wei Lu [MSFT]" wrote:
> Hello Ade, > > I would like to get the job history log. > > You could get the log from the management studio. > > Sincerely, > > Wei Lu > Microsoft Online Community Support > > ================================================== > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Hello Ade,
I would like to get the job history log. You could get the log from the management studio. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
What you are asking me to do is create the package from scratch. This takes
too long and I have not got the time to do it. The existing package was created using the Copy Database task. I have just tried the whole process of creating the package and job using the Copy Database task wizard and I get the same error. This worked before installing SP2 as I created many of these jobs and ran them regularly. However, if I run the package created by the copy database task directly, it works. Surely something is wrong with executing the package from a scheduled job. -- Ade [quoted text, click to view] "Wei Lu [MSFT]" wrote:
> Hello Ade, > > In the Management Studio, you could generate the deploy package file and > deploy it to the SQL Server. > > Deploying Integration Services Packages > http://msdn2.microsoft.com/en-us/library/ms137592.aspx > > Please redeploy the package and then use the correct package in the SQL > Agent to have a try. > > Sincerely, > > Wei Lu > Microsoft Online Community Support > > ================================================== > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Hello Ade,
In the Management Studio, you could generate the deploy package file and deploy it to the SQL Server. Deploying Integration Services Packages http://msdn2.microsoft.com/en-us/library/ms137592.aspx Please redeploy the package and then use the correct package in the SQL Agent to have a try. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Hello Wei,
I have got to the bottom of the problem. The problem lies with the Copy Database wizard. Although SP2 was installed on the Server, I did not realise that it would have to be installed my PC which has got the Client tools installed. So installing SP2 on my PC resolved the problem. Thanks very much for your help. -- Ade [quoted text, click to view] "Wei Lu [MSFT]" wrote:
> Hello Ade, > > I am not asking you to recreate the package but just redeploy it to the SQL > Server, not to the file system. > > Could you send me the SSIS package so that I may try to reproduce this > issue? > > Also, the job you are running is useful. You could send it to my email > address. To get my real email, please remove the ONLINE in my display email > address. > > Sincerely, > > Wei Lu > Microsoft Online Community Support > > ================================================== > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Hello Ade,
I am not asking you to recreate the package but just redeploy it to the SQL Server, not to the file system. Could you send me the SSIS package so that I may try to reproduce this issue? Also, the job you are running is useful. You could send it to my email address. To get my real email, please remove the ONLINE in my display email address. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Hello Ade,
Thanks for the update. If you have any question, please feel free to let me know Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |