Workflow + Multiple Transform Data Tasks 1

[BuGlen]

I have a package that is used to update tables on a Report server from a Production server and then creates a report from the data on the Report server. The steps are as follows:

1. Execute SQL Task - Clear local tables
2. Transform Data Tasks (3) - Update 3 local tables with data from remote server tables.
3. ActiveX Script Task - Update Excel based report using VBScript.
4. Email the report to the AppAdmin.

The problem with this workflow is that step 2 actually contains 3 tasks. When I try to set workflow from step 2 to step 3 (ActiveX script), it creates 3 workflow tasks. This tells me that the script will be run after each of the transformations (if successful) in step 2.

I need to find a way in the workflow that will wait until all 3 transformation tasks are successful and then run a single instance of the ActiveX script.

Can anyone help?

- Glen

 

[john76]

The way I handle this sort of workflow constraint is to put the (in your case) 3 transform tasks into a separate package and put an execute package task in your main workflow. Then have your ActiveX task run on completion of the called package.

One thing to note is if you need to set your precedence constraint for the ActiveX task to On Success or On Failure, the called package will always report successful execution even if one of your tranform tasks fails UNLESS you either set the called package's properties to fail the package on the first task failure, or set one or more of the tranform task steps to fail the package on step failure.

Hope this helps,
John

 

[BuGlen]

AHA! Nested packages is probably the best solution. Thank you John, I appreciate the speedy response to my post.

- Glen