One of the interfaces that we developed for our client had the following requirement.
Send the output file to the target location using Dynamic send port. If Output file is already present at the target location, then send the file to Backup location using a Static send port. If file is already present at backup location, then catch the exception and send the file to error location using Static send port.
We'll cover the technical implementation in the following sections, with each subsequent section (optionally) making use of the previous section’s implementation.
Schemas, MAP and Pipelines
In order to implement this solution, we have used the following schema, Map and Pipelines:
- Schema: EmpData.xsd
- Map: EmpTO Emp.btm
- Receive Pipeline1
- Send Pipeline1
Create one receive and three send ports in the orchestration in the orchestration view.
Here we will cover the creation of Dynamic send port.
2.2 Creating Dynamic Port and Exception Handling
Please see the below images for creating Dynamic send port:
- Create a port type in orchestration view (e.g.
- Add new port in orchestration view and configure the Port using Configuration Wizard:
- Drag an expression shape from the tool bar and write the following code in the expression shape:
DynamicSend(Microsoft.XLANGs.BaseTypes.Address) = @"file://E:TempOUT" + strFilename;
This will create the dynamic port for you after deploying the orchestration.
(Note: Please note that we can read the transport type and Location from Configuration Section. Here I have hard coded transport type and location in the orchestration for simplicity.)
To handle the exception, Set the Property Delivery Notification = Transmitted for each port you have created in orchestration view.
The exception object we need to construct is of type
Now deploy the solution containing schemas, pipeline, orchestration.
Perform the following steps:
- Create one Receive port in BTS explorer (e.g.
DynamicPortExp.Receive with one receive Location.
DynamicPortExp.Receive.File with File as transport type and
e:dataIn as file drop location).
- Create two send ports in BTS explorer (e.g.
DynamicportExp.Send.File with backup location as
DynamicPortExp.Send.Error.file with error location
e:dataError with transport type as File).
- Now drop the file (e.g. Emp1.txt) at Input location after processing through orchestration it reaches to a location specified by dynamic send port, e.g.
- Once again drop the same file at input location as the file is already present at location specified by dynamic send port. It will throw a Delivery failure exception. We are catching this exception in the
catch block of the orchestration. After logging this exception in the event viewer, the file is sent to backup location.
- Again drop the same file at input location. After processing orchestration, try to send the file at location specified by dynamic port. As file is already present at the location, it raises an exception and tries to send the file at backup location but file is also present at backup location. So we are expecting that orchestration will send this file to an error location but it will not send the file to error location and we will see the following warning message in the event viewer and orchestration will not catch the exception.
- In order to catch the exception for static send port, the trick here is to set the retry count(default –3) and retry interval(default –5) property of send port to Zero(0) as shown below.
To find more articles written/posted by me, please visits my blog at http://biztalk-ssis-ssas.blogspot.com.