Hi there. I'm trying to convert hundreds of DTS packages over to SSIS,
some of which are rather complex and have several components which wont
migrate nicely. I'd like to use the Execute DTS 2000 Package Task for
many of the packages - but the problem I'm encountering is that no
matter what happens within the DTS 2000 package, the outer package step
reports success. Does anyone know of some way to make the Execute DTS
2000 Package task in SSIS fail if any of the steps within the original
DTS package fail? Ideally of course none of the steps within the old
DTS 2000 package would fail, but this being the real world I know many
of them do fail and frequently at that - so I'm nervous to move any of
these jobs into production on SQL 2005 since I'd never know when
something had failed.

Any advice is greatly appreciated...

thanks,

Wil

Hello Wil,

Does the DTS 2000 package actually fail? If it doesn't raise the event to
the calling container (EXECUTE 2000 PACKAGE TASK) then the container will
succeed.

Here is what I did to test

Created a 2000 package that simply had an ExecuteSQL task with no statement
inside.
I plugged this in to the Execute 2000 Package Task.



When I ran the SSIS package I go this

Error: 0x0 at Execute DTS 2000 Package Task: System.Runtime.InteropServices.COMException
(0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Ex ec80PackageTask.ExecuteThread()


Allan

Fascinating... I think I figured out the issue, and it might even be a
bug (or just a confusing quagmire). While stabbing at various
solutions in the dark I edited the DTS 2000 package I was using for
testing to log to the named instance of the local SQL 2005 server (it
had logging turned on already but was pointing to <local>). That
appears to make the failures show properly. To attempt to test my
theory I then turned off logging on the internal package altogether -
assuming that would go back to not showing the failure - but actually
it still worked even with logging on the internal package disabled. So
I created a new package completely (just to be safe) - and sure enough
if the internal package has its logging enabled but set to <local> -
you won't get the failure notification from SSIS. You can either
disable logging on the internal package (which seems odd, but it works)
or you can switch the logging over to using the named instance of the
SQL 2005 server. Feel free to test it out yourself and let me know if
you also come up with the same results - but I've done it a few times
now and it definitely seems to repeat itself. You can even toggle
between <local> and the named instance back and forth on the same
package (though the <local> wont verify, it will still let you keep it)
and it goes back and forth between reporting the main package failure
vs success.

Thanks,

Wil