ForEachLoop Container and Variables

Hi Guys

I am trying to do the following and am quite new to SSIS.

I have to select a dataset from a database on server A, check if it exists on server B and perform an Update or Insert dependant on the existence.

I have created a SQL task to do the Select from server A with the results set passed to a variable of Vendors. I have added a ForEach Loop container with an enumerator of Foreach ADO Enumerator and the source variable is set to Vendors.

I have created 2 variables in the Foreach Loop called Code and Supplier - both as strings - as there are 2 fields from the initial Select that need to be passed to the final Update/ Insert.

I have then created another SQL task insert the Foreach which will perform the Update/Insert.

obviously when I run it at the moment it performs the Update/ Insert but just adds the rows with both Code and Supplier as NULL.

having looked at a couple of examples in books I have i know i need to add something in the Expressions of the Update/Insert SQL task but it is here i get a bit lost.

Which of the properties from the drop down do i need to use to map the variables against?

Any help would be massively appreciated asI am tearing my hair out!

Thanks

Scott

Hi Scott,

We're all still learning SSIS.

It sounds like you're most of the way there.

There are a couple ways to approach this solution. The simplest way, from what I understand from your post, is to use placeholders and parameters in your Update/Insert statements. If you already have the Code and Supplier variables defined, you could perform an insert using an Execute SQL Task with something similar to the following code:

Code Snippet

INSERT INTO Vendors

(Code, Supplier)

VALUES(?, ?)

You could then supply Parameters:

Code Snippet

VariableName Direction DataType ParameterName ParameterSize

User::Code Input Int 0 -1

User::Supplier Input VarChar 1 -1

This would substitute the question marks in the SQL Statement property with the values contained in your variables.

Hope this helps,

Andy

|||

Scott,

Any special reason for not using a dataflow with a lookup transform to detect if the rows exists(update) or not (insert). That is by far a pretty common practice in these scenarios.

|||

Hi Rafael

Still new to this (and database stuff as a whole) and am going on someone elses advice!

I have looked at your suggestion and have got as far as the following:

OLEDB Source with a SQL select statement to return the data required

Look Up transform to look up the 2 columns from the Select against the destination table

After that I am a bit lost. I guess i have to add a OLEDB destination but do I do it to a table or a SQL Command?

thanks again

Scott

|||

I think you are on the right track. I would add an OLE DB Destination against the destination table.

Keep in mind you have to tweak the lookup to 'redirect' errors. Lookup will treat the no matches as errors; hence will be send to the error output of the component (red arrow). Then you have to connect the error output of the Lup to the input of the destination.

Now the updates; every row going to the green output of the L.up is an existing/to-updated row. Here you have 2 options; use an OLE DB Commnad to update the row in the destination table; or send those rows to an estiging table (yes a seconf OLE DB Destination) and then back in control flow use an Execute SQl task to do a 1 time update. The advantage of the second method is performance. the Update runs 1 time updating all the required rows. The First one will perform an update for every row passing trhough; wich depending on the volume of data can be performance killer; the good thing is that you don't need a second table.

This thread has some examples

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1

Forcing Specific Distinct Columns....

Hi Guys,

I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...

Is their a way to force 1 column to always be unique?

Heres the query:

SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
CUSTOMER.customerForename AS 'Customer Forename',
CUSTOMER.customerSurname AS 'Customer Surname'
FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
WHERE ITEM.ItemID = ORDER_ITEM.ItemID
AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
AND ORDER_T.CustomerID = CUSTOMER.CustomerID
GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
CUSTOMER.customerForename, CUSTOMER.customerSurname
ORDER BY Total_Purchased DESC

The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.

Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:

ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage

Which is kinda annoying... is there anyway i can prevent this?

And also apart from the Where Joins... is there a more efficient way of writing this?

thx for reading :-)

--PhilkillsDoes no one know any way around this?|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.

on the contrary, i left out the data on purpose as it was irrelevent... i was only trying to point out an example of whats wrong... in the "EXAMPLE"
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
The parts in bold should only appear once.... and the data it should select should be Mathew smith who purchased 30 of that item... 36 is the total amount sold, what i need the query to do... is to only display the person who bought the highest amount of the item that is part of the top 5 best selling items.

another "EXAMPLE":

ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................30......... ......................Jim
2.........20.................20......... ......................Jam
3.........10.................10......... ......................Flim
4.........5..................5......... .......................Flam
5.........2..................2......... .......................Stam

In this case... only 1 person purchased the items in the top 5...

However, if 5 people purchased the same item... the query would return:

ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................5.......... .....................Jim
1.........30.................5.......... .....................Jam
1.........30.................5.......... .....................Flim
1.........30.................5.......... .....................Flam
1.........30.................10......... .....................Stam

Notice how it returned the TOP 1 Item instead of the TOP 5 items?

The data is correct... the query is wrong... which is why i asked if i could put a constraint on the data returned saying that ItemID MUST be unique... unless ofcourse there is a better way to do it ;p

I hope this more accurately describes the problem i am having. :-)

--Philkills|||You are making this more confusing than it should be.

Lets break it down into parts.

Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||You are making this more confusing than it should be.

Lets break it down into parts.

Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

yep that code gives the top 5 items ^^|||...and now you want the top customer for each of those items?|||...and now you want the top customer for each of those items?

correct...|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

Theres no table called CUSTOMER_ITEM... However, i have edited it to return the top customerID correctly.

SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ITEM
INNER JOIN ORDER_ITEMOrdItem2 ON ITEM.ItemID = OrdItem2.ItemID
INNER JOIN ORDER_T ON OrdItem2.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC,
ORDER.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM_0510096.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||ok, so i can display the customer ID... now how do i display the rest of the customer info ... ^_^|||Am i assuming that no one knows the answer? :confused:

lol, kinda funny a problem that even professionals can't figure out :p|||lol, kinda funny a problem that even professionals can't figure out :poh stop

yes, there are a lot of professionals here

what makes you think they want to be your personal free development center?

ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person

good luck, phil

:)|||oh stop

yes, there are a lot of professionals here

what makes you think they want to be your personal free development center?

ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person

good luck, phil

:)

Eh?, i asked 1 question in this thread... and simply gave more info... in my other posts...

If your refering to my other questions that i've asked around the forum... well, they are pretty much unrelated to this question. I have done a lot of research on SQL queries and the only questions i've asked are related to areas that i have found very difficult to find an answer to.

So as a last resort i turned to this support forum, now i realise your doing this out of the kindness of your heart (i think) and for that im grateful. I too help people, but my area of expertise is with C++/Other languages, i on the other hand will help people regardless of the complexity of the problem, infact the more complex the better :p keeps things interesting.

In regards to my original point about no one being able to solve the problem... well, that was based on the posts inside this thread:

OK. I lied. This is complicated, but we are almost there...

He admitted himself that this was complicated, still being new to SQL i do not yet know all of its limits... but when a proffessional admits that something is complicated, i would say its a pretty good indication that it actually is complicated...

When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.

So perhaps next time, before jumping in and making assumptions... you should consider all the facts and why i might have said such words.|||When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.
I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.|||I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.

I.... was not intending to be rude
YOU.... Misinterpreted my point.. obviously thinking i was taking a dig at you or your skill when i was not...
I.... Appreciate all your help so far
YOU.... Probably won't even read this :p

It's up to you whether you help me or not i suppose... but just for the record... the only reason i may have seemed slightly impatient was due to the ever approaching deadline :confused:|||as the old saying goes, "failure to plan on your part does not constitute an emergency on our part"

i'm sorry about your homework assignment coming due real soon, but that's life

if you would like to start a new thread, we can close this one and let it fade into the archives...|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.

And the fact that now it turns out that this was some sort of homework assignment irks me even more.|||Not to bump a thread that seems to have everyone up in arms, but...

I like to try to solve these "puzzles", just to see if I can.

I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.

Using my own data, I came up with this:

SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC

This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.

And the fact that now it turns out that this was some sort of homework assignment irks me even more.

again suprise suprise you jump to conclusions that you think are right... i was NOT implying anything... if something can't be done... it can't be done... thats why i said what i did...

Not to bump a thread that seems to have everyone up in arms, but...

I like to try to solve these "puzzles", just to see if I can.

I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.

Using my own data, I came up with this:

Code:
SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC
This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549

thank you very much mate for putting in the effort to solve this conundrum
:-). I appreciate it i really do ^^|||Am i assuming that no one knows the answer? :confused:
lol, kinda funny a problem that even professionals can't figure out :p
i on the other hand will help people regardless of the complexity of the problem,
When he never replied after that post... i just assumed that he gave up and admitted defeat...
YOU.... Probably won't even read this
You were acting like a complete twit. The fact that you seem oblivious to this I will chalk up to immaturity. Hopefully, you will soon grow up enough to be respectful to people who are volunteering their time and expertise to help you out.|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.

I intended the top 5 in the outer query to pull the top 5 customers, while
the top 5 in the nested query is pulling just the top 5 products.sql

FOR XML Query

Hi Guys,
I am trying to understand the FOR XML EXPLICIT option and what I don't
understand is , how is the XML being generated , for example,
select 1 AS TAG,
NULL as PARENT,
NULL as [Customers!1!!element],
NULL as [Customer!2!!element]
from Customers
select 2 AS TAG,
1 as PARENT,
NULL as [Customers!1!!element],
NULL as [Customer!2!!element]
from Customers
both the above queries on Northwind returns 92 rows - When I union them a
(EVEN IF I DON'T SPECIFY FOR XML EXPLICIT) it returns only 2 rows !!! How
does this happen ? What's the trick here ?
Thanks!
You only have 2 distinct rows specified:
1 null null null
2 1 null null
if you select some data from the tables by specifying some column names you
will get more rows in the result. UNION eliminates duplicates so you're
down to only two rows.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"pSm" <pSm@.discussions.microsoft.com> wrote in message
news:697805DC-78EA-42E3-B970-97D7F86A2211@.microsoft.com...
> Hi Guys,
> I am trying to understand the FOR XML EXPLICIT option and what I
> don't
> understand is , how is the XML being generated , for example,
> ----
> select 1 AS TAG,
> NULL as PARENT,
> NULL as [Customers!1!!element],
> NULL as [Customer!2!!element]
> from Customers
> ----
> select 2 AS TAG,
> 1 as PARENT,
> NULL as [Customers!1!!element],
> NULL as [Customer!2!!element]
> from Customers
> both the above queries on Northwind returns 92 rows - When I union them a
> (EVEN IF I DON'T SPECIFY FOR XML EXPLICIT) it returns only 2 rows !!! How
> does this happen ? What's the trick here ?
> Thanks!
>
|||Also, note that for FOR XML, you should use UNION ALL that does not
eliminate duplicates.
There was an old article that Michael Conning and I write back in 99 that
explains the explicit mode (you should find it with a websearch). Or both
Books Online and the SQL Server 2005 whitepaper on MSDN about FOR XML should
be able to help.
HTH
Michael
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uGJCbTh9EHA.3416@.TK2MSFTNGP09.phx.gbl...
> You only have 2 distinct rows specified:
> 1 null null null
> 2 1 null null
> if you select some data from the tables by specifying some column names
> you will get more rows in the result. UNION eliminates duplicates so
> you're down to only two rows.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "pSm" <pSm@.discussions.microsoft.com> wrote in message
> news:697805DC-78EA-42E3-B970-97D7F86A2211@.microsoft.com...
>

FOR XML Query

Hi Guys,
I am trying to understand the FOR XML EXPLICIT option and what I don't
understand is , how is the XML being generated , for example,
----
select 1 AS TAG,
NULL as PARENT,
NULL as [Customers!1!!element],
NULL as [Customer!2!!element]
from Customers
----
select 2 AS TAG,
1 as PARENT,
NULL as [Customers!1!!element],
NULL as [Customer!2!!element]
from Customers
---
both the above queries on Northwind returns 92 rows - When I union them a
(EVEN IF I DON'T SPECIFY FOR XML EXPLICIT) it returns only 2 rows !!! How
does this happen ? What's the trick here ?
Thanks!You only have 2 distinct rows specified:
1 null null null
2 1 null null
if you select some data from the tables by specifying some column names you
will get more rows in the result. UNION eliminates duplicates so you're
down to only two rows.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"pSm" <pSm@.discussions.microsoft.com> wrote in message
news:697805DC-78EA-42E3-B970-97D7F86A2211@.microsoft.com...
> Hi Guys,
> I am trying to understand the FOR XML EXPLICIT option and what I
> don't
> understand is , how is the XML being generated , for example,
> ----
> select 1 AS TAG,
> NULL as PARENT,
> NULL as [Customers!1!!element],
> NULL as [Customer!2!!element]
> from Customers
> ----
> select 2 AS TAG,
> 1 as PARENT,
> NULL as [Customers!1!!element],
> NULL as [Customer!2!!element]
> from Customers
> ---
> both the above queries on Northwind returns 92 rows - When I union them a
> (EVEN IF I DON'T SPECIFY FOR XML EXPLICIT) it returns only 2 rows !!! How
> does this happen ? What's the trick here ?
> Thanks!
>|||Also, note that for FOR XML, you should use UNION ALL that does not
eliminate duplicates.
There was an old article that Michael Conning and I write back in 99 that
explains the explicit mode (you should find it with a websearch). Or both
Books Online and the SQL Server 2005 whitepaper on MSDN about FOR XML should
be able to help.
HTH
Michael
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uGJCbTh9EHA.3416@.TK2MSFTNGP09.phx.gbl...
> You only have 2 distinct rows specified:
> 1 null null null
> 2 1 null null
> if you select some data from the tables by specifying some column names
> you will get more rows in the result. UNION eliminates duplicates so
> you're down to only two rows.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "pSm" <pSm@.discussions.microsoft.com> wrote in message
> news:697805DC-78EA-42E3-B970-97D7F86A2211@.microsoft.com...
>

FOR EACH Loop to export files based on Header Record

Hey guys,,

Well im new at this SSIS stuff and i have something that i am trying to do, but cannot get it to work.. Ill try to explain, and if anyone can help me or point me in the right direction it would be much appriciated..

I have 2 tables, one header table. and one lines table. This is a one - to -many relationship.. ie 1 header, many lines.. This is a Order Header, and Order Lines table setup.. Order header has Order numbers and and email address field that link to the lines table by order number. I also have a view which links all this info together.

I would like to export a excel file (preferable named from the order number column - but that can come later) for each order number in the header table. The excel file will contain the details from the View that was created. I want this to loop through all the header records in the header table and create a excel file for each one..

Down the track i want to add a send mail task to this and pass the email address to a variable so i can use it in the send mail task.. But ill get the main part working first..

Anyhelp would be more than helpful.. I tried to set this up, but i am stuck on the enumerator part..

thanks again, scotty

I just created my first blog entry with something for you; let me know if it helps.

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

Thanks Rafael.. This is exactly what i wanted to do and you have described the process exactly

Much appriciated..

Merry xmas and have a happpy new year..

Thaks

Scotty

For all MS SQL Gurus :( please advice

Hi guys,
we has accidently restored our Database with a back up file created months ago. we have no back up for the huge data and Stored procedures ( I know it is very stupid :( and i m sad abt it all ), guys is there any way by which i can get restore my db back to what it was before restoration.
the flks in team have also deleted(shift + delete) the transaction log file ( I know it is very stupid again :( and i m very sad abt it all )..
please advice
Thanx in advance
ashish

There are a few ways of doing this ...
1) Go to your off-drive backups (tape, CDs, etc) and restore the latest version
2) Restore the Stored Procedures from your SCM, but deal with the data
3) Use a time machine to go back in time and prevent that from ever happening.
If all three do not work, then as you guessed, you are screwed. Let it be an expensive lesson in regular backups and SCM for your code. And investing in a time machine.

|||If this database is critical, immediately cease all activity on that server until you decide what you are going to do.

Personally I have no experience at all with such utilities, but you might consider something like this:

Recovery for SQL Server

And here's a review for the product at SQL Server Central (it's a freeregistration to read the article; great site, I recommend registering)

Review - MSSQLRecovery