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

FOR XML EXPLICIT formatting issue

I am trying to use FOR XML EXPLICIT to group records that are related.
I want to achieve something like
<Header Id="1" ... >
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
</Header>
<Header Id="2" ... >
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
</Header>
<Header Id="3" ... >
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
</Header>
I keep getting
<Header Id="1" ... >
<Header Id="2" ... />
<Header Id="3" ... />
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
The query looks something like
SELECT
1 AS Tag
, NULL AS Parent
, header AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, ChildId AS 'Child1!2!Child1Id'
, Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, ChildId AS 'Child2!3!Child1Id'
, Name AS 'Child2!3!Name'
FROM
blah, blah, blah
Any help with either of these is gratefully appreciated.
Let me know if I am on the wrong path also, as I wouldn't be surprised
Thanks
Steve
You're missing an ORDER BY. Try this
create table #Header(HeaderId int)
insert into #Header(HeaderId) values(1)
insert into #Header(HeaderId) values(2)
insert into #Header(HeaderId) values(3)
create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
SELECT 1 AS Tag
, NULL AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
UNION ALL
SELECT 2 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, c.Child1Id AS 'Child1!2!Child1Id'
, c.Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
UNION ALL
SELECT 3 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, c.Child2Id AS 'Child2!3!Child1Id'
, c.Name AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
ORDER BY h.HeaderId,Tag
FOR XML EXPLICIT
drop table #Child2
drop table #Child1
drop table #Header
|||"markc600@.hotmail.com" wrote:

> You're missing an ORDER BY. Try this
>
> create table #Header(HeaderId int)
> insert into #Header(HeaderId) values(1)
> insert into #Header(HeaderId) values(2)
> insert into #Header(HeaderId) values(3)
> create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
> create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
> insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
> SELECT 1 AS Tag
> , NULL AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> UNION ALL
> SELECT 2 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , c.Child1Id AS 'Child1!2!Child1Id'
> , c.Name AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
> UNION ALL
> SELECT 3 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , c.Child2Id AS 'Child2!3!Child1Id'
> , c.Name AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
> ORDER BY h.HeaderId,Tag
> FOR XML EXPLICIT
>
> drop table #Child2
> drop table #Child1
> drop table #Header
>
Excellent, so close yet so far.
Also, in my actual code I had not propogated the Header Id into the other
parts of the union, it din't actually know what the relationships were.
And now BizTalk seems to like it.
Thanks

FOR XML EXPLICIT formatting issue

I am trying to use FOR XML EXPLICIT to group records that are related.
I want to achieve something like
<Header Id="1" ... >
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
</Header>
<Header Id="2" ... >
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
</Header>
<Header Id="3" ... >
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
</Header>
I keep getting
<Header Id="1" ... >
<Header Id="2" ... />
<Header Id="3" ... />
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
The query looks something like
SELECT
1 AS Tag
, NULL AS Parent
, header AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, ChildId AS 'Child1!2!Child1Id'
, Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, ChildId AS 'Child2!3!Child1Id'
, Name AS 'Child2!3!Name'
FROM
blah, blah, blah
Any help with either of these is gratefully appreciated.
Let me know if I am on the wrong path also, as I wouldn't be surprised
Thanks
SteveYou're missing an ORDER BY. Try this
create table #Header(HeaderId int)
insert into #Header(HeaderId) values(1)
insert into #Header(HeaderId) values(2)
insert into #Header(HeaderId) values(3)
create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
SELECT 1 AS Tag
, NULL AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
UNION ALL
SELECT 2 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, c.Child1Id AS 'Child1!2!Child1Id'
, c.Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
UNION ALL
SELECT 3 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, c.Child2Id AS 'Child2!3!Child1Id'
, c.Name AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
ORDER BY h.HeaderId,Tag
FOR XML EXPLICIT
drop table #Child2
drop table #Child1
drop table #Header|||
"markc600@.hotmail.com" wrote:

> You're missing an ORDER BY. Try this
>
> create table #Header(HeaderId int)
> insert into #Header(HeaderId) values(1)
> insert into #Header(HeaderId) values(2)
> insert into #Header(HeaderId) values(3)
> create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
> create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
> insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
> SELECT 1 AS Tag
> , NULL AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> UNION ALL
> SELECT 2 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , c.Child1Id AS 'Child1!2!Child1Id'
> , c.Name AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
> UNION ALL
> SELECT 3 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , c.Child2Id AS 'Child2!3!Child1Id'
> , c.Name AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
> ORDER BY h.HeaderId,Tag
> FOR XML EXPLICIT
>
> drop table #Child2
> drop table #Child1
> drop table #Header
>
Excellent, so close yet so far.
Also, in my actual code I had not propogated the Header Id into the other
parts of the union, it din't actually know what the relationships were.
And now BizTalk seems to like it.
Thanks

For each record in a table

Hi I have two table related to each other, but not bound trough an id. I
know that there are better ways to do this relation but I am not in a
position that I can change the design of the database.
My tables are as below
Table name myCategory
id description
1 work
2 private
3 hobby
4 protected
Table name myItems
id Category
1 work, private
2 private, hobby, protected
3 work, protected
4 private, hobby
For each description in the table myCategory I want to select the items in
the table myItems where the description appear and union the results in a
list sorted by the description.
TIRislaaTor Inge Rislaa (tor.ingenospam@.rislaa.no) writes:
> Hi I have two table related to each other, but not bound trough an id. I
> know that there are better ways to do this relation but I am not in a
> position that I can change the design of the database.
> My tables are as below
> Table name myCategory
> id description
> 1 work
> 2 private
> 3 hobby
> 4 protected
> Table name myItems
> id Category
> 1 work, private
> 2 private, hobby, protected
> 3 work, protected
> 4 private, hobby
> For each description in the table myCategory I want to select the items in
> the table myItems where the description appear and union the results in a
> list sorted by the description.
I'm not sure that I understand the desired results, but what about :
SELECT c.id, c.description, i.Category
FROM myCategory c
JOIN myItems i ON charindex(c.description, i.Category) > 0
ORDER BY c.description
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
2) Please learn what First Normal is, why columns are not like fields,
what a relational key is, etc. What you have here is a 1950's file
system written in bad SQL.
3) Look up "relational division" -- it is one of Dr. Codd's original
basic operations.|||Exactly the result I was looking for, Thanks
TIRislaa
"Erland Sommarskog" <esquel@.sommarskog.se> skrev i melding
news:Xns9726CDF3A7BYazorman@.127.0.0.1...
> Tor Inge Rislaa (tor.ingenospam@.rislaa.no) writes:
> I'm not sure that I understand the desired results, but what about :
> SELECT c.id, c.description, i.Category
> FROM myCategory c
> JOIN myItems i ON charindex(c.description, i.Category) > 0
> ORDER BY c.description
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

For Each across list of databases

I need to get records from multiple databases.

In my main database, I have a list of databases related to seperate business units.

For each of those databases, I need to get a list of values from a table (the table exists in each database).

Basically

foreach database in a list
Do a Lookup
end

Possible?

The option I would consider would be to put a Data Flow Task in the PreExecute Event Handler of the For Each Loop Task to loop through the databases you need to access.

First, parameterize the Connection String of your source main Data Flow DB source (not the Event Handler DB source) with a variable. Here's an example of what I'm doing within an expression for the ConnectionString:

"Data Source=" + @.[User::masterServer] + ";Initial Catalog=" + @.[User::masterDatabase] + ";Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

Create a PreExecute Event Handler Data Flow Task in which you select the database names from sysdatabases with a OLE DB source (not the same source you're using in the main Handler Data Flow) connected to the server and write it to a RecordSet Destination. You'll have to define a new variable of type Object as the RS destination.

Then, specify an ADO Enumerator as the Enumerator of your For Each Loop and map the database name to the variable that you're using to parameterize the Connection String of your non-Event Handler from the RecordSet variable.

|||Thanks!

That looks like exactly what I'd like to do.

There are several details in your response that I'm not sure about. But I'm going to try to work them out on my own before asking for more help.|||

Dear mr_superlove,

I have been looking to use For Each Across a list of database. Your reply seems very helpful..

I am new to SSIS. I tried to do what you said. I am missing something to get it working. Can you elaborate the procedure you wrote in step by step way.

I would really appreciate you effort.

Thank you,

Sanjeev