ForEach Trapping an Error and Continuing

I have a ForEach loop that processes a list of databases. Inside the loop I many steps, one of which is a sequence that contains two steps. Either of these steps may fail (they are attempting to start mirroring and could fail for any number of reasons). I would like to trap this error and ignore it so the For loop will continue, but still fail if other steps than this one fail. The only thing I've been able to do so far is to tell the whole loop to continue through some insane number of errors. Is there a way to identify or actually ignore the error? In the sequence I have have on completion and from the sequence to the next step (which checks if mirroring actually started) is running on completion.

Thanks.

I found a solution using SQL Server.

BEGIN TRY
ALTER DATABASE AdventureWorks SET Partner='http://TEST'
END TRY
BEGIN CATCH
END CATCH

This will prevent the error from being seen by SSIS. But for other errors this will not work (such as SELECT * FROM person.contacts) where contacts does not exist in the adventureworks database (person.contact does).

I'd still be interested in any feedback on ways to selectively trap and ignore errors and get the for loop to continue.

Larry

|||

For selectively ignoring an error, one approach I've used it to modify the MaximumErrorCount to greater than 1 (say 1 billion) on the task (or container) I want to selectively ignore errors on. Then, put an error handler on the task or container which basically sets a variable for fatal errors and use expression based precedence constraints rather than success/failure/completion precedence constraints.

Public Sub Main()

' Don't propagate error message up the chain, is this propagated

Dim errorMessage As String

Dts.Variables("Propagate").Value = False

'inspect error message

errorMessage = CType(Dts.Variables("ErrorDescription").Value, String)

If errorMessage.Contains("really bad error here") Then

Dts.Variables("FatalError").Value = True

End If

Dts.TaskResult = Dts.Results.Success

End Sub

ForEach Loop sequence question

Hi,

This is related to an earlier post.

I have a ForEach loop that contains 3 script tasks in it.

The script tasks are connected by precedence constraints, as in:

script1 --> script2 --> script3

So they should execute in order.

When I run the debugger however, I see that script3 turns green before script2. It is a little disconcerting, however it seems to working correctly. Script3 can't even do what it's supposed to do until script1 and script2 are finished.

But as I've said, it's working. So why does script3 appear to finish before script2 is done?

Thanks

Double click on the precedence constraint between script2 and script3. What are the settings? You don't have any other precedence constraints going into script3 do you? (From any other tasks?)|||

No, there are no other precedence constraints to script3.

The precedence constraints are set to "success".

But script1 and script2 create files that script3 compares. The compare part is working fine, and it shows that it's running the diff on the files created in the previous 2 scripts. It can't do that unless the files exist first.

|||I understand the scenario perfectly.

Script3 can't possibly execute unless 1 and 2 have finished "successfully." That doesn't mean that 1 and 2 did what they were supposed to do though.|||My understanding (subject to correction by someone better informed) is that the IDE changes the colors based on receiving events. Events are not guaranteed to be received in the order that they occur. Of course, I could be completely wrong about this |||

jwelch wrote:

My understanding (subject to correction by someone better informed) is that the IDE changes the colors based on receiving events. Events are not guaranteed to be received in the order that they occur. Of course, I could be completely wrong about this

Something along those lines, K108, did you copy n paste the script tasks?|||I have seen many cases where the coloring of the tasks in BIDS (debug mode) does not behave in a logic order; but later while reviewing the execution progress and results, everything looks OK. This seems to occur more when there is a high number of tasks/rows in the packages. My bottom line: if the results and logs are correct; it is nothing to be concerned about.|||

K108 wrote:

Hi,

This is related to an earlier post.

I have a ForEach loop that contains 3 script tasks in it.

The script tasks are connected by precedence constraints, as in:

script1 --> script2 --> script3

So they should execute in order.

When I run the debugger however, I see that script3 turns green before script2. It is a little disconcerting, however it seems to working correctly. Script3 can't even do what it's supposed to do until script1 and script2 are finished.

But as I've said, it's working. So why does script3 appear to finish before script2 is done?

Thanks

I wouldn't worry about it. The tasks changig colours is based on the receiving of events from the execution engine. There could be any number of reasons - possibly that the reason script2 is not green is because its already executing again on the next iteration. It could be that the UI simply can't keep up with the execution engine. Who knows. Bottom line is (as everyone else has said) its really nothing to worry about.

-Jamie

|||

Thanks for the input.

I won't worry about it, as it working.

sql

ForceExecutionResult is ignored by parent

1 Add a Sequence Container.
2 Add a Script Task to the contianer, and set the script to fail, Dts.TaskResult = Dts.Results.Failure
3 Set the ForceExecutionResult on the Script to Success.
4 Execute the package.

The container fails, so it detects the Dts.Results.Failure, and ignores ForceExecutionResult. The task itself is Green, so that indicates success. I think this is wrong, as ForceExecutionResult should be observed by both the task and parent container. Not that it should matter, I think, FailParentOnFailure is false. By design? (Sep CTP)
Ok, so there is a simpler issue here. The sequence container takes no notice of the ForceExecutionResult. Try a container with a simple task that always succeeds. Set ForceExecutionResult on the container to Failure, and the container still succeeds (goes green).|||

Containers don't fail because their children fail. Containers fail because one of their settings (MaxErrorCount, FailParentOnFailure, etc) mandates it to fail.

The task isn't raising an error. So MaxErrorCount doesn't take effect. A failed task doesn't fail the parent by default. Neither is FailParentOnFailure set to true.

Here, like the Disable/Enable, is a brute force method of making something happen. You've made the conceptual assumption that because tasks fail when they have errors, if the task is forced to look like it failed, the parent should behave the same as a task that failed because it had errors.

The feature does what it says and no more. It forces the return value to false. Nothing more. It effects only those features that key on the return value. Precedence constraints are effected by the execution result, failing the parent is not. Neither is there an error raised.

If you set fail parent or fail package on failure, it will fail the sequence.

If you return an error from the script task, it will fail the parent if MaxErrors is set to 1.

HTH,
K

Force order of XML elements in FOR XML EXPLICIT

Hello,

I need to generate XML that matches an existing XSD. The XSD has the elements in a sequence requiring the XML elements to be in a specific order.

I want to generate XML like the following:

<employee>
<id>1</a>
<name>
<first>Nancy</first>
<last>Davolio</last>
</name>
<title>Sales Representative</title>
</employee>

When I perform my query using FOR XML EXPLICIT, how do I get the name element to be after the id element and before the title element?

Here is an example query (does not work, but illustrates what I would like):

select 1 as tag, null as parent,
EmployeeId as [employee!1!id!element],
null as [name!2!first!element],
null as [name!2!last!element],
Title as [employee!1!title!element]
from employees
where EmployeeId = 1

union all

select 2 as tag, 1 as parent,
EmployeeId as [employee!1!id!element],
FirstName as [name!2!first!element],
LastName as [name!2!last!element],
null as [employee!1!title!element]
from employees
where EmployeeId = 1

order by [employee!1!id!element], tag

for xml explicit

I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries.

Any ideas?

Thanks!

Trev

I have the same problem. Is there a solution?

Travallion said "I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries."

Could someone post an example of how to do it this way please?

Force order of XML elements in FOR XML EXPLICIT

Hello,

I need to generate XML that matches an existing XSD. The XSD has the elements in a sequence requiring the XML elements to be in a specific order.

I want to generate XML like the following:

<employee>
<id>1</a>
<name>
<first>Nancy</first>
<last>Davolio</last>
</name>
<title>Sales Representative</title>
</employee>

When I perform my query using FOR XML EXPLICIT, how do I get the name element to be after the id element and before the title element?

Here is an example query (does not work, but illustrates what I would like):

select 1 as tag, null as parent,
EmployeeId as [employee!1!id!element],
null as [name!2!first!element],
null as [name!2!last!element],
Title as [employee!1!title!element]
from employees
where EmployeeId = 1

union all

select 2 as tag, 1 as parent,
EmployeeId as [employee!1!id!element],
FirstName as [name!2!first!element],
LastName as [name!2!last!element],
null as [employee!1!title!element]
from employees
where EmployeeId = 1

order by [employee!1!id!element], tag

for xml explicit

I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries.

Any ideas?

Thanks!

Trev

I have the same problem. Is there a solution?

Travallion said "I know a possible solution is to create a tag #3 with [id!3] and parent = 1, but this requires an extra query from the employees table. If I have n elements after the name element, it would require n queries."

Could someone post an example of how to do it this way please?

FOR XML EXPLICIT problem

This is an example of the sequence I'm after:
<sold_products>
<base_info>
<type_shipment>N</type_shipment>
<origin>
<city>San Diego</city>
<state>CA</state>
</origin>
<destination>
<city>Portland</city>
<state>ME</state>
</destination>
<salesperson_id>1234</salesperson_id>
<nat_acct_nbr>6789</nat_acct_nbr>
</base_info>
.
.
.
</sold_products>
my T_SQL looks like:
SELECT
1 AS Tag,
NULL As Parent,
Shipment.Order_Type AS [special_products!1!order_type!Element],
NULL AS [base_info!2!type_shipment!Element],
NULL AS [origin!3!city!Element],
NULL AS [origin!3!state!Element],
NULL AS [destination!4!city!Element],
NULL AS [destination!4!state!Element],
NULL AS [base_info!5!salesperson_id!Element],
NULL AS [base_info!5!nat_acct_nbr!Element]
FROM
Shipment
WHERE Shipment.shipment_id = 140915
UNION ALL
SELECT
2,
1,
NULL,
Shipment_Detail.Type_Shipment,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM
Shipment
LEFT JOIN
Shipment_Detail ON Shipment.Shipment_ID = Shipment_Detail.Shipment_ID
WHERE Shipment.shipment_id = 140915
UNION ALL
-- get origin info
UNION ALL
-- get destination info
UNION ALL
-- get origin info
SELECT
5,
1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Shipment_Sales.Salesperson_ID,
Shipment_Sales.nat_acct_nbr
FROM
Shipment
LEFT JOIN
Shipment_Sales ON Shipment.Shipment_ID = Shipment_Sales.Shipment_ID
WHERE Shipment.shipment_id = 140915
My problem is getting the <salesperson_id> and <nat_acct_nbr> elements
within the same
<base_info> element as <type_shipment>. As it is wriiten above and by
setting all base_info tags = 2,
I get:
<base_info>
<type_shipment>N</type_shipment>
<origin>
<city>San Diego</city>
<state>CA</state>
</origin>
<destination>
<city>Portland</city>
<state>ME</state>
</destination>
</base_info>
<base_info>
<salesperson_id>1234</salesperson_id>
<nat_acct_nbr>6789</nat_acct_nbr>
</base_info>
How can I do this?
Thank you in advance for your time.The problem is that each row in your query appears as a separate
base_info tag. Since you get the shipment detail column in the first
row and the salesperson_id and nat_acct_nbr in another row, they appear
in different tags. Thus if you want to include the salesperson_id tag
and nat_acct_nbr tag in the same base_info tag, you need to try this:
SELECT
1 AS Tag,
NULL As Parent,
Shipment.Order_Type AS [special_products!1!order_type!Element],
NULL AS [base_info!2!type_shipment!Element],
NULL AS [origin!3!city!Element],
NULL AS [origin!3!state!Element],
NULL AS [destination!4!city!Element],
NULL AS [destination!4!state!Element],
NULL AS [base_info!2!salesperson_id!Element],
NULL AS [base_info!2!nat_acct_nbr!Element]
FROM
Shipment
WHERE Shipment.shipment_id = 140915
UNION ALL
SELECT
2,
1,
NULL,
Shipment_Detail.Type_Shipment,
NULL,
NULL,
NULL,
NULL,
Shipment_Sales.Salesperson_ID,
Shipment_Sales.nat_acct_nbr
FROM
Shipment
LEFT JOIN
Shipment_Detail ON Shipment.Shipment_ID =
Shipment_Detail.Shipment_ID
LEFT JOIN
Shipment_Sales ON Shipment.Shipment_ID = Shipment_Sales.Shipment_ID
WHERE Shipment.shipment_id = 140915
UNION ALL
-- get origin info
UNION ALL
-- get destination info
UNION ALL
-- get origin info|||Thank you for your response. If I do it this way, salesperson_id and
nat_acct_nbr immediately follow type_shipment in the xml stream instead of
following destination. This is invalid because the schema requires the
sequence to be as it is in my posting. Thanks again.
<kniemczak@.gmail.com> wrote in message
news:1133388828.086137.136010@.g43g2000cwa.googlegroups.com...
> The problem is that each row in your query appears as a separate
> base_info tag. Since you get the shipment detail column in the first
> row and the salesperson_id and nat_acct_nbr in another row, they appear
> in different tags. Thus if you want to include the salesperson_id tag
> and nat_acct_nbr tag in the same base_info tag, you need to try this:
> SELECT
> 1 AS Tag,
> NULL As Parent,
> Shipment.Order_Type AS [special_products!1!order_type!Element],
> NULL AS [base_info!2!type_shipment!Element],
> NULL AS [origin!3!city!Element],
> NULL AS [origin!3!state!Element],
> NULL AS [destination!4!city!Element],
> NULL AS [destination!4!state!Element],
> NULL AS [base_info!2!salesperson_id!Element],
> NULL AS [base_info!2!nat_acct_nbr!Element]
> FROM
> Shipment
> WHERE Shipment.shipment_id = 140915
>
> UNION ALL
>
> SELECT
> 2,
> 1,
> NULL,
> Shipment_Detail.Type_Shipment,
> NULL,
> NULL,
> NULL,
> NULL,
> Shipment_Sales.Salesperson_ID,
> Shipment_Sales.nat_acct_nbr
> FROM
> Shipment
> LEFT JOIN
> Shipment_Detail ON Shipment.Shipment_ID =
> Shipment_Detail.Shipment_ID
> LEFT JOIN
> Shipment_Sales ON Shipment.Shipment_ID = Shipment_Sales.Shipment_ID
> WHERE Shipment.shipment_id = 140915
>
> UNION ALL
>
> -- get origin info
>
> UNION ALL
>
> -- get destination info
>
> UNION ALL
>
> -- get origin info
>|||Ok this reply will be short, but if you want me to elaborate further,
just ask. Try using the shipment ID to group the XML tags. Use your
original query, but add a hidden value for shipment id so that tags 2
and 5 both are associated with shipment id X.|||Also, if you have SQL Server 2005, you may want to use FOR XML PATH instead.
Best regards
Michael
<kniemczak@.gmail.com> wrote in message
news:1133500921.199025.127140@.g43g2000cwa.googlegroups.com...
> Ok this reply will be short, but if you want me to elaborate further,
> just ask. Try using the shipment ID to group the XML tags. Use your
> original query, but add a hidden value for shipment id so that tags 2
> and 5 both are associated with shipment id X.
>

FOR XML EXPLICIT problem

This is an example of the sequence I'm after:
<sold_products>
<base_info>
<type_shipment>N</type_shipment>
<origin>
<city>San Diego</city>
<state>CA</state>
</origin>
<destination>
<city>Portland</city>
<state>ME</state>
</destination>
<salesperson_id>1234</salesperson_id>
<nat_acct_nbr>6789</nat_acct_nbr>
</base_info>
..
..
..
</sold_products>
my T_SQL looks like:
SELECT
1 AS Tag,
NULL As Parent,
Shipment.Order_Type AS [special_products!1!order_type!Element],
NULL AS [base_info!2!type_shipment!Element],
NULL AS [origin!3!city!Element],
NULL AS [origin!3!state!Element],
NULL AS [destination!4!city!Element],
NULL AS [destination!4!state!Element],
NULL AS [base_info!5!salesperson_id!Element],
NULL AS [base_info!5!nat_acct_nbr!Element]
FROM
Shipment
WHERE Shipment.shipment_id = 140915
UNION ALL
SELECT
2,
1,
NULL,
Shipment_Detail.Type_Shipment,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM
Shipment
LEFT JOIN
Shipment_Detail ON Shipment.Shipment_ID = Shipment_Detail.Shipment_ID
WHERE Shipment.shipment_id = 140915
UNION ALL
-- get origin info
UNION ALL
-- get destination info
UNION ALL
-- get origin info
SELECT
5,
1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Shipment_Sales.Salesperson_ID,
Shipment_Sales.nat_acct_nbr
FROM
Shipment
LEFT JOIN
Shipment_Sales ON Shipment.Shipment_ID = Shipment_Sales.Shipment_ID
WHERE Shipment.shipment_id = 140915
My problem is getting the <salesperson_id> and <nat_acct_nbr> elements
within the same
<base_info> element as <type_shipment>. As it is wriiten above and by
setting all base_info tags = 2,
I get:
<base_info>
<type_shipment>N</type_shipment>
<origin>
<city>San Diego</city>
<state>CA</state>
</origin>
<destination>
<city>Portland</city>
<state>ME</state>
</destination>
</base_info>
<base_info>
<salesperson_id>1234</salesperson_id>
<nat_acct_nbr>6789</nat_acct_nbr>
</base_info>
How can I do this?
Thank you in advance for your time.
The problem is that each row in your query appears as a separate
base_info tag. Since you get the shipment detail column in the first
row and the salesperson_id and nat_acct_nbr in another row, they appear
in different tags. Thus if you want to include the salesperson_id tag
and nat_acct_nbr tag in the same base_info tag, you need to try this:
SELECT
1 AS Tag,
NULL As Parent,
Shipment.Order_Type AS [special_products!1!order_type!Element],
NULL AS [base_info!2!type_shipment!Element],
NULL AS [origin!3!city!Element],
NULL AS [origin!3!state!Element],
NULL AS [destination!4!city!Element],
NULL AS [destination!4!state!Element],
NULL AS [base_info!2!salesperson_id!Element],
NULL AS [base_info!2!nat_acct_nbr!Element]
FROM
Shipment
WHERE Shipment.shipment_id = 140915
UNION ALL
SELECT
2,
1,
NULL,
Shipment_Detail.Type_Shipment,
NULL,
NULL,
NULL,
NULL,
Shipment_Sales.Salesperson_ID,
Shipment_Sales.nat_acct_nbr
FROM
Shipment
LEFT JOIN
Shipment_Detail ON Shipment.Shipment_ID =
Shipment_Detail.Shipment_ID
LEFT JOIN
Shipment_Sales ON Shipment.Shipment_ID = Shipment_Sales.Shipment_ID
WHERE Shipment.shipment_id = 140915
UNION ALL
-- get origin info
UNION ALL
-- get destination info
UNION ALL
-- get origin info
|||Thank you for your response. If I do it this way, salesperson_id and
nat_acct_nbr immediately follow type_shipment in the xml stream instead of
following destination. This is invalid because the schema requires the
sequence to be as it is in my posting. Thanks again.
<kniemczak@.gmail.com> wrote in message
news:1133388828.086137.136010@.g43g2000cwa.googlegr oups.com...
> The problem is that each row in your query appears as a separate
> base_info tag. Since you get the shipment detail column in the first
> row and the salesperson_id and nat_acct_nbr in another row, they appear
> in different tags. Thus if you want to include the salesperson_id tag
> and nat_acct_nbr tag in the same base_info tag, you need to try this:
> SELECT
> 1 AS Tag,
> NULL As Parent,
> Shipment.Order_Type AS [special_products!1!order_type!Element],
> NULL AS [base_info!2!type_shipment!Element],
> NULL AS [origin!3!city!Element],
> NULL AS [origin!3!state!Element],
> NULL AS [destination!4!city!Element],
> NULL AS [destination!4!state!Element],
> NULL AS [base_info!2!salesperson_id!Element],
> NULL AS [base_info!2!nat_acct_nbr!Element]
> FROM
> Shipment
> WHERE Shipment.shipment_id = 140915
>
> UNION ALL
>
> SELECT
> 2,
> 1,
> NULL,
> Shipment_Detail.Type_Shipment,
> NULL,
> NULL,
> NULL,
> NULL,
> Shipment_Sales.Salesperson_ID,
> Shipment_Sales.nat_acct_nbr
> FROM
> Shipment
> LEFT JOIN
> Shipment_Detail ON Shipment.Shipment_ID =
> Shipment_Detail.Shipment_ID
> LEFT JOIN
> Shipment_Sales ON Shipment.Shipment_ID = Shipment_Sales.Shipment_ID
> WHERE Shipment.shipment_id = 140915
>
> UNION ALL
>
> -- get origin info
>
> UNION ALL
>
> -- get destination info
>
> UNION ALL
>
> -- get origin info
>
|||Ok this reply will be short, but if you want me to elaborate further,
just ask. Try using the shipment ID to group the XML tags. Use your
original query, but add a hidden value for shipment id so that tags 2
and 5 both are associated with shipment id X.
|||Also, if you have SQL Server 2005, you may want to use FOR XML PATH instead.
Best regards
Michael
<kniemczak@.gmail.com> wrote in message
news:1133500921.199025.127140@.g43g2000cwa.googlegr oups.com...
> Ok this reply will be short, but if you want me to elaborate further,
> just ask. Try using the shipment ID to group the XML tags. Use your
> original query, but add a hidden value for shipment id so that tags 2
> and 5 both are associated with shipment id X.
>

For each Loop Container do nothing inside

Hi,

A very strange thing happened to me. I have a package that includes two For each loop containers. Each container has script tasks, sequence containers, etc. Both are Foreach ADO Enumerator basis. It works without any problems until I changed the position of one of them in the Control flow and added some code in the script task. After these changes I executed the package and both of For each loop containers did not execute the tasks inside of them, any task. However the execution color on the containers was green (success). How can it be?

Your help is much welcome. Thanks in Advance.

Jo?o Cruz

Not enought nformation to tell really, but likely related to the code in the script task. Without knowing what the package looks like, what precedence contraints you have etc., it's really tough to know.

More information please...

|||

I have found the solution . Thanks very much for your time.