Multi-step SSIS Job Hangs

I have two SSIS packages that I want to run in one SQL Agent job as two individual steps. The two packages run fine when they are in separate jobs. However, when I run the job conaining both SSIS packages (under the same proxy), the first SSIS package starts, but hangs in the middle.

I then tried setting the DelayValidation flag to True as suggested for a similar issue in another thread from this forum. After changing the DelayValidation flag to True for all containers and tasks on the second SSIS package, the first SSIS package ran completely through sucessfully, but the job continued executing for hours and the second SSIS package never started. I finally killed the job.

Any ideas as to what is the problem here? I have logged to the event viewer and see that the first package completes sucessfully. They run successfully in separate jobs, but I can not get them to run together within the same job without hanging.

Any help is appreciated,

Paulette

What task is the package hanging on?

Do the packages share any external resources? Perhaps there is resource locking occurring.

-Jamie

|||

Thanks Jamie. Well, I know Step 1 (the first SSIS package) finished based on an event viewer message 'Package "ExtractPkg" finished Successfully.', but that was the last event to be logged. No event was logged stating that the second package started. It seems that the job is hanging while trying to start the second package, so I cannot see a task that either package is hanging on.

As far as shared resources... The packages do not share a "Data Source" in the solution, however they do have individual connections that point to the same SQL server and database.

Also, they both share audit and error handler "child" packages that they call from their event handlers. DelayValidation for the Audit and Error handler packages is currently set to false. Could the sharing of these audit and error handler packages be causing a problem? We planned on removing the audit and error handler packages from future versions since we've discovered the automatic logging provided in SSIS is sufficient for our needs.

- Paulette

|||

My first avenue of attack would be to disable/remove the eventhandlers to see if the problem goes away. That sounds like it could be the problem.

By the way, executing a package from each eventhandler is a huge overhead as I have talked about here: http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1100.aspx

-Jamie

|||

Thank you Jamie! When I removed the event handlers the job ran perfectly!

- Paulette

Multi-Server Jobs

Why is it that when viewing Multi-Server Jobs on the MSX Server via Management Studio (Server\SQL Agent\Jobs\Multi-Server Jobs), only jobs that are in the default [Uncategorized (Multi-Server)] category appear? Any other Multi-Server job that has been put into a different category is displayed under the Local Jobs folder.

A master server distributes jobs to, and receives events from, target servers. A master server also stores the central copy of job definitions for jobs that are run on target servers. Target servers connect periodically to the master server to update their schedule of jobs. If a new job exists on the master server, the target server downloads the job. After the target server completes the job, it reconnects to the master server and reports the status of the job.

So in this case if you haven't specified the job category then they are shown as default.

|||That still doesn't answer my question! All I was questioning was why when a job has been categorised in any other Multi-Server job category than [Uncategorized (Multi-Server)] it doesn't show under the Multi-Server jobs folder, (as it does in Enterprise Manager).|||Having dug around a little further it appears that this has already been raised as a bug and should be in SP2.|||

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=139988

This will be addressed in the next service pack.

sql

Multiserver Administration Master Server (MSX)

Hi
I am trying to delete some jobs on a SQL Server 2000
database that I have taken over and I am getting the
following error : Error 14274 - Cannot delete a job that
originated from an MSX server.
We only have one server in our organisation and I have
checked and it is not set up as an MSX server.
Please could someone tell me how I can delete these jobs.
Many thanksIt sounds like the server has been renamed at some point. You need to update
sysjobs to reflect the current server name in the originating_server column.
You can use the following procedure to do this
http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Philip" <anonymous@.discussions.microsoft.com> wrote in message
news:07c001c3d423$352cfaf0$a301280a@.phx.gbl...
> Hi
> I am trying to delete some jobs on a SQL Server 2000
> database that I have taken over and I am getting the
> following error : Error 14274 - Cannot delete a job that
> originated from an MSX server.
> We only have one server in our organisation and I have
> checked and it is not set up as an MSX server.
> Please could someone tell me how I can delete these jobs.
> Many thanks
>sql

Multiserver administratie on SQL2005

Hello,
does anyone has managed to get MultiServer jobs working on SQL2005.
I have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP
server and DNS server. The two others are SQL2005, using the same
service account that has full administrative rights. I log on also as
administrator when I try to make one of the SQL2005 the master server.
During enlisting of the target server on the master server I get the
error that the target server could not logon on the master server
(altough its service account has full admin rights on the master server)
Any suggestions
Thanks a lot in advance
Marc MertensMarc
Do you mean to run a job on the source server that does the work on the
destination? Linked servers?
"Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hello,
> does anyone has managed to get MultiServer jobs working on SQL2005. I
> have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP server
> and DNS server. The two others are SQL2005, using the same service account
> that has full administrative rights. I log on also as administrator when I
> try to make one of the SQL2005 the master server. During enlisting of the
> target server on the master server I get the error that the target server
> could not logon on the master server (altough its service account has full
> admin rights on the master server)
> Any suggestions
> Thanks a lot in advance
> Marc Mertens|||No, MultiServer administration means that you create a multserver job on
a master server that is downloaded on target servers and executed there.
You enable multiserver administration by right clicking on the SQL
Server agent and choosing the correct menu entry in All Tasks. This is a
feature already available on a SQL2000 (where it worked without a
problem), but it does not seems to work anymore in SQL2005 (at least I
can not get it working).
Marc
Uri Dimant wrote:
> Marc
> Do you mean to run a job on the source server that does the work on the
> destination? Linked servers?
>
> "Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
> news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
>

Multiserver administratie on SQL2005

Hello,
does anyone has managed to get MultiServer jobs working on SQL2005.
I have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP
server and DNS server. The two others are SQL2005, using the same
service account that has full administrative rights. I log on also as
administrator when I try to make one of the SQL2005 the master server.
During enlisting of the target server on the master server I get the
error that the target server could not logon on the master server
(altough its service account has full admin rights on the master server)
Any suggestions
Thanks a lot in advance
Marc MertensMarc
Do you mean to run a job on the source server that does the work on the
destination? Linked servers?
"Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hello,
> does anyone has managed to get MultiServer jobs working on SQL2005. I
> have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP server
> and DNS server. The two others are SQL2005, using the same service account
> that has full administrative rights. I log on also as administrator when I
> try to make one of the SQL2005 the master server. During enlisting of the
> target server on the master server I get the error that the target server
> could not logon on the master server (altough its service account has full
> admin rights on the master server)
> Any suggestions
> Thanks a lot in advance
> Marc Mertens|||No, MultiServer administration means that you create a multserver job on
a master server that is downloaded on target servers and executed there.
You enable multiserver administration by right clicking on the SQL
Server agent and choosing the correct menu entry in All Tasks. This is a
feature already available on a SQL2000 (where it worked without a
problem), but it does not seems to work anymore in SQL2005 (at least I
can not get it working).
Marc
Uri Dimant wrote:
> Marc
> Do you mean to run a job on the source server that does the work on the
> destination? Linked servers?
>
> "Marc Mertens" <mertens.techdata@.gmail.com> wrote in message
> news:upCqi06ZGHA.4580@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> does anyone has managed to get MultiServer jobs working on SQL2005. I
>> have installed 3 fresh win2003 servers with SP1. One is a DC, DHCP server
>> and DNS server. The two others are SQL2005, using the same service account
>> that has full administrative rights. I log on also as administrator when I
>> try to make one of the SQL2005 the master server. During enlisting of the
>> target server on the master server I get the error that the target server
>> could not logon on the master server (altough its service account has full
>> admin rights on the master server)
>> Any suggestions
>> Thanks a lot in advance
>> Marc Mertens
>

multiprocessor environment

Folks,
We're running Microsoft SQL-2000 server
in a multiprocessor environment. (quad processor box)
Can we force different jobs that are
scheduled concurrently to use a particular
processor.
What we would like to do is run 4 separate
jobs, scheduled for the same time, and force
each job to use a different processor on a
quad processor database server host.
Is it possible to do this at all with SQL-2000.

Thanks in advanceI think you can only configure which processors will be used by SQL Server. The server itself will assign every thread that runs to a processor based on the priority.sql