Wednesday, October 31, 2012

MSDTC on server 'Server' is unavailable.

Just a quick post on an error that I got today...
 
 
Msg 8501, Level 16, State 3, Line 3
MSDTC on server 'ODCLG29' is unavailable.
 
 
That means the "Distributed Transaction Coordinator" service is not running.
 
ways to get to it...
 
Type "services.msc" in the run or search command.
or go to Control Panel --> administrative tools ---> Services
 
 
look for Distributed Transaction Coordinator, you may double click to start it or right click and select start.
 
 
You may further read about it here...
 
 

Wednesday, October 10, 2012

SSIS Foreach Loop Container continue or error

A recent question about how to skip a failure inside a Foreach Loop Container , I'm sharing the answer as it's easy and apparently not everybody is aware of it.


So let's assume it's only a simple Foreach Loop Container  that's looping over a files on a network location or even the local disk, getting the file names into a variable and passing it to a file system task to move these files over to another location.

The first step will be by creating an OnError event handler for the file system task, and what I mean by that is


just click on Event Handlers from the top and then click on the BLUE link in the event handlers and that's it :)



 Then look under system variables in the variables window, find the "Propagate" variable and set it to false.


So now The Foreach Loop Container is not aware that the file system task has failed therefore does not cause the container to fail and exit.

OPTIONAL--you can use the failure Precedence Constraint "red branch" to do any other processing, such as to send a notification email with the name of the failed image!!

Hope that helps someone!