foreach loop?

I need to execute about dozen packages from another package... how do I dynamically pass the dozen package names to the package and execute using foreach loop...?

idea is to store the names of packages in a text file and set the file connection property reading each package names from the text file... in this way I can just configure/edit the text file from time to time, the packages and the units that I want to execute...

Someone please provide me steps to make it work.

Thanks in adv.

You need to load the contents of the file into an ADO Recordset using a data-flow. You can then shred that recordset using the ForEach loop. This example demonstrates the same - the only differrence being that the ADO recordset is populated using an Execute SQL Task rather than a data-flow. The shredding is exactly the same though: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

-Jamie

|||

Thanks Jamie.... it worked wonderfully!

|||I need a sql Query to loop through a column in one table reading the ID of tenants, the result being a list of the Tenants names from another table with the same tenantID's. The captured data needs to be filled into textboxes on a form.sql

foreach loop?

I need to execute about dozen packages from another package... how do I dynamically pass the dozen package names to the package and execute using foreach loop...?

idea is to store the names of packages in a text file and set the file connection property reading each package names from the text file... in this way I can just configure/edit the text file from time to time, the packages and the units that I want to execute...

Someone please provide me steps to make it work.

Thanks in adv.

You need to load the contents of the file into an ADO Recordset using a data-flow. You can then shred that recordset using the ForEach loop. This example demonstrates the same - the only differrence being that the ADO recordset is populated using an Execute SQL Task rather than a data-flow. The shredding is exactly the same though: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

-Jamie

|||

Thanks Jamie.... it worked wonderfully!

|||I need a sql Query to loop through a column in one table reading the ID of tenants, the result being a list of the Tenants names from another table with the same tenantID's. The captured data needs to be filled into textboxes on a form.

Fore Color Expressions

Hi all, I can't seem to find an example anywhere on the net or BOL on this one. I assume you can set the color of a calculated members dynamically in the Fore Color Expression property but I can't find any code samples for that. Can someone provide me a simply one for changing the color to red if a condition is met?

I really appreciate it!

Brian

Hi Brian,

Here's a recent query for the AS 2000 Foodmart Sales cube - you should see some cells highlighted in red when this is run in the MDX Sample App:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/3a1eccc9d291c961

>>

With Member [Measures].[testmetric] AS
'[Measures].[Store Sales Net]-[Measures].[Store Sales]',

FORE_COLOR ='iif([Time].currentMember.Level is [Time].[Quarter] And
([Time].CurrentMember.PrevMember,[Measures].[testmetric])
- CalculationPassValue( [Measures].[testmetric], -1,Relative )

> Abs(([Time].CurrentMember.PrevMember,Measures.[testmetric])) * 0.025,

rgb(255,0,0), rgb(0,0,0))',
FORMAT_STRING = 'Currency'

SELECT
{[Measures].[Store Sales], [Measures].[Store Sales Net],
Measures.[testmetric]} ON COLUMNS ,
CrossJoin({[Product].[Product Family].[Drink],
[Product].[Product Family].[Food],
[Product].[Product Family].[Non-Consumable]},
{{[Time].[Year].[1997].CHILDREN}}) ON ROWS

FROM [Sales] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

>>

|||

Thanks, so it sounds like I'd want to do something like this at runtime in the OLAP client application and not in the SQL Server 2005 Color Expressions of a Calculated Member?

Brian

|||

You can do this using Calculations tab of Sql Server Busyness Intelligence Studio too. When you press the button on the right side of a color expression textbox (there are 2 of those – for foreground and background color) there will be a color chooser dialog box to pick the desired color. When you have selected the color its code will be inserted in the text box. You can subsequently pick another color and it will add it to the text box again. Thus the contents of the text box will have 2 color codes with comments.

Now you can modify the contents of the textbox to have an expression using the codes you obtained. Something like:

iif([measures].currentmember > 0, 4227327 /*R=255, G=128, B=64*/, 8388608 /*Navy*/)

If you like editing MDX scripts in the plain text format then you might find convenient usage of the debugger as a helper to write the script. When you are on the calculations tab you can hit F5 and the tab will load the debugger. In the text box for the script you can type your MDX statements and click toolbar buttons to select colors and fonts. The codes will be inserted at the cursor of the script text box. You can also drag the templates of MDX statements from the tree views on the left side of the debugger.

Having constructed your statement, you can execute it (F10) and immediately test with the pivot table or MDX query you like (there are 4 tabs for custom queries). Pressing F10 subsequently will step over till the end and restart from the beginning of the script. Breakpoints also help.

Once you are done you can select Debug | Stop menu command and you will be offered to keep your script or discard. Thus you can use the debugger to try something and throw away (MDX prototyping).

|||Thanks Andrew, that worked like a champ.sql

FOR XML EXPLICIT issue

Hi guys:
I'm in an urgent need to know if we can dynamically alter values in
querying tables using FOR XML EXPLICIT. In the following sample, I have
3 tabels involved vis_Rule, vis_IF, and vis_AND.
Table Structure:
Table: vis_Rule
Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
Table: vis_IF
Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
Table: vis_AND
Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++
In the following stored proc, can we grab the RuleId of the first
SELECT statement and pass it to the next SELECT statement such that it
extracts only the desired rows? In other words, can we dynamically
assign values from one part of the SELECT statement to the next?
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id],
NULL as [compare!4!id],
NULL as [compare!4!operator]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL,
NULL,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
if.IFId,
[and].AndId,
NULL,
NULL
FROM vis_AND [and], vis_IF if
where [and].IFId = if.IFId
FOR XML EXPLICIT
GO
Thanks in advance.My apologies for the late reply, but I was on vacation the last couple of
ws.
I am not quite sure what you try to acheive. Do you want to only provide the
nesting of the tree for a given ruleID?
In that case try:
CREATE Table vis_Rule (RuleId int,Name nvarchar(40), Priority int, Active
bit)
go
insert into vis_Rule values (1, N'r1', 1, 1)
insert into vis_Rule values (2, N'r2', 2, 1)
go
CREATE Table vis_IF (IFId int, RuleId int --> Foreign Key to RuleId in
vis_Rule
)
go
insert into vis_IF values (1, 1)
insert into vis_IF values (2, 1)
insert into vis_IF values (3, 1)
insert into vis_IF values (4, 2)
go
CREATE Table vis_AND (AndId int, IFId int --> Foreign Key to IFId in vis_IF
)
insert into vis_AND values (1, 1)
insert into vis_AND values (2, 1)
insert into vis_AND values (3, 2)
insert into vis_AND values (4, 2)
insert into vis_AND values (5, 3)
insert into vis_AND values (6, 4)
go
declare @.rid int
set @.rid = 1
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
where r.RuleId = @.rid
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = @.rid
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
[if].RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if]
where [and].IFId = [if].IFId
AND [if].RuleId = @.rid
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
If you want it for all rules, try:
SELECT 1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = [if].RuleId
AND [and].IFId = [if].IFId
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
Best regards
Michael
<shamod@.gmail.com> wrote in message
news:1122527565.227837.240190@.g43g2000cwa.googlegroups.com...
> Hi guys:
> I'm in an urgent need to know if we can dynamically alter values in
> querying tables using FOR XML EXPLICIT. In the following sample, I have
> 3 tabels involved vis_Rule, vis_IF, and vis_AND.
> Table Structure:
> Table: vis_Rule
> Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
> Table: vis_IF
> Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
> Table: vis_AND
> Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
> ++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++
> In the following stored proc, can we grab the RuleId of the first
> SELECT statement and pass it to the next SELECT statement such that it
> extracts only the desired rows? In other words, can we dynamically
> assign values from one part of the SELECT statement to the next?
> SELECT TOP 1
> 1 AS Tag,
> NULL AS Parent,
> r.RuleId as [rule!1!Id],
> r.Name as [rule!1!name],
> r.Priority as [rule!1!priority],
> r.Active as [rule!1!active],
> NULL as [if!2!id],
> NULL as [and!3!id],
> NULL as [compare!4!id],
> NULL as [compare!4!operator]
> FROM vis_Rule r
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> r.RuleId,
> NULL,
> NULL,
> NULL,
> [if].IFId,
> NULL,
> NULL,
> NULL
> FROM vis_IF [if], vis_Rule r
> WHERE [if].RuleId = r.RuleId
> UNION ALL
> SELECT 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> if.IFId,
> [and].AndId,
> NULL,
> NULL
> FROM vis_AND [and], vis_IF if
> where [and].IFId = if.IFId
> FOR XML EXPLICIT
> GO
> Thanks in advance.
>

FOR XML EXPLICIT issue

Hi guys:
I'm in an urgent need to know if we can dynamically alter values in
querying tables using FOR XML EXPLICIT. In the following sample, I have
3 tabels involved vis_Rule, vis_IF, and vis_AND.
Table Structure:
Table: vis_Rule
Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
Table: vis_IF
Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
Table: vis_AND
Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++
In the following stored proc, can we grab the RuleId of the first
SELECT statement and pass it to the next SELECT statement such that it
extracts only the desired rows? In other words, can we dynamically
assign values from one part of the SELECT statement to the next?
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id],
NULL as [compare!4!id],
NULL as [compare!4!operator]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL,
NULL,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
if.IFId,
[and].AndId,
NULL,
NULL
FROM vis_AND [and], vis_IF if
where [and].IFId = if.IFId
FOR XML EXPLICIT
GO
Thanks in advance.
My apologies for the late reply, but I was on vacation the last couple of
weeks.
I am not quite sure what you try to acheive. Do you want to only provide the
nesting of the tree for a given ruleID?
In that case try:
CREATE Table vis_Rule (RuleId int,Name nvarchar(40), Priority int, Active
bit)
go
insert into vis_Rule values (1, N'r1', 1, 1)
insert into vis_Rule values (2, N'r2', 2, 1)
go
CREATE Table vis_IF (IFId int, RuleId int --> Foreign Key to RuleId in
vis_Rule
)
go
insert into vis_IF values (1, 1)
insert into vis_IF values (2, 1)
insert into vis_IF values (3, 1)
insert into vis_IF values (4, 2)
go
CREATE Table vis_AND (AndId int, IFId int --> Foreign Key to IFId in vis_IF
)
insert into vis_AND values (1, 1)
insert into vis_AND values (2, 1)
insert into vis_AND values (3, 2)
insert into vis_AND values (4, 2)
insert into vis_AND values (5, 3)
insert into vis_AND values (6, 4)
go
declare @.rid int
set @.rid = 1
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
where r.RuleId = @.rid
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = @.rid
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
[if].RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if]
where [and].IFId = [if].IFId
AND [if].RuleId = @.rid
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
If you want it for all rules, try:
SELECT 1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = [if].RuleId
AND [and].IFId = [if].IFId
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
Best regards
Michael
<shamod@.gmail.com> wrote in message
news:1122527565.227837.240190@.g43g2000cwa.googlegr oups.com...
> Hi guys:
> I'm in an urgent need to know if we can dynamically alter values in
> querying tables using FOR XML EXPLICIT. In the following sample, I have
> 3 tabels involved vis_Rule, vis_IF, and vis_AND.
> Table Structure:
> Table: vis_Rule
> Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
> Table: vis_IF
> Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
> Table: vis_AND
> Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
> ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++
> In the following stored proc, can we grab the RuleId of the first
> SELECT statement and pass it to the next SELECT statement such that it
> extracts only the desired rows? In other words, can we dynamically
> assign values from one part of the SELECT statement to the next?
> SELECT TOP 1
> 1 AS Tag,
> NULL AS Parent,
> r.RuleId as [rule!1!Id],
> r.Name as [rule!1!name],
> r.Priority as [rule!1!priority],
> r.Active as [rule!1!active],
> NULL as [if!2!id],
> NULL as [and!3!id],
> NULL as [compare!4!id],
> NULL as [compare!4!operator]
> FROM vis_Rule r
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> r.RuleId,
> NULL,
> NULL,
> NULL,
> [if].IFId,
> NULL,
> NULL,
> NULL
> FROM vis_IF [if], vis_Rule r
> WHERE [if].RuleId = r.RuleId
> UNION ALL
> SELECT 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> if.IFId,
> [and].AndId,
> NULL,
> NULL
> FROM vis_AND [and], vis_IF if
> where [and].IFId = if.IFId
> FOR XML EXPLICIT
> GO
> Thanks in advance.
>

FOR XML EXPLICIT doesnt seem to work for me--HELP NEEDED ASAP!!

Hi,
Am trying to produce XML output using "FOR XML EXPLICIT" using a dynamically created table. Say ur using the Northwind DB, Customers table. Is there a way i can have each column displayed in a different row such that the attribute is column name and the text element is the column value e.g.

<Customers>
<Customer>
<Field fieldname="ContactName">Alfreds Futterkiste</Field>
<Field fieldname="ContactTitle">Sales Representative</Field>
</Customer>
<Customer>
...

</Customer>
</Customers>

Basically that's the format i need for the output for whichever customer(s) get retrieved from the table. All methods i've thought of havent worked upto now so in case someone has an idea please feel free to share the code. Thanx in adv!

My apologies for the delay in answering....

If you know the names of the fields you could do it. But not if you don't. Are you using SQL Server 2000 or 2005? In 2005 the query formulation becomes quite a bit simpler using FOR XML PATH...

Here is the EXPLICIT solution:

select 1 as tag, NULL as parent
, 1 as "Customers!1!!hide"
, NULL as "Customer!2!!hide"
, NULL as "Field!3!fieldname", NULL as "Field!3!"

union all
select 2 , 1
, 1
, CustomerID
, NULL, NULL
from Customers

union all
select 3 , 2
, 1
, CustomerID
, 'ContactName', ContactName
from Customers

union all
select 3 , 2
, 1
, CustomerID
, 'ContactTitle', ContactTitle
from Customers

-- more for other fields
order by "Customers!1!!hide", "Customer!2!!hide"
for xml explicit

and here the 2005 FOR XML PATH one (note that the '' is needed to break it into two elements):

select 'ContactName' as "Field/@.fieldname", ContactName as "Field", ''
, 'ContactTitle' as "Field/@.fieldname", ContactTitle as "Field"
from Customers
for xml path('Customer'), ROOT('Customers')

Best regards
Michael