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

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.

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

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.

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

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

OK

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:

OK

|||

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

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

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

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

Foreach loop runs out of memory

Hi

I have a for each loop which steps through an ado recordset (approx. 5,000 rows), this passes two variables to an SQL statement which populates a second recordset (normally 8 to 10 rows). I use the second recordset in a dataflow task which was a simple Script which returns approximately 30 rows for inclusion in my destination table. The package runs for a while OK, although the loop appears to execute slowly, then I get the below message constantly repeated in the debug window.

[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 174 buffers were considered and 174 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

I have 2gb of virtual memory on my machine, and the recordsets are relatively small. Have I missed a seting some where?My guess is your problem is where you're creating 5,000 8-10 row recordsets. Instead of creating those recordsets from a SQL statement and consuming them in a Data Flow script, can you not execute the SQL statement in an OLE DB Source in your Data Flow?

Maybe you simplified your scenario for the sake of explanation, but if you can do the above, I wonder if you can't find a way to move that 5,000 iteration loop into the same SQL Statement as a set-based operation.

|||

Thanks for the reply, the 5000 row record set has two fields one integer the other nchar(5). The second recordset gets re-used in the loop so this will only have 10 rows max at a time and has 4 fields. Unfortunately I cannot change the Loop to SQL, I do not have any knowledge of set based operations, is this similar to a loop?

Regards

ADG

|||What SP level are you running? I looks like there was some memory related issues in pre SP versions of the products.|||

How is the second recordset being reused? Do you mean that you are repopulated the same variable with a new recordset?

Also, are you explicitly closing the recrodset you are using?

|||

Thanks for the reply.

Inside my Foreach loop container I have an Execute SQL task which uses the two variables from the Recordset being scrolled through, and populates my second recordset. Do I need to explicitly close the recordset inside the loop? If so how should the recordset be closed, is this a Script task?

|||

ADG wrote:

Thanks for the reply.

Inside my Foreach loop container I have an Execute SQL task which uses the two variables from the Recordset being scrolled through, and populates my second recordset. Do I need to explicitly close the recordset inside the loop? If so how should the recordset be closed, is this a Script task?

The outer recordset (A) probably isn't the issue - it's the second one (B) that I am talking about. You are using the second one in a data flow, right? Are you using a source script component to read recordset B? If so, call the recordset.Close() method (if you are using ADODB) or the DataTable.Dispose() method if you are using ADO.NET) after the loop to output the rows completes.

|||

Many thanks,

I will change the script component and give it a try. I am new to SQL Server / SSIS so it looks like II have made another beginners error.

|||

I have tried to close the recordset but the close method does not appear to be valid in the main code,( I tried Me.Variables.MyRecordSet2.close). Do I need to override one of the Class methods? The code I used is modified from one of Jamie Thomsons blogs and is as below:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

'

Dim olead As New Data.OleDb.OleDbDataAdapter'Define an ADO.Net data adapter

Dim dt As New Data.DataTable'Define an ADO.Net DataTable

Dim row As System.Data.DataRow'Define an ADO.Net DataRow

Dim lastSI, SI As Int32

Dim strU(50) As String

Dim strU2(50) As String

Dim dblFactor(50) As Double

Dim x, y, j, k As Int32

Dim isFirst As Boolean

Dim strLComb, strComb As String

olead.Fill(dt, Me.Variables.MyRecordSet2)'Populate our DataTable from the adapter

lastSI = 0

strLComb = " "

SI = 0

x = 0

isFirst = True

For Each row In dt.Rows'Iterate over the rows in the table

strComb = row("SI").ToString & row("U").ToString

If strComb <> strLComb Then

If isFirst Then

isFirst = False

Else

For y = 1 To (x - 1)

For k = 2 To x

With OutputBuffer

.AddRow()

.Calculated = True

.Factor = dblFactor(k) / dblFactor(y)

.SI = SI

.U = strU2(y)

.U2 = strU2(k)

End With

Next

Next

End If

SI = CType(row("SI"), Integer)

strLComb = row("SI").ToString & row("U").ToString

x = 1

strU(x) = row("U").ToString

strU2(x) = row("U2").ToString

dblFactor(x) = CType(row("Factor"), Double)

Else

x = x + 1

strU(x) = row("U").ToString

strU2(x) = row("U2").ToString

dblFactor(x) = CType(row("Factor"), Double)

End If

Next

For y = 1 To (x - 1)

For k = 2 To x

With OutputBuffer

.AddRow()

.Calculated = True

.Factor = dblFactor(k) / dblFactor(y)

.SI = SI

.U = strU2(y)

.U2 = strU2(k)

End With

Next

Next

dt.Dispose()

End Sub

End Class

|||Thanks for showing us your code. I don't see why you need the recordsets at all. It looks like you could just as easily execute the query that creates the recordset in an OLE DB Source component and use this code in a custom transformation component. Instead of the rows coming into this code via Recordset, they come in via the component's input. This is what I suggested on 4/2.
|||Since you are using ADO.NET, you need to call the Dispose() method. However, Jay is correct in saying that the operation you are trying to perform would be much more efficient if it was not done in a script component.|||

Thanks again,

Can you point me in the right direction with the custom transformation. I have created the OLE DB which will return between 2 and 10 rows, I need to store this in an array to process. I assume that each row will pass into the script via Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) . How do I catch all the rows then perform the output?

|||

ADG wrote:

Thanks again,

Can you point me in the right direction with the custom transformation. I have created the OLE DB which will return between 2 and 10 rows, I need to store this in an array to process. I assume that each row will pass into the script via Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) . How do I catch all the rows then perform the output?

Take a look at this topic in books online: "Creating an Asynchronous Transformation with the Script Component"

Since your are not simply passing each row through, you'll need to use an asynchronous transform.

|||If you have your rows coming from an OLE DB Source, then you would change your script source to an asynchronous script transformation and do your processing on the incoming rows instead of shredding the recordset. Biggest difference is that you no longer have a FOR loop to iterate the rows. Instead ProcessInputRows gets called multiple times. Therefore some of your variables needed to be made global. Also, the rows you output after seeing all the input rows had to be moved to another method that gets called when the input rows are finished. I rearranged your code to demonstrate. Hopefully I didn't change the logic any.

Code Snippet

Dim lastSI As Int32 = 0
Dim SI As Int32 = 0
Dim strU(50) As String
Dim strU2(50) As String
Dim dblFactor(50) As Double
Dim x As Int32 = 0
Dim isFirst As Boolean = True
Dim strLComb As String = " "

Public Overrides Sub FinishOutputs()
Dim y, k As Int32
For y = 1 To (x - 1)
For k = 2 To x
With OutputBuffer
.AddRow()
.Calculated = True
.Factor = dblFactor(k) / dblFactor(y)
.SI = SI
.U = strU2(y)
.U2 = strU2(k)
End With
Next
Next
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Dim strComb As String
Dim y, k As Int32

strComb = Row.SI & Row.U

If strComb <> strLComb Then

If isFirst Then
isFirst = False
Else

For y = 1 To (x - 1)
For k = 2 To x
With OutputBuffer
.AddRow()
.Calculated = True
.Factor = dblFactor(k) / dblFactor(y)
.SI = SI
.U = strU2(y)
.U2 = strU2(k)
End With
Next
Next
End If

SI = CType(Row.SI, Integer)
strLComb = Row.SI & Row.U

x = 1
strU(x) = Row.U
strU2(x) = Row.U2
dblFactor(x) = CType(Row.Factor, Double)

Else
x = x + 1
strU(x) = Row.U
strU2(x) = Row.U2
dblFactor(x) = CType(Row.Factor, Double)
End If

End Sub

|||

Many thanks for the post. I have incorporated some of the above into my final package

However, I have gone back to using a for each loop to generate a OLE DB source, which feeds an asynchronous script transformation . The above works but does not create all the records that I needed. The process of adding rows to the table in turn creates more possible row combinations. My memory issue was eventually resolved by installing SP2. My package now works, but is slow. Below is an exapmle of my data problem ( data for two SKU's as in the original source, and as in my final table )

SI U1 U2 Factor 1 CT BG 192 1 CT EA 24 1 CT KG 10.9 1 CT M3 0.001 1 CT SM 143 1 PL CT 49 1 PL HT 1400 2 CT BG 300 2 CT EA 30 2 CT KG 2.028 2 CT M3 0.001 2 CT SM 125 2 PL CT 96 2 PL HT 1400

Becomes

SI U1 U2 Factor Calculated 1 BG BG 1.00000000 1 1 BG CT 0.00520833 1 1 BG EA 0.12500000 1 1 BG KG 0.05677083 1 1 BG M3 0.00000521 1 1 BG PL 0.00010629 1 1 BG SM 0.74479167 1 1 CT BG 192.00000000 0 1 CT CT 1.00000000 1 1 CT EA 24.00000000 0 1 CT KG 10.90000000 0 1 CT M3 0.00100000 0 1 CT PL 0.02040816 1 1 CT SM 143.00000000 0 1 EA BG 8.00000000 1 1 EA CT 0.04166667 1 1 EA EA 1.00000000 1 1 EA KG 0.45416667 1 1 EA M3 0.00004167 1 1 EA PL 0.00085034 1 1 EA SM 5.95833333 1 1 HT HT 1.00000000 1 1 HT PL 0.00071429 1 1 KG BG 17.61467890 1 1 KG CT 0.09174312 1 1 KG EA 2.20183486 1 1 KG KG 1.00000000 1 1 KG M3 0.00009174 1 1 KG PL 0.00187231 1 1 KG SM 13.11926606 1 1 M3 BG 192000.00000000 1 1 M3 CT 1000.00000000 1 1 M3 EA 24000.00000000 1 1 M3 KG 10900.00000000 1 1 M3 M3 1.00000000 1 1 M3 PL 20.40816327 1 1 M3 SM 143000.00000000 1 1 PL BG 9408.00000000 1 1 PL CT 49.00000000 0 1 PL EA 1176.00000000 1 1 PL HT 1400.00000000 0 1 PL KG 534.10000000 1 1 PL M3 0.04900000 1 1 PL PL 1.00000000 1 1 PL SM 7007.00000000 1 1 SM BG 1.34265734 1 1 SM CT 0.00699301 1 1 SM EA 0.16783217 1 1 SM KG 0.07622378 1 1 SM M3 0.00000699 1 1 SM PL 0.00014271 1 1 SM SM 1.00000000 1 2 BG BG 1.00000000 1 2 BG CT 0.00333333 1 2 BG EA 0.10000000 1 2 BG KG 0.00676000 1 2 BG M3 0.00000333 1 2 BG PL 0.00003472 1 2 BG SM 0.41666667 1 2 CT BG 300.00000000 0 2 CT CT 1.00000000 1 2 CT EA 30.00000000 0 2 CT KG 2.02800000 0 2 CT M3 0.00100000 0 2 CT PL 0.01041667 1 2 CT SM 125.00000000 0 2 EA BG 10.00000000 1 2 EA CT 0.03333333 1 2 EA EA 1.00000000 1 2 EA KG 0.06760000 1 2 EA M3 0.00003333 1 2 EA PL 0.00034722 1 2 EA SM 4.16666667 1 2 HT HT 1.00000000 1 2 HT PL 0.00071429 1 2 KG BG 147.92899408 1 2 KG CT 0.49309665 1 2 KG EA 14.79289941 1 2 KG KG 1.00000000 1 2 KG M3 0.00049310 1 2 KG PL 0.00513642 1 2 KG SM 61.63708087 1 2 M3 BG 300000.00000000 1 2 M3 CT 1000.00000000 1 2 M3 EA 30000.00000000 1 2 M3 KG 2028.00000000 1 2 M3 M3 1.00000000 1 2 M3 PL 10.41666667 1 2 M3 SM 125000.00000000 1 2 PL BG 28800.00000000 1 2 PL CT 96.00000000 0 2 PL EA 2880.00000000 1 2 PL HT 1400.00000000 0 2 PL KG 194.68800000 1 2 PL M3 0.09600000 1 2 PL PL 1.00000000 1 2 PL SM 12000.00000000 1 2 SM BG 2.40000000 1 2 SM CT 0.00800000 1 2 SM EA 0.24000000 1 2 SM KG 0.01622400 1 2 SM M3 0.00000800 1 2 SM PL 0.00008333 1 2 SM SM 1.00000000 1

The project takes about 30 minutes to run with 21,500 input rows and has 135,900 final rows. I guess if this process could be done with SQL it would be far more efficient, but I cannot see how this can be done when each row added can gerate more possible combinations. If anyone out there can see away of performing the above transformation with pure SQL I would be grateful for your ideas.