FullText Ranking: foreach

Showing posts with label foreach. Show all posts

Thursday, March 29, 2012

ForEachLoop task not behaving as expected

Hi,

I have a ForEach Loop that has 3 script tasks in it.

I have them set up so that they execute in order, such as:

script1 > script2 > script3

script1 creates a file

script2 creates a file

script3 compares the files using a diff command

Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.

The error is "The system cannot find the file specified".

Thanks

Do you have the tasks hooked together by precedence constraints? (The green arrows?)|||

Yes,

I think the problem is something else with my bat file.

Never mind

:-)

Thanks

ForEachLoop Container - How to Force Next Iteration -

How can I force a Next Iteration in a ForEach Loop container?

I am looping through a folder(ForEach Loop Container) looking for a specific File Name ( Child 'Script Task') to evaluate name).

If the current file is not the File Name I need, get the next file, other wise drop down to a Exec Proc task.

Is it possible to force "Next Interation' on the parent container?

Thanks - Covi

Not quite sure what you mean. In what circumstances do you want to 'force teh next iteration'?

-Jamie

sql

ForeachFile and ForeachItem

Since I installed SP1, I do not see ForeachFile and ForEachItem anymore as possible collections in the Foreach package, What went wrong? Thanks for any advice.

You may have to make sure there are selected under the SSIS Control flow items. Right Click on Control Flow task tab - select choose item... - select SSIS Control flow tab - you should see the foreachfile and foreachitem controls - make sure they are selected.

Carl

|||

thanks for your answer but I did check that

In the list, I only see the ForEach loop item, and that I checked.

It's when I use that control flow item that I do not get the choice for ForEachFile and ForeachItem in the Collections page.

|||

Still could not solve this (annoying) problem (need ForEach File)

Can somebody please help?

|||It sounds like your installation has been corrupted. You have the For and ForEach Shapes in the toolbox, but when you drag the ForEach shape over you are missing 2 of the values in the drop-down for the type of enumerator. Is this correct? If so you may need to do a reinstall.|||

thanks, I've installed SQL Standard edition this weekend (with lots of problems, had to reinstall windows, visual studio did not install) and since then I have the mentioned items.

just discovered another (very) big problem though, not one of my SSIS packages works, every time I want to edit the data flow, Visual studio crashes,

any experience with that?

|||

That's weird. No errors come up? It just crashes?

I had an issue with very large packages sometimes crashing. It didn't really crash the IDE, but I couldn't save any packages. I would get an Out of Memory error. That mysteriously disappeared though.

Do you have SP1?

|||

I copy my thread on that, works now, I had to delete all data sources and data source views, execute, edit in debug mode and stop debugging, very weird indeed.

Adress was different by package

I have CTP1 installed, did you install the Techn preview of SP2? Worth doing (and taking the risk)?

I used an evaluation version till last week.

Was working fine so I decided to buy a Standard License.

Installed during the weekend, I had many problems till I decided to reinstall completely Windows. I have installed SP1

Everything worked fine then, ONLY not one of my SSIS packages still works; I get following error when editing the data flow task

The thread 'Win32 Thread' (0x7e8) has exited with code 0 (0x0).

Unhandled exception at 0x54fc5e89 in devenv.exe: 0xC0000005: Access violation reading location 0x00000000.

Please help, this is a disaster otherwise

Seems to work if I delete datasources and data source views and execute first?

ForeachFile and ForeachItem

Since I installed SP1, I do not see ForeachFile and ForEachItem anymore as possible collections in the Foreach package, What went wrong? Thanks for any advice.

Carl

|||

thanks for your answer but I did check that

In the list, I only see the ForEach loop item, and that I checked.

It's when I use that control flow item that I do not get the choice for ForEachFile and ForeachItem in the Collections page.

|||

Still could not solve this (annoying) problem (need ForEach File)

Can somebody please help?

thanks, I've installed SQL Standard edition this weekend (with lots of problems, had to reinstall windows, visual studio did not install) and since then I have the mentioned items.

just discovered another (very) big problem though, not one of my SSIS packages works, every time I want to edit the data flow, Visual studio crashes,

any experience with that?

|||

That's weird. No errors come up? It just crashes?

I had an issue with very large packages sometimes crashing. It didn't really crash the IDE, but I couldn't save any packages. I would get an Out of Memory error. That mysteriously disappeared though.

Do you have SP1?

|||

I copy my thread on that, works now, I had to delete all data sources and data source views, execute, edit in debug mode and stop debugging, very weird indeed.

Adress was different by package

I have CTP1 installed, did you install the Techn preview of SP2? Worth doing (and taking the risk)?

I used an evaluation version till last week.

Was working fine so I decided to buy a Standard License.

Installed during the weekend, I had many problems till I decided to reinstall completely Windows. I have installed SP1

Everything worked fine then, ONLY not one of my SSIS packages still works; I get following error when editing the data flow task

The thread 'Win32 Thread' (0x7e8) has exited with code 0 (0x0).

Unhandled exception at 0x54fc5e89 in devenv.exe: 0xC0000005: Access violation reading location 0x00000000.

Please help, this is a disaster otherwise

Seems to work if I delete datasources and data source views and execute first?

ForeachFile and ForeachItem

Since I installed SP1, I do not see ForeachFile and ForEachItem anymore as possible collections in the Foreach package, What went wrong? Thanks for any advice.

Carl

|||

thanks for your answer but I did check that

In the list, I only see the ForEach loop item, and that I checked.

It's when I use that control flow item that I do not get the choice for ForEachFile and ForeachItem in the Collections page.

|||

Still could not solve this (annoying) problem (need ForEach File)

Can somebody please help?

thanks, I've installed SQL Standard edition this weekend (with lots of problems, had to reinstall windows, visual studio did not install) and since then I have the mentioned items.

just discovered another (very) big problem though, not one of my SSIS packages works, every time I want to edit the data flow, Visual studio crashes,

any experience with that?

|||

That's weird. No errors come up? It just crashes?

I had an issue with very large packages sometimes crashing. It didn't really crash the IDE, but I couldn't save any packages. I would get an Out of Memory error. That mysteriously disappeared though.

Do you have SP1?

|||

I copy my thread on that, works now, I had to delete all data sources and data source views, execute, edit in debug mode and stop debugging, very weird indeed.

Adress was different by package

I have CTP1 installed, did you install the Techn preview of SP2? Worth doing (and taking the risk)?

I used an evaluation version till last week.

Was working fine so I decided to buy a Standard License.

Installed during the weekend, I had many problems till I decided to reinstall completely Windows. I have installed SP1

Everything worked fine then, ONLY not one of my SSIS packages still works; I get following error when editing the data flow task

The thread 'Win32 Thread' (0x7e8) has exited with code 0 (0x0).

Unhandled exception at 0x54fc5e89 in devenv.exe: 0xC0000005: Access violation reading location 0x00000000.

Please help, this is a disaster otherwise

Seems to work if I delete datasources and data source views and execute first?

Foreach?

Hi,

I’m not sure whether a “Foreach” capability is achievable in SQL; I’ve examined joins, unions and subqueries but can’t come up with a solution.

Scenario is 3 related tables: Location, Room, Booking (date and guest name) and what I wish to do is produce a data grid that shows every for each Location and for each room the status on a given day as follows:

LocationRoomDateGuest

Main11/1/06Jones

Main21/1/06Smith

Main3nullnull

Annex11/1/06Bloggs

House1nullnull

In psuedo-code what I want to do is

For a given date

Show each location

for each location show each room

for each room show guest (if present) else show null.

I can generate Location and Room OK using LEFT OUTER JOIN but when I introduce the date check it all goes pear shaped in that (obviously) the grid is not populated with nulls in place of the non-existent booking records!

Craighton:

Is this the idea of it?

set nocount on
declare @.location table( location varchar (20) not null)
insert into @.location values ('Main')
insert into @.location values ('Annex')
insert into @.location values ('House')
insert into @.location values ('Planned')
--select * from @.location
declare @.room table ( location varchar (20) not null, room varchar (8) not null )
insert into @.room values ('Main', '1')
insert into @.room values ('Main', '2')
insert into @.room values ('Main', '3')
insert into @.room values ('Annex', '1')
insert into @.room values ('Annex', '2')
insert into @.room values ('House', '1')
--select * from @.room
declare @.booking table
( location varchar (20) not null,
room varchar (8) not null,
booking datetime not null,
guest varchar (20) null
)
insert into @.booking values ('Main', '1', '1/1/6', 'Jones')
insert into @.booking values ('Main', '2', '1/1/6', 'Smith')
insert into @.booking values ('Annex', '1', '1/1/6', 'Bloggs')
--select * from @.booking
select a.location,
b.room,
c.booking,
c.guest
from @.location a
full join @.room b
on a.location = b.location
full join @.booking c
on ( a.location = c.location or
b.location = c.location
)
and ( b.room = c.room )

-- -
-- S A M P L E O U T P U T :
-- -
-- location room booking guest
-- -- -- --
-- Main 1 2006-01-01 00:00:00.000 Jones
-- Main 2 2006-01-01 00:00:00.000 Smith
-- Main 3 NULL NULL
-- Annex 1 2006-01-01 00:00:00.000 Bloggs
-- Annex 2 NULL NULL
-- House 1 NULL NULL
-- Planned NULL NULL NULL

|||

I forgot the "for a given date part." You will need to add:

and c.booking = @.givenDate

|||what about using a cross apply in place of the join ?|||

You can do the following (assumes that only one guest can stay in a room):

select l.Location, r.Room, @.date as Date

, (select b.Guest from Booking as b where b.RoomId = r.RoomId and b.Date = @.date) as Guest

from Location as l

join Room as r

on r.LocationId = r.LocationId

I might have made some incorrect assumptions about your schema and relationships between the tables. But you should get the idea. You don't need to do outer joins. Also, if you need to do this for a range of dates then use a Calendar table and cross join with that like:

select l.Location, r.Room, c.dt as Date

, (select b.Guest from Booking as b where b.RoomId = r.RoomId and b.Date = c.dt) as Guest

from Location as l

join Room as r

on r.LocationId = r.LocationId

cross join Calendar as c

where c.dt >= @.date1 and c.dt < dateadd(week, 7, @.date1)

|||

Mugambo,

Thank you for this - it worked well until the date check was added! See below for the solution from Umachandar. again, thanks for your response.

|||

Carllop,

Thanks for this - Iused both solution from Umachander and one contained the (new to me!) Cross feature.

|||

Umachandar,

Many thanks - worked a treat!

Foreach variable cannot be applied

I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.

The SQL Task that is linked to the Foreach container is somthing like this:

SELECT str1, str2, str2, nValue1, nValue2

FROM MyTable

In the SQL Task I've set the following

ResultSet = Full Result Set

Connection Type = OLE DB

Result Name = 0

I've created the following Variables with a Package scope

s1 as String

s2 as String

s3 as String

n1 as Double

n2 as Double

When I run the package I get the following errors

Foreach variable mapping number 4 to variable "User : : n1" cannot be applied

Foreach variable mapping number 5 to variable "User : : n2" cannot be applied

The data type of nValue1 and nValue2 in MyTable are both numeric(19,4)

In setting the variable types in the package it seems that the closest to numeric(19,4) would be double. Is this why it is failing? Is there any way around it? I just need to pass the variables which are numeric and have decimal places.

Any insight would be greatly appreciated.

Regards,

Bill

See if this blog post helps you:

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

|||

Thanks for the pointer, I found that if I cast the numeric fields in my query to float that the mapping seemed to pass the variables ok.

Regards,

Bill

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 NodeList Enumerator

Does anyone have any experience of using the NodeList enumerator in a Foreach loop? BOL is a bit light on this.

I want to enumerate over an XML Document that is passed into my package. The package is executed from a .net application.
Has anyone done anything like this?
Any demo material?
Should I pass the XML Document into an SSIS object variable or a String variable?
Can the NodeList enumerator enumerate an XML document that is stored in a String variable?
etc...
Thanks
JamieHey Jamie,
I've done this -- albeit for a rather simple example. I have an XML file that is just a persisted collection of structs with properties called "BusinessObjectName", so the data itself looks like this:
<DimensionInfos xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<BusinessDimensionEntries>
<anyType xsi:type="BusinessDimensionInfo">
<BusinessObjectName>User</BusinessObjectName>
</anyType>
</BusinessDimensionEntries>
</DimensionInfos>

Essentially I want to iterate over all the nodes and pull out the "BusinessObjectName" from each. I created a foreach NodeList enumerator, document source is the file, enumeration type is NodeText, XPath source is DirectInput (meaning I specify it within the task) and the XPath string is "//BusinessObjectName". This query will recursively match all nodes of that type. Then I map index 0 to some variable and I will get the NodeText "User" (as specified) of the node(s) that match my XPath expression.
Not sure if you came across this article:
http://databasejournal.com/features/mssql/article.php/3528791
Also, a good XPath reference:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk/html/_xpath_reference.asp

|||BTW - DocumentSourceType seems to be what you need, no? It allows you to draw the XML in from a variable or a string (which I suppose in theory you could bind to an expression =))
|||I have to say the use of indexes is a bit of a black art.

What if I want to get the value of multiple elements of a node.

i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

Can I iterate through mynode elements and store the value1 and value2 attributes.|||

SimonSa wrote:
I have to say the use of indexes is a bit of a black art.
What if I want to get the value of multiple elements of a node.
i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>
Can I iterate through mynode elements and store the value1 and value2 attributes.

Simon,

I'm trying to do something almost identical to this but just can't get it working at the moment. I'll post up here if i get something useful working.

-Jamie|||I sent feedback about the help not being very helpful.

The help for the properties of the for each loop doesn't explain what each one is it just says 'set the value'. What else would I do with it. It needs to say what the value should be set to. Unlike other feedback. I didn't get a response on this one|||

SimonSa wrote:
I sent feedback about the help not being very helpful.

Me too. Doug Laudenshlager is good at taking feedback on board so expect something more useful in the future.

-Jamie|||

SimonSa wrote:
I have to say the use of indexes is a bit of a black art.
What if I want to get the value of multiple elements of a node.
i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>
Can I iterate through mynode elements and store the value1 and value2 attributes.

Hi all,
OK, i'm progressing with this but have now hit EXACTLY the same problem as Simon. Here's my XML document:
<LastLoadDateList>
<Pair>
<StreamDetailID>3</StreamDetailID>
<LastLoadDate>2005-09-19 13:40:00</LastLoadDate>
</Pair>
<Pair>
<StreamDetailID>4</StreamDetailID>
<LastLoadDate>2005-09-19 13:42:15</LastLoadDate>
</Pair>
</LastLoadDateList>

I've managed to enumerate the 2 <Pair> nodes which results in the strings "32005-09-19 13:40:00" & "42005-09-19 13:42:15" getting enumerated. I've got EnumerationType=Nodetext.
Can you see what's happened here? Its concatenated the StreamDetailID & LastLoadDate nodes.

So how can I get those 2 values out into seperate variables? I guess its something to do with InnerXPathString but I can't see how to do it.

The lack of documentation around this is infuriating. To say the least!!!

Any help much appreciated!

-Jamie
|||<root>
<mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

If your goal is to enumerate the attributes of the preceeding document, you could do so with an EnumerationType of NodeText and an OuterXPathString of /root/mynode/@.*|||<LastLoadDateList>
<Pair>
<StreamDetailID>3</StreamDetailID>
<LastLoadDate>2005-09-19 13:40:00</LastLoadDate>
</Pair>
<Pair>
<StreamDetailID>4</StreamDetailID>
<LastLoadDate>2005-09-19 13:42:15</LastLoadDate>
</Pair>
</LastLoadDateList>

If your goal is to enumerate the children of the Pair nodes in the preceeding document, you could do so with an EnumerationType of NodeText and an OuterXPathString of /LastLoadDateList/Pair/*|||Can the NodeList Enumerator be used to slice out a segment of an XML file and store it in a variable? Here's an example of what I would like to do. Given the following xml file:

<book>
<section>
<name>Chapter1</name>
<content>...</content>
</section>
<section>
<name>Chapter2</name>
<content>...</content>
</section>
</book>

I would like to use the NodeList Enumerator to loop twice over the XML and pull out the section into a variable, such that the variable would contain <section><name>Chapter1</name><content>...</content></section> in the first iteration and <section><name>Chapter2</name><content>...</content></section> in the second. Whatever I do, the enumerator either wants to map the different elements to different variables or store everything as text. Any help on this matter is appreciated.

Regards,
Lars R?nnb?ck|||If you set the index to -1 and the variable type to object and the outerXpath to \\section you should end up with the section in your object|||Should the enumerator type then be "Navigator" or "Node", and how would you transform the object variable back to string, so I could use it in an XSLT task?

Thanks for the tip about the -1 index, that seems to put everything into a single variable though.

Regards,
Lars|||Probably Node

and probably need to use a script component to convert the object to text.|||

Since others might be struggling with figuring out the InnerXPathString I thought I would post my experiences. I have the following recursive schema in the database for my typed XML datatype:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xhtml="http://www.w3.org/1999/xhtml"
xmlns="http://garbleddomain/schemas/meta/jobb"
targetNamespace="http://garbleddomain/schemas/meta/jobb"
elementFormDefault="qualified">
<xs:element name="job">
<xs:annotation>
<xs:documentation>
<xhtml:p>
Defines a job.
</xhtml:p>
</xs:documentation>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element ref="job" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
<xs:attribute name="name" type="xs:string" />
<xs:attribute name="script" type="xs:string" />
<xs:attribute name="type" default="Group">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="Group" />
<xs:enumeration value="SP" />
<xs:enumeration value="SSIS" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>

The schema is used to define jobs I want to run that can be grouped and hierarchical. Here's an example XML document:

<?xml version="1.0"?>
<job xmlns="http://garbleddomain/schemas/meta/jobb">
<job name="Loads">
<job name="Load customer data" script="sp_loadCust" type="SP" />
<job name="Load articles" script="sp_loadArticles" type="SP" />
</job>
<job name="Updates">
<job name="Update transactions" script="sp_updateTrans" type="SP" />
<job name="Update categories" script="UpdateCategories" type="SSIS" />
</job>
</job>

To retrieve the XML using an Execute SQL Task over OLE DB I set the ResultSet type to XML and used the following Direct Input query:

SELECT CAST(definition AS VARCHAR(max)) AS JobDefinition
FROM META_Job_TB
WHERE (JobID = ?)

In the Parameter Mapping section I map one String variable, User::JobID as Input with type VARCHAR and Parameter Name 0. In the Result Set section I map another variable User::JobDefinition with Result Name 0. This will pull the XML document above wrapped in <ROOT> tags.

Since I want to remove the <ROOT> tags and since I couldn't get SSIS to work with typed XML I have a cleanup step using an XML Task (XSLT) where I remove the tags and namespace. The XSLT is a Direct Input which looks as follows:

<?xml version="1.0" ?>
<xsl:stylesheet
version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:id="http://garbleddomain/schemas/meta/jobb"
exclude-result-prefixes="id">
<xsl:template match="/">
<xsl:apply-templates mode="copy-no-ns" select="/ROOT/id:job"/>
</xsl:template>
<xsl:template mode="copy-no-ns" match="*">
<xsl:element name="{name(.)}">
<xsl:copy-of select="@.*"/>
<xsl:apply-templates mode="copy-no-ns"/>
</xsl:element>
</xsl:template>
</xsl:stylesheet>

I use the User::JobDefinition variable both as input source and output operation result destination. Now I am left with the job definition XML document without the namespace. In this case I want to iterate over the actual jobs and store the attributes in variables that can be accessible inside the loop, and do the following:

1. Create three String variables; User::JobName, User::JobScript, and User::JobType.
2. In the ForEach Loop I select the NodeList Enumerator.
3. I use the User::JobDefinition as document source.
4. Set EnumerationType to ElementCollection.
5. Set the OuterXPathString to //job[not(@.type = 'Group')]
6. Set the InnerElementType to NodeText.
7. Set the InnerXPathString to @.*
8. In Variable Mappings add the three String variables with Index 0, 1, and 2.

Voila, the variables will now be set to the values of the attributes for each job.

Hope this helps someone,
Regards,
Lars R?nnb?ck

Foreach NodeList Enumerator

Does anyone have any experience of using the NodeList enumerator in a Foreach loop? BOL is a bit light on this.

What if I want to get the value of multiple elements of a node.

i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

Can I iterate through mynode elements and store the value1 and value2 attributes.|||

SimonSa wrote:
I have to say the use of indexes is a bit of a black art.
What if I want to get the value of multiple elements of a node.
i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>
Can I iterate through mynode elements and store the value1 and value2 attributes.

Simon,

I'm trying to do something almost identical to this but just can't get it working at the moment. I'll post up here if i get something useful working.

-Jamie|||I sent feedback about the help not being very helpful.

SimonSa wrote:
I sent feedback about the help not being very helpful.

Me too. Doug Laudenshlager is good at taking feedback on board so expect something more useful in the future.

-Jamie|||

SimonSa wrote:
I have to say the use of indexes is a bit of a black art.
What if I want to get the value of multiple elements of a node.
i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>
Can I iterate through mynode elements and store the value1 and value2 attributes.

So how can I get those 2 values out into seperate variables? I guess its something to do with InnerXPathString but I can't see how to do it.

The lack of documentation around this is infuriating. To say the least!!!

Any help much appreciated!

-Jamie
|||<root>
<mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

Thanks for the tip about the -1 index, that seems to put everything into a single variable though.

Regards,
Lars|||Probably Node

and probably need to use a script component to convert the object to text.|||

Since others might be struggling with figuring out the InnerXPathString I thought I would post my experiences. I have the following recursive schema in the database for my typed XML datatype:

SELECT CAST(definition AS VARCHAR(max)) AS JobDefinition
FROM META_Job_TB
WHERE (JobID = ?)

Hope this helps someone,
Regards,
Lars R?nnb?ck

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

Tuesday, March 27, 2012

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...?

Someone please provide me steps to make it work.

Thanks in adv.

-Jamie

|||

Thanks Jamie.... it worked wonderfully!

Foreach Loop, Data Flow task buffer failed

I have a package that runs fine by itself.But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops.Here are a couple of the error lines:

A buffer failed while allocating 49085616 bytes.

The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

I already played around with the Data Flow task’s DefaultBufferMaxRows and DefaultBufferSize properties, and I am still getting the error. Just wondering if there is a memory leak or something with the Foreach Loop task.I haven’t install SP1.Maybe SP1 fixes this issue?

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

|||I have SP1 installed and I have a similar issue. I do not get an error but the DataFlow hangs at 33 in OnProgress/Pre-execute event (Datacode=33 in sysdtslog90). My package executes another package from within the 'ForEach' loop. The child package contains the DataFlow task. When I run the child package standalone (i.e. not from the parent package containing the 'ForEach' loop) with the same variables as in the parent, the DataFlow works fine.

Foreach Loop, Data Flow task buffer failed

I have a package that runs fine by itself.But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops.Here are a couple of the error lines:

A buffer failed while allocating 49085616 bytes.

The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

Foreach loop with XML Source failure

I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
<xs:attribute name="computerName" type="xs:string" use="optional" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
<xs:attribute name="Model" type="xs:string" use="optional" />
<xs:attribute name="Make" type="xs:string" use="optional" />
<xs:attribute name="BIOS" type="xs:string" use="optional" />
<xs:attribute name="Processor" type="xs:string" use="optional" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
<computerCount QTY="1" />
<computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>

That error says INPUT column, so I doubt its coming from the XML source adapter. An XML source adapter has output columns (and external metadata columns). Are you certain the error is with the source adapter and not some other pipeline component?

As an aside, that XSD and xml will work just fine in without regard the surrounding container. The XSD is not broken so far as use in the SSIS source adapter is concerned, although it does not contain the <computerCount> element.|||

Thank you for the feedback, but I think that I failed to mention that yes, the next thing that I send the XML Source to, whether it be a sort, derived column, an ole db destination, etc... is where the failure shows up.

Take for instance the case where I put the XML Source to an OLE DB Destination. I use a file and set the columns via regular mapping. Then I go back and set the XML Source to another file to be sure it continues to work and I get the error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 4776 that was not previously used in the Data Flow task. Package.dtsx 0 0

Then I go back into the Ole DB Destination and have it map using Column Names. And everything is okay again. Then go back and switch to the next file and get this error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 5265 that was not previously used in the Data Flow task. Package.dtsx 0 0

It's a vicious cycle.

An aside, to your aside, I was messing with the XSD and took out the ComputerCount during debug.

Thank you for your help.

sql

Foreach loop with parallel execution

Is is possible to get the iterations in a foreach loop to run in parallel? What I need to do is to spawn an arbitrary number of parallel execution paths that all look exactly the same. The number is equal to the number of input files, which varies from time to time. Any help is appreciated!

Regards,
Lars R?nnb?ck

Nope. This feature was available in some beta releases, so you may notice it references in newsgroups and forums. But it was cut due to complexity and quality issues.

|||Thanks for the answer, albeit not what I was hoping for. Could it be "simluated" by having a loop containing only an Execute Package Task and the ExecuteOutOfProcess flag set to true?

Regards,
Lars|||ExecuteOutOfProcess does not change synchronous behavior of Execute Package Task - the task still waits for the package to finish.

If you want to start child packages really asynchronously - i.e. start child and continue execution of parent package, use Execute Process Task to start dtexec, specify
application="cmd.exe" and
parameters="/c start dtexec.exe /f package file ..."

You'll also need to configure child package using DTEXEC's command line (where I've left '...').|||Note that
1) parent can't reliably get execution result from the children, since it may exit before all children finish,
2) in some cases you may get even worse performance compared to sequential execution - since all these packages will clash for processor and memory.|||Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack?

Thanks,
Lars|||3) You don't have any control over the degree of parallelism. If you have 100 files, but only want to process three at a time for example.
|||

lasa wrote:
Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack?
Thanks,
Lars

The smart money says this will appear in a later version. Alot of people are asking for it.

-Jamie|||

Wouldn't it be possible to achieve control over the degree of parallelism using a Dummy package and the /MaxConcurrent flag of dtexec? Say I start four "real" packages in parallel using cmd.exe and use /MaxConcurrent 4 as an option to dtexec, then I start one dummy package using dtexec directly with the option /MaxConcurrent 1. The way I have understood it, the dummy package will now be queued for execution and will start only when the number of parallell processes goes below 1, i e when all four "real" packages are finished?
I am going to try this out and will report back.
Regards,
Lars

|||Since the experiment above didn't work out the way I thought (the dummy package started regardless of the fact that four other packages were running) I am guessing that I have misunderstood the /MaxConcurrent option. Taken from BOL:

Specifies the number of executable files that the package can run concurrently. The value specified must be a non-negative integer, or -1. A value of -1 means that SSIS will allow a maximum number of concurrently running executables that is equal to the total number of processors on the computer executing the package, plus two.

What kind of executable files is the text referring to? Those that are called using the "Execute Process Task" within a package? It made more sense that the SSIS engine would only allow a certain number of concurrently running packages.

Regards,
Lars|||Executables are tasks in a single package. The SSIS runtime nor DTExec do not do any interprocess communication to limit the number of packages or tasks running across process boundaries.

Matt

Foreach loop with parallel execution

Regards,
Lars R?nnb?ck

Nope. This feature was available in some beta releases, so you may notice it references in newsgroups and forums. But it was cut due to complexity and quality issues.

|||Thanks for the answer, albeit not what I was hoping for. Could it be "simluated" by having a loop containing only an Execute Package Task and the ExecuteOutOfProcess flag set to true?

Regards,
Lars|||ExecuteOutOfProcess does not change synchronous behavior of Execute Package Task - the task still waits for the package to finish.

Thanks,
Lars|||3) You don't have any control over the degree of parallelism. If you have 100 files, but only want to process three at a time for example.|||

lasa wrote:
Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack?
Thanks,
Lars

The smart money says this will appear in a later version. Alot of people are asking for it.

-Jamie|||

Matt

ForEach loop with Excel

Hi,

I'm attempting to use the Foreach loop container to loop through the excel files located on a shared network folder. I've set up the Excel file connection manager to include the user variable generated from the container, but I get the below error messages when applying that variable to the connection string in the data flow. I've tried everything but I can't seem to get SSIS to recognize the path of the Excel files. I've tried copying the files to my PC, I tried running the package on the server, etc. The connection works fine if I set it up to point to any of the excel files in the network directory, but not with the Foreach loop connection name.

Any help or suggestions would be greatly appreciated! I've looked everywhere and tried everything but to no avail...

Thanks,

Kevin

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

You should give more detail about how you're configuring the connection manager, included any expressions and which properties they're being applied to. Confirming the run-time values of variables through the use of breakpoints would be helpful, too.

You say you're setting the connection string. Isn't there a FileName property?
|||

Thanks!

It's my first time posting on this site so I'll try and do a better job explaining what I'm trying to do.

I have a connection to an Excel file created in my package. Using the ForEach loop I'm attempting to change the connection string on that connection for each excel file located in the directory. I've created a user variable in the ForEach loop that's supposed to be populated with the fully qualified location of each excel file. The data flow component has the error on it before I even execute the package. That component is linked to an OLE DB destination which is a SQL table.

I can send you further detail if you'd like or if I'm missing anything...

Kevin

|||

Kevin wrote:

I have a connection to an Excel file created in my package. Using the ForEach loop I'm attempting to change the connection string on that connection for each excel file located in the directory. I've created a user variable in the ForEach loop that's supposed to be populated with the fully qualified location of each excel file. The data flow component has the error on it before I even execute the package. That component is linked to an OLE DB destination which is a SQL table.

You're saying the OLE DB Destination component is giving you an error? I would expect the error to be on the Excel Source. Usually this is because whatever variables are used in the expression to control Source have not been initialized with default values. The Source needs design-time access to one of the files so it can read the metadata.

Your For Each loop should be placing the fully qualified name of your Excel files into a package-level variable. This package-level variable should have a valid path to an existing file as a default value. You should set up an expression on the Excel connection manager to set the ExcelFilePath property with your variable containing the filename.
|||

The OLE DB Destination component is ok. I've assigned an excel file to the excel source but it gets overwritten because of the ForEach loop variable that was created.

I've done what you explained in the second part of your reply. I set the ConnectionString expression in the Excel file connection to the variable in the ForEach loop.

I used the example in this article but with an excel connection:

http://www.sqlis.com/55.aspx

Thank you,

Kevin

|||

Kevin wrote:

The OLE DB Destination component is ok. I've assigned an excel file to the excel source but it gets overwritten because of the ForEach loop variable that was created.

Correct. The default value is only there for design-time metadata. It will be overwritten at run-time when you actually read the file.

Kevin wrote:

I've done what you explained in the second part of your reply. I set the ConnectionString expression in the Excel file connection to the variable in the ForEach loop.

I think you want the ExcelFilePath property, not the ConnectionString.
|||

I really appreciate your help.

I tried using the ExcelFilePath expression instead and I'm still getting the same error. Also, when I set the excel file in the connection and then go back into it in design mode, the file path is empty.

This is getting pretty frustrating to have this great option but not have it work

Here's the message text again. It's erroring on Package Validation...

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error at Extract UK RDB [DTS.Pipeline]: component "UK RDB" (1) failed validation and returned error code 0xC020801C.

Error at Extract UK RDB [DTS.Pipeline]: One or more component failed validation.

Error at Extract UK RDB: There were errors during task validation.

Error at GDW - RDB LOAD [Connection manager "RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

|||

I think I may have helped answer my own post with that last reply. There's a setting in the package properties called DelayValidation which I set to True, and now the package runs. I still get the error on the data flow component, but each excel file is loaded. DelayValidation indicates whether the validation of the executable is delayed until run time.

|||

Kevin wrote:

I tried using the ExcelFilePath expression instead and I'm still getting the same error.

I don't know what that error is. Maybe the component got messed up somehow. Try deleting it and creating a new one.

Kevin wrote:

Also, when I set the excel file in the connection and then go back into it in design mode, the file path is empty.

That indicates to me that you don't have a default value in that variable.

ForEach Loop utilisation

Hello,

ForEach Loop Item allow to make operations row per row.

How can i do operations 10 rows per 10 rows or 100 rows per 100 rows ?

Thanks !So, a few folks have blogged about this.

http://www.sqlis.com/default.aspx?59
http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx

I thought Jamie did, but a quick search turned up empty.
In any case, one of the best sources of information on SSIS is Jamie Thomson's blog.
http://blogs.conchango.com/jamiethomson/default.aspx

HTH|||

Coroebus wrote:
How can i do operations 10 rows per 10 rows or 100 rows per 100 rows ?

Can you elaborate on exactly what you want to do? "10 rows per 10 rows" isn't very descriptive.

-Jamie|||

I want to make a request to yahoo stock quotes.

In my database i have a list of quotes, and i want to get a page with a selection of quotes.

look this example :
http://fr.old.finance.yahoo.com/d/quotes.csv?s=SLB.PA&f=snl1d1t1c1ohgv&e=.txt

I can request up to 200 quotes maximum like this

http://fr.old.finance.yahoo.com/d/quotes.csv?s=ATO.PA,AF.PA,AC.PA,ADE.PA&f=snl1d1t1c1ohgv&e=.txt

In my package i want to be able to create those request with a defined list of quotes.

I hope you can understand my bad english...

thanks a lot

|||Your english is good, don't worry about that :)

You can read your list of quotes into an Object variable using 1 of 2 methods:
1) Use the Execute SQL Task or
2) Use a data-flow with a Recordset destination component.

Once there you can loop over it using the Foreach Loop's "Foreach ADO Enumerator" and put the stock into a variable. The variable can then be used in a property expression to build a URL ("http://finance.yahoo.com/q?s=" + User::VariableName) for the HTTP Connection Manager that will retrieve the stock quote from the Yahoo site.

I don't have an SSIS instance to hand so can't build a demo of this but if you're having trouble let me know and I'll see what I can do later.

In the meantime, this article at SQLIS.com explains the basic process that you need to go through here: http://www.sqlis.com/default.aspx?59

Hope this helps.

-Jamie|||Ok, but how can I make row with 200 quotes ?|||

Coroebus wrote:
Ok, but how can I make row with 200 quotes ?

I'm not quite sure I understand. Do you mean that instead of 200 rows with 1 quote in each you want 1 row containing the same 200 quotes? [In other words you want to pivot the data.]

-Jamie|||No, I have in a table a list of 2000 quotes. I had to treat it by 200 quotes Items, request yahoo with this kind of http request.
In a sense, my package schould do this :

1- Create the list of quotes
2- Create http request for the 200 first quotes
3- Treat the http file
4- Create http request for the 200 Next quotes
5- Treat the http file
6- ...

I can do it quotes per quotes but i think it is more efficient 200 per 200

Thanks a lot for your help|||So you want to send the 2000 stocks to Yahoo in batches of 200, is that correct?
And the 200 stocks are stored in a table, is that correct?

If so I would do the following:
1) Have a Foreach loop that pulls a batch of 200 out of the table. It does this using the new Yukon windowing functions (http://sqljunkies.com/HowTo/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk). Each time around the loop it pulls out the next batch of 200 until there is none left.
2) Inside that Foreach Loop have another Foreach loop that loops over the 200 returned rows, sending the request through to Yahoo for each one.

Does that make sense? Have I understood you correctly?

-Jamie|||YES !!!! Big Smile

I was sure you'll find wht i want !

thanks a lot|||No problem. Let us know how you get on. The windowing functions are right up there on my "favourite new features of SQL Server" list. Nowhere near SSIS of course Smile

-Jamie|||

Sorry... Those functions looks nice but are not made to my problem...

I'm trying to resolve my problem with a transformation script to make a recordset containing rows in the correct format.

If you want, i'll send you the script

Nico

|||If you think it'll help. jamie.thomson[at]donotspamme.conchango.com

-Jamie

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 Smile |||

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

Thursday, March 29, 2012

ForEachLoop task not behaving as expected

ForEachLoop Container - How to Force Next Iteration -

ForeachFile and ForeachItem

ForeachFile and ForeachItem

ForeachFile and ForeachItem

Foreach?

Foreach variable cannot be applied

ForEach Trapping an Error and Continuing

Foreach NodeList Enumerator

Foreach NodeList Enumerator

foreach loop?

Tuesday, March 27, 2012

foreach loop?

Foreach Loop, Data Flow task buffer failed

Foreach Loop, Data Flow task buffer failed

Foreach loop with XML Source failure

Foreach loop with parallel execution

Foreach loop with parallel execution

ForEach loop with Excel

ForEach Loop utilisation

ForEach Loop sequence question

FullText Ranking

Blog Archive

About Me