Foreach loop process error

Hello,

In a Foreach loop component, i have this message at 2nd or 3rd loop but never at first.

[DTS.Pipeline] Error: The ProcessInput method on component "Sort" (716) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Does anyone can help me ?

Coroebus wrote:

Hello,

In a Foreach loop component, i have this message at 2nd or 3rd loop but never at first.

[DTS.Pipeline] Error: The ProcessInput method on component "Sort" (716) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Does anyone can help me ?

I *may* be wrong but I believe this is a known bug and has been fixed in the next CTP. Something to do with the Sort component grabbing too much memory when used inside a Foreach loop.

-Jamie|||Yes, we have fixed some of these already. It would be great if you can confirm this when the new CTP comes out.

thanks!|||

Ash Sharma wrote:

Yes, we have fixed some of these already. It would be great if you can confirm this when the new CTP comes out.

Any sharable info on when that might take place?

Foreach loop container Foreach File Enumerator sort files

How do you sort files from the Foreach loop container?

I love this component, except I can't get it to sort by filename.

Nice to know how to sort by timestamps also.

Dave

Cylon2005 wrote:

How do you sort files from the Foreach loop container?

I love this component, except I can't get it to sort by filename.

Nice to know how to sort by timestamps also.

Dave

You can't sort the foreach loop container. My experience is that it always *does* sort by name, but I've never trusted it.

One thing you can do is with a few steps. First use your foreach loop container to get the list of files, and then populate a database table. Then, outside of the foreach loop, use an execute SQL task to select from that table using an ORDER BY. Load an object variable with that result set. Then use a second foreach loop to spin through that object variable (ADO.net recordset). From here you can perform the work you desire.|||I was about to suggest the same; puting the file names in a table give you control over the order....|||

Thanks. It sounds like a lot of work for something that should be a property setting.

|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

Post your feedback at http://connect.microsoft.com/sqlserver/feedback|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

I would agree. Perhaps you could post a suggestion at connect (http://connect.microsoft.com/sqlserver/feedback/)

Your original mail said you wanted to order by filename. By default, this is what it does. I'm a little bit more trusting than Phil as well - I've never seen it do anything different

-Jamie

|||

Rafael Salas wrote:

I was about to suggest the same; puting the file names in a table give you control over the order....

This SQLCLR sproc *might* help you do this:

Replacement for xp_getfiledetails

(http://blogs.conchango.com/jamiethomson/archive/2006/08/24/4400.aspx)

-Jamie

|||

Jamie Thomson wrote:

I would agree. Perhaps you could post a suggestion at connect (http://connect.microsoft.com/sqlserver/feedback/)

Your original mail said you wanted to order by filename. By default, this is what it does. I'm a little bit more trusting than Phil as well - I've never seen it do anything different

-Jamie

The only reason I've never trusted it is because I've never had to. I haven't had a situation yet where I've been required to process the files in order. My requirements to date have been to process all available files in the directory, order irrelevant.

However, the OP is claiming that it doesn't order by filename. I'm curious to know two things, Dave: 1 - What are your filenames? 2 - How have you confirmed that they are not being processed in order?|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

Well, as discussed, it isn't. Have you posted anything to Connect?

-Jamie

|||A ascending/descending Sorted Files ForEach enumerator would be a fairly straight-forward extension of the ForEach Directory enumerator included in the set of SQL samples (SqlServerSamples.msi). For example, I added sorting on properties like name (not full path), extension, size, dates, and a regular expression on the file name itself. The included enumerator is for directories, but modifying it to return sorted files was not much of a change.

The included enumerator sample is "%ProgramFiles%\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\ForEachDirectory Sample", which has both VB.NET and C# versions.

After building the sample, the new enumerator shows up in the For Each container's enumerator list box as "For Each Directory CS", and it has a UI included. Property expressions (on the enumerator itself) may be used as well, as below. The actual enumerated object is an ArrayList of directories. No, this enumerator is not sorted by default, since it calls GetDirectories() on a DirectoryInfo object. Point is, its "easy" (for those with a .NET framework background) to add sorting to it and almost all of it is already built.

Example expressions on enumerator
RootDirectory =>3 "c:\\"
RootDirectorySourceFile => 3 (DirectInput)
EnumerateSubFolders => false

To install the enumerator, modify the project's (in this case C#) post-build event to write the enumerator assembly (which actually contains the enumerator's UI as well) to the Global Assembly Cache and also into the For Each Enumerators directory.

xcopy /y "$(TargetPath)" "%ProgramFiles%\Microsoft SQL Server\90\DTS\ForEachEnumerators"
"$(DevEnvDir)..\..\SDK\v2.0\bin\gacutil.exe" /if "$(TargetPath)"

The second step (gacutil...) is an attempt to write the enumerator to the Global assembly cache. Global assembly cache installs can also be done by copy and paste of the dll (pointed to by $TargetPath) into the "%windir%\assembly" folder.

|||

Wow. Fantastic stuff Jaegd. If I have time I might try and have a play with that later - see if I can get it sorted any differently. Unless you've done it already (which it sounds like you might have done)?

-Jamie

|||

Yes, I have built the sorted files enumerator. The part I'm not sure about is how to build a platform independent installer for SSIS widgets (e.g. an installer that writes to the correct locations on x64 and x86). Installing to the Global assembly cache is no problem.

Perhaps Darren can answer this one or point me in the correct direction. What are the steps necessary to acquire the correct location(s) for a platform indepedent SSIS "widget" install (task,component,enumerator,logger,connection manager). I'm not sure where you can get a platform independent location(s). Registry, environment variable, function call?

|||

SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup

This registry entry holds the DTS install location for 32-bit. I don't have my 64-bit machine available, so I'm not sure if it is the same for 64-bit. I'll check tomorrow.

|||I've just tested this a little by creating files. Try sort files 1.txt, 2.txt, 21.txt, 3.txt, 33.txt, 31.txt. and msgbox the files in the order they are processed.... Not what you would want or expect for a sort.|||

The files contain date and time stamp.

C200729_82812.XML

C2007210_82812.XML

P200729_82812.XML

P2007210_82812.XML

They are FTP'd over and we decrypt them.

It's be nice if SSIS had the ability to decrypt PGP files too.

Then I wouldn't have to do all this werid batch stuff outside.

Foreach loop container Foreach File Enumerator sort files

How do you sort files from the Foreach loop container?

I love this component, except I can't get it to sort by filename.

Nice to know how to sort by timestamps also.

Dave

Cylon2005 wrote:

How do you sort files from the Foreach loop container?

I love this component, except I can't get it to sort by filename.

Nice to know how to sort by timestamps also.

Dave

You can't sort the foreach loop container. My experience is that it always *does* sort by name, but I've never trusted it.

One thing you can do is with a few steps. First use your foreach loop container to get the list of files, and then populate a database table. Then, outside of the foreach loop, use an execute SQL task to select from that table using an ORDER BY. Load an object variable with that result set. Then use a second foreach loop to spin through that object variable (ADO.net recordset). From here you can perform the work you desire.|||I was about to suggest the same; puting the file names in a table give you control over the order....|||

Thanks. It sounds like a lot of work for something that should be a property setting.

|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

Post your feedback at http://connect.microsoft.com/sqlserver/feedback|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

I would agree. Perhaps you could post a suggestion at connect (http://connect.microsoft.com/sqlserver/feedback/)

Your original mail said you wanted to order by filename. By default, this is what it does. I'm a little bit more trusting than Phil as well - I've never seen it do anything different

-Jamie

|||

Rafael Salas wrote:

I was about to suggest the same; puting the file names in a table give you control over the order....

This SQLCLR sproc *might* help you do this:

Replacement for xp_getfiledetails

(http://blogs.conchango.com/jamiethomson/archive/2006/08/24/4400.aspx)

-Jamie

|||

Jamie Thomson wrote:

I would agree. Perhaps you could post a suggestion at connect (http://connect.microsoft.com/sqlserver/feedback/)

Your original mail said you wanted to order by filename. By default, this is what it does. I'm a little bit more trusting than Phil as well - I've never seen it do anything different

-Jamie

The only reason I've never trusted it is because I've never had to. I haven't had a situation yet where I've been required to process the files in order. My requirements to date have been to process all available files in the directory, order irrelevant.

However, the OP is claiming that it doesn't order by filename. I'm curious to know two things, Dave: 1 - What are your filenames? 2 - How have you confirmed that they are not being processed in order?|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

Well, as discussed, it isn't. Have you posted anything to Connect?

-Jamie

|||A ascending/descending Sorted Files ForEach enumerator would be a fairly straight-forward extension of the ForEach Directory enumerator included in the set of SQL samples (SqlServerSamples.msi). For example, I added sorting on properties like name (not full path), extension, size, dates, and a regular expression on the file name itself. The included enumerator is for directories, but modifying it to return sorted files was not much of a change.

The included enumerator sample is "%ProgramFiles%\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\ForEachDirectory Sample", which has both VB.NET and C# versions.

After building the sample, the new enumerator shows up in the For Each container's enumerator list box as "For Each Directory CS", and it has a UI included. Property expressions (on the enumerator itself) may be used as well, as below. The actual enumerated object is an ArrayList of directories. No, this enumerator is not sorted by default, since it calls GetDirectories() on a DirectoryInfo object. Point is, its "easy" (for those with a .NET framework background) to add sorting to it and almost all of it is already built.

Example expressions on enumerator
RootDirectory =>3 "c:\\"
RootDirectorySourceFile => 3 (DirectInput)
EnumerateSubFolders => false

To install the enumerator, modify the project's (in this case C#) post-build event to write the enumerator assembly (which actually contains the enumerator's UI as well) to the Global Assembly Cache and also into the For Each Enumerators directory.

xcopy /y "$(TargetPath)" "%ProgramFiles%\Microsoft SQL Server\90\DTS\ForEachEnumerators"
"$(DevEnvDir)..\..\SDK\v2.0\bin\gacutil.exe" /if "$(TargetPath)"

The second step (gacutil...) is an attempt to write the enumerator to the Global assembly cache. Global assembly cache installs can also be done by copy and paste of the dll (pointed to by $TargetPath) into the "%windir%\assembly" folder.

|||

Wow. Fantastic stuff Jaegd. If I have time I might try and have a play with that later - see if I can get it sorted any differently. Unless you've done it already (which it sounds like you might have done)?

-Jamie

|||

Yes, I have built the sorted files enumerator. The part I'm not sure about is how to build a platform independent installer for SSIS widgets (e.g. an installer that writes to the correct locations on x64 and x86). Installing to the Global assembly cache is no problem.

Perhaps Darren can answer this one or point me in the correct direction. What are the steps necessary to acquire the correct location(s) for a platform indepedent SSIS "widget" install (task,component,enumerator,logger,connection manager). I'm not sure where you can get a platform independent location(s). Registry, environment variable, function call?

|||

SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup

This registry entry holds the DTS install location for 32-bit. I don't have my 64-bit machine available, so I'm not sure if it is the same for 64-bit. I'll check tomorrow.

|||I've just tested this a little by creating files. Try sort files 1.txt, 2.txt, 21.txt, 3.txt, 33.txt, 31.txt. and msgbox the files in the order they are processed.... Not what you would want or expect for a sort.|||

The files contain date and time stamp.

C200729_82812.XML

C2007210_82812.XML

P200729_82812.XML

P2007210_82812.XML

They are FTP'd over and we decrypt them.

It's be nice if SSIS had the ability to decrypt PGP files too.

Then I wouldn't have to do all this werid batch stuff outside.

Foreach File Enumerator - is absent in Foreach Loop Component

Hi,

I have no "Foreach File Enumerator" oprtion in the Enumerator Property of the Foreach Loop Component.

I have this enumerator in the c:\Program Files\Microsoft SQL Server\90\DTS\ForEachEnumerators\ folder.

Also I check it in the GAC - it does not here. I try to execute gacutil.exe -iF ForEachFileEnumerator.dll, but it is failed with "Failure adding assembly to the cache: The module was expected to contain an assembly manifest." Seems it is not managed enumerator.

Please help me.

Also information on how to regeister unmanaged enumerators are welcome!

Did you search the forums?

From the thread, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=608235&SiteID=1, you need to review this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;913817

Also, a user has posted his experience with the same thing: http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!203.entrysql

Foreach ADO Enumerator?

my setting in foreach component like below shown

Enumerator: Foreach ADO Enumerator

ADO Object source variable: user::strsql

strsql like "select distinct name from table"

Enumeration mode: Rows in the first table

my goal: transform data according to the distinct every name

however execute error

prompt: Variable "User::strsql" does not contain a valid data object

pls help,thanks in advance

The foreach ADO Enum, needs and OBJECT type.

You need to create a Execute SQL Task with the SQL you want, then asign the return to the object variable. Then use the object as the ADO Object.

See this example: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

FOR XML performance question

I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source.
The older current component requests this data using an ADO recordset and
then manually converts this to XML.
I have run several performance tests comparing the 2 and on narrow and
medium width tables I have found that the performance gain is massive (appro
x
80% gain). However, when I run the 2 on very wide tables, ones which contai
n
text/ntext columns, FOR XML only performs about 10% better pulling back 1 ro
w
but pulling back 20 rows it becomes over twice as slow as the older componen
t.
Can anyone suggest why? Or even better, any methods/tips that could improve
performance in this instance?
Thanks in advance.I'm cross posting this to microsoft.public.sqlserver.xml, a more appropriate
forum for this question.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:F314E284-6E34-403C-94C1-B307682B1418@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.|||I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source.
The older current component requests this data using an ADO recordset and
then manually converts this to XML.
I have run several performance tests comparing the 2 and on narrow and
medium width tables I have found that the performance gain is massive (appro
x
80% gain). However, when I run the 2 on very wide tables, ones which contai
n
text/ntext columns, FOR XML only performs about 10% better pulling back 1 ro
w
but pulling back 20 rows it becomes over twice as slow as the older componen
t.
Can anyone suggest why? Or even better, any methods/tips that could improve
performance in this instance?
Thanks in advance.
----
In addition to the above I have done some further investigation. On a query
which returns the top row from a table the FOR XML method performed 83.5%
faster than the recordset version. However, when I run a where query which
I
know returns a single row the FOR XML method performance plunges and is
actually 6% slower than the recordset version.
Is SQLXML just one of those things which seems like a great idea but has no
real practical use in an enterprise environment? I find it very frustrating
that its performance is superb in some situations but is so awful in others.
Is it a work in progress?
That said, are there any resources which discuss various ways to pull data
from SQL server 2000 as(and convert to) XML format? Surely there is a bette
r
way than using a ADO recordset as described above?
Thanks.|||Hi Lee
This is hard to answer without having more specifics.
How does your FOR XML query look like? How does it compare to the previous
query, what indices do yo have on it?
Etc.
Best regards
Michael
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.
> ----
> In addition to the above I have done some further investigation. On a
> query
> which returns the top row from a table the FOR XML method performed 83.5%
> faster than the recordset version. However, when I run a where query
> which I
> know returns a single row the FOR XML method performance plunges and is
> actually 6% slower than the recordset version.
> Is SQLXML just one of those things which seems like a great idea but has
> no
> real practical use in an enterprise environment? I find it very
> frustrating
> that its performance is superb in some situations but is so awful in
> others.
> Is it a work in progress?
> That said, are there any resources which discuss various ways to pull data
> from SQL server 2000 as(and convert to) XML format? Surely there is a
> better
> way than using a ADO recordset as described above?
> Thanks.|||The query is a simple :-
SELECT stuff
FROM table
WHERE condition (optional)
FOR XML RAW
There is a single index on the primary key of the table and that was the
field I did my where clause on as described in my above posts.
"Michael Rys [MSFT]" wrote:

> Hi Lee
> This is hard to answer without having more specifics.
> How does your FOR XML query look like? How does it compare to the previous
> query, what indices do yo have on it?
> Etc.
> Best regards
> Michael
> "Lee" <Lee@.discussions.microsoft.com> wrote in message
> news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>
>