ForEachLoop Container and Variables

Hi Guys

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

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

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

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

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

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

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

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

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

Thanks

Scott

Hi Scott,

We're all still learning SSIS.

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

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

Code Snippet

INSERT INTO Vendors

(Code, Supplier)

VALUES(?, ?)

You could then supply Parameters:

Code Snippet

VariableName Direction DataType ParameterName ParameterSize

User::Code Input Int 0 -1

User::Supplier Input VarChar 1 -1

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

Hope this helps,

Andy

|||

Scott,

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

|||

Hi Rafael

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

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

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

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

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

thanks again

Scott

|||

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

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

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

This thread has some examples

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

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

Here is what I am attempting to get accomplished. I have an SSIS package that contains a Foreach loop container. This container executes a number of SQL tasks in order: SQL Task 1, SQL Task 2, SQL Task 3.

if the SQL task 1 succeeds it should flow on to SQL Task 2 and 3. This works fine when the SQL tasks do not fail...

In the event of any SQL Task failing control should flow to a send mail task to alert about the failure. Next the Foreach loop container should go to the next enumeration in the Foreach loop container and start the next new SQL task 1. So far I have been able to get the control to flow to the send mail task when a SQL Task fails. What does not work is when one SQL Task fails the entire Foreach loop fails and does not move to the next enumeration. It should only fail the package and move on.

Any help would be appreciated....

Please check the FailPackageOnFailure and FailParentOnFailure properties of ForeachLoop Container as well as Execute SQL Task Object. if any of them is defined as true then set it to false.

If this will not help you let me know.

|||

I have checked these properties and i have both of them set to 'False'... Still Fails...

Any other suggestons?

|||I suppose you could ForceExectionResult = Success|||

Hi Steve,

Here is the Solution:

1. For Foreach Loop container set "ForceExecutionResult" to "Success" so that this container never failes on execution.
2. For precedence constraint of all your tasks in Foreach loop container set the "Value" property as "Completion" so that next SQL task get executed only on COMPLETION of previous SQL task and not SUCCESS of previous one.
3. Set the "FailParentOnFailure", "FailPackageOnFailure" property to "False" for all SQL Task in container. Set "ForceExecutionResult" property to "None" for all SQL Task in container.
4. I am sure you are using "Failure" precedence constraint to send mail task from SQL Tasks.

I created a test package to try this scenario and it works :)

Thanks
Mohit

Foreach Loop is killing me!!!!!!!!!!!!!!!!!!!!!!

I would like your help with the Foreach loop container. Boy, am I having issues with using it for looping through an ADO.NET dataset or what!?!? My control flow has a data flow task that is executing a Data Reader task (creating a .NET dataset for me). Now I go back to the control flow and add a Foreach loop container to loop through each record in my dataset. But which type of the Foreach loop container should I be using? I see an option to use Foreach ADO.NET schema enumerator but not sure how to configure it. Also I used the Foreach ADO option, but setting up variables to go through every column is an absolute pain in the you know what!!?!! I have about 200 columns and I want an easier way to refer to those columns in my transformation phase.

Hope you are able to help me out here.

Jamie posted an answer on another thread!!?!!?!!?!!?!!!!!!!!?!!!!!

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

Foreach loop for DBASE IV problem

I've followed the steps on how to use the ForEach loop container using a Flat Text file and it's working fine. I tried reading the text file and put it in my SQL table. However, I have some DBF files as well and the ForEach loop does not work at all with my DBF files.

Here is my scenario:

A connection manager pointed to C:\DBF_FILES

On the Control Flow: a Foreach loop container with Foreach file enumerator. (checks for IFUL*.DBF files and retrieves the Fully qualified name.

Within the foreach loop container is my DataFlow (import)

Within the dataflow:

OLEDB datasource
data access mode: Table name or View Name variable
Variable nam: username::Filefound

Now from this point on, I'm already having problems, here is the error:

An OLEDB error has occured.
An OLEDB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers.
Data source name not found and no default driver specified.
Error at data flow task...

There is also some error that say i don't have a destination table specified. Plus the error is on the OLEDB datasource not having a destination table? I don't understand this part.

Please help!

From what I understand of your post in my thread, I think what you want to do is simple. On the Foreach loop set the Folder to C:\DBF_FILES, set Files to *.dbf and select the "Name only" radio box in teh Retrieve file name.

That should set your configured variable in the Variable mapping to the name of the dbf file for each iteration. In your DataFlow set the data access mode to "Table name of view name variable" and the variable to the one you used in the Foreach variable mapping.

|||

Oh you should note, that you can only open one type of DBF file with that OLE DB Source. Meaning, all the files will need to have the same schema. If you have files with different content your going to either have to select them and handle the differences before this part. I have a couple ideas but it really depends on what you situation is.

|||

I think I see your problem, I actually put something together really quick to test that idea. I'll have to think about it a bit more later.

|||

Thanks Dan,

Yup, i only have the same schema for all the DBF files that I'm trying to retrieve. If I test the data flow with an implicit database name, it works fine. It's when I put it to the Foreach loop that I'm having the problem.

When I test the foreach loop with flat file, it works fine, but when I use the OLEDB, that's when it fails, I think I am following everything that needs to be done, it's just that all the samples for foreach loop has flat files for data source, and I expect it to be the same to the other types of data source. Maybe there is a missing link when the data source is not a flat file...

|||

Opps, I didn't have my Connection manager pointing to the right directory. What I described does in fact work. I'll try and post up some screen captures of how I have the package setup and perhaps you can see what isn' t configured right on your end.

This is about what I can write-up at the momment.

http://www.infrandom.com/42/Blog/tabid/53/EntryID/2/Default.aspx

|||

I've been doing and followed the steps exactly as you have laid out. My problem is on the OLEDB source. When I fil up the: OLEDB Connection Manager, data source mode and the variable name, when I click OK, it gives me the error:

Error at Data Flow task [OLE DB Source(431)] A destination table name has not been provided.

I really really appreciate the screen caps that you have provided. It really helps a lot. However, it is on that OLEDB source that I can't click OK due to the error, although my screen looks exactly like yours already.

Is there perhaps something that's not installed on my machine?

Thanks a lot.

|||

Another thing that I'm playing around just to be able to fix this is giving my variable a valid initial value. I am sure that the DBF file exists in the list but the error that I get when I do that is:

Opening a rowset for 'TBLNAME' failed. Check that the object exist in the database.

I tried TBLNAME.DBF, I tried TBLNAME only, but it just can't find it.

I made DBASE IV a version V but it said couldn't find installable ISAM. So I put back the DBASE IV.

On OLEDB Source:

The thing is I made my table access mode as table or view, and I choose TBLNAME from the list, I am fine. The problem is when i make my table access mode as 'table name or view name variable' and on the variable, i select my variable with my initial value as TBLNAME, it just can't find it.

I'm already lost. I gotta get some fresh air.

|||

View the variables, if you scroll the pane out, you can set the initial value of the variable in the designer. Give it a valid file name for a file in the directory. (There has to be a file present in the directory design time at least).

|||

I got that error when my Foreach loop directory and my Connection Manager directory were not point to the same directory. Once I updated to connection manager to the same directory I was able to run the package. Again, once you set the detault value of the variable just name no extension I think you should have it.

|||

Whew!!!

sometimes, a fresh air is really what it takes to get the mind cleared out.

Yup, I give my variable a valid file name. The problem was that only the DBF files were in that directory, not the index files. So I copied the index files as well, and it's now doing it's thing.

I followed everything that you have laid out on your screen caps.

Added a valid value to the variable. Made sure that the name retrieval is set to 'NAME only'. I set it to 'fully qualified path' at first, which contributed to the error. And finally, make sure that both the DBF and CDX files are present.

THANKS A LOT!!!

Really appreciate it.

|||

Glad you got it working!

sql

FOREACH Loop Container: Continue ?

With a ForEach container, configured to loop through files in a directory, if I have a problem with a file.. can I direct the loop to skip on to the next file?
I'm processing structured files, first record of each is some header info, body records are in the middle, and then the last record is a trailer containg a checksum
So, for each file in the directory, I split the records into three raw files, one for header rec(s) , one for body recs and one for trailer recs. (based on line numbers and using a conditional split to direct the records)
Then I start by processing the header recs in a dataflow.. if all goes well there I move on to the next dataflow to process the body recs from the DataRecs raw file.. etc...
I would like to do some validation at each processing step.. if a header rec fails validation say... then I'd like to just stop processing that file and move onto the next file...
Now, I don't see my validation throwing an exception... so its more that I'd decide (maybe using an Audit ) that the header doesn't pass validation.. then I'd like to put a record in an error table (with info about filename, source etc, not just content of the current data row)
But not sure what approach to take on this...
If there is an appropriate section in BOL please point me at it...
Thanks
PJ

OK, so a record could fail validation but won't necassarily cause an error, is that right?

If you can find a way of populating a boolean variable stating whether the validation was successful or not then you could put a conditional precedence constraint beween data-flow1 and data-flow2 to determine whether data-flow2 should execute or not.

-Jamie

|||

... and if data-flow2 shouldn't execute then the ForEach will just loop around?

PJ

|||

Ok... so have to figure out how to access global variables in a script task....

OR...

...could do a conditional split.. which carries out the validation checking...and has one output connected to an OLE DB Destination to store a valid header record.. the other output directed to a RowCount...

If the row count >0 then we have a validation failure.. so could set the global variable there... is there a way to send a row count to a package variable?

PJ

|||

PJFINTRAX wrote:

... and if data-flow2 shouldn't execute then the ForEach will just loop around?

PJ

Absolutely! (Unless you have any other OnSuccess precedence constraints from data-flow1). Once all work is done for that iteration then it'll go onto the next iteration.

|||

PJFINTRAX wrote:

a way to send a row count to a package variable?

PJ

Err yeah. That's exactly what it does (and the only thing it does). i.e. Store the rowcount in a variable :)

|||

Jamie Thomson wrote:

PJFINTRAX wrote:

a way to send a row count to a package variable?

PJ

Err yeah. That's exactly what it does (and the only thing it does). i.e. Store the rowcount in a variable :)

DOH! Well, we all know I'm only making this up as I go along :^)

Thanks

PJ

|||

sigh.. this is like pulling teeth... now another problem has reared its ugly head...

when i use the SQL Task to retrieve the ID of the FileHeader record (that I just inserted) into a package variable... I get a datatype error.. and can't figure out which bloomin data type to use...(for the variable)

In my DB table the ID is a bigint....

what on earth is the equivalent in SSIS datatypes.... I have literally tried em all.. only one that works is Object !!!

So, SQL Task is executing SQL query as follows "select MAX(ID) as ID from fileheader"

Table definition is :

CREATE TABLE [dbo].[FileHeader](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Source] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FileName] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LoadDate] [datetime] NULL CONSTRAINT [DF_FileHeader_LoadDate] DEFAULT (getdate())
) ON [PRIMARY]

So what should my variable type be if its to hold the result of the query?

PJ....

|||

Ahh what they hell.. I'll just use String.. that works....

|||Guys, I have the bigint problem again, can't just use a string this time...
has anyone any idea what the SSIS datatype equivalent of a bigint is at all?
Thanks
PJ
|||Is this just so obvious that nobody is bothering to answer?
|||

Probably cos they don't know. And I'm afraid I include myself in that. I don't have a SSIS instance to hand but as and when I do I'll try and take a look OK.

-Jamie

|||

Thanks Jamie,

but I have been through every single datatype .. and if I try to wedge a bigint into anything other than a string I get an error...

I would have thought an INT64 would work.. but no...

the bigint is an identity column in a table in my db... I'm a result set (which includes it) into an object and using that as a recordset using a foreach.. and I need to compare the ID column numerically.. to decide which flow to go down (ie If FiletypeID >12 and FileTypeID > 4 )

i suppose since the presedence contstraint is an expression I could try casting to a int64 maybe...

bit awkward tho.... would have thought this would all be easier... (like most SSIS things, heh)

PJ

ForEach Loop container issue!!!

Hi everyone,

I am having hard time with foreach loop container. The for each loop container in my package goes over all the rows in a ADO enumerator recordset variable and shows row values one by one in message box. The problem is that it just keeps printing the first row infinitely. Could anyone tell me what could be wrong?

Thanks in Advance,

Care to share the code on how you build the message box?|||

Praveen Dayanithi wrote:

Hi everyone,

I am having hard time with foreach loop container. The for each loop container in my package goes over all the rows in a ADO enumerator recordset variable and shows row values one by one in message box. The problem is that it just keeps printing the first row infinitely. Could anyone tell me what could be wrong?

Thanks in Advance,

Jamie Thomson has a sample package here:

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx

|||

Check this feedback:

ForEach enumeration of ADO recordset can cause infinite loop when using checkpoints

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125915

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 Loop Container does not iterate automatically

Hi,

Another classic example of something silly I must be doing

I have a "Foreach Loop Container" which uses a "Foreach ADO Enumerator".

The object source is a recordset variable.

Inside the "Foreach Loop Container", a lot of things are happening - some Execute SQL Tasks, Send Mail Tasks, Script Tasks and also a couple of Sequence Containers.

Now, I am getting 2 records in the recordset, but, the "Foreach Loop Container" executes just once.

Any Ideas?

Thanks in advance.

Regards,

B@.ns

There could be some criteria not being met in one of the tasks for the 2nd record. Just to test the loop execution I would recommend making a copy of your package and delete most of the tasks and leave behind just a couple of tasks; maybe an Excecute SQL Task and a Flat File Manager to write the records to (do not overwrite the file). You can then check if the loop is executing twice.

This may be a dumb idea but when you have a lot going on inside a loop it is hard to say what is going on with the loop control. I cannot see any reason why the loop would not execute for the second record.

|||

Hi,

Well, I didn't do much and the Foreach Loop Container is working now. It is indeed looping through all the available records.

No clue as to why and how.

Thanks for replying!

Regards,

B@.ns

sql

Foreach Loop Container does not iterate automatically

Hi,

Another classic example of something silly I must be doing

I have a "Foreach Loop Container" which uses a "Foreach ADO Enumerator".

The object source is a recordset variable.

Inside the "Foreach Loop Container", a lot of things are happening - some Execute SQL Tasks, Send Mail Tasks, Script Tasks and also a couple of Sequence Containers.

Now, I am getting 2 records in the recordset, but, the "Foreach Loop Container" executes just once.

Any Ideas?

Thanks in advance.

Regards,

B@.ns

There could be some criteria not being met in one of the tasks for the 2nd record. Just to test the loop execution I would recommend making a copy of your package and delete most of the tasks and leave behind just a couple of tasks; maybe an Excecute SQL Task and a Flat File Manager to write the records to (do not overwrite the file). You can then check if the loop is executing twice.

This may be a dumb idea but when you have a lot going on inside a loop it is hard to say what is going on with the loop control. I cannot see any reason why the loop would not execute for the second record.

|||

Hi,

Well, I didn't do much and the Foreach Loop Container is working now. It is indeed looping through all the available records.

No clue as to why and how.

Thanks for replying!

Regards,

B@.ns

Foreach Loop Container causes package to crash

We have a problem with a SSIS package containing a Foreach Loop Container that causes the package to fail unpredictably.

We are using a Foreach Loop Container to process records in a source table one by one. We do this by executing a SQL statement on the source table, putting the resultset in a package variable and using that variable as an ADO object source variable in a Foreach Loop Container. In that container, we do four things:

1) copy the record we want to process into a temporary table,
2) run a dataflow task on that temporary table to actually process the record,
3) truncate the temporary table filled in step 1 and
4) delete the processed record from the source table.

This part of the package validates and runs fine, but every now and then the package fails somewhere in the Foreach Loop Container without any useful notification. We cannot tell where exactly the package fails: it differs. It's often in the dataflow task, but not always. If we clean up the step in which the package fails and rerun it (such that the last row in the source table is processed again), it continues without a problem. Sometimes it stops in the middle of processing a specific record. If we leave the record in the source and process that record again, it processes fine without failing on that record again. So it's not one of the source records causing the problem. One time it will take a couple of hundred iterations before the failure occurs, the next time it might take less than a hundred.

Does anybody have any clue on what might cause this problem or what we can do to further investigate this?

Thanks in advance, Hans Geurtsen

Does "without any useful notification" mean that no errors are shown or that you don't find the error(s) useful. If there are errors then can you please provide them. Without the errors all I can do is hazard a guess. Perhaps you are encountering locking issues or perhaps there are memory problems due to fragmentation.

Thanks,

Matt

ForEach Loop Container and First Task

Hi,

My Foreach Loop container has 10 different task inside. I want to execute the first task only one time. I have a variable with increases for each repition. How can I put precedence contstraint on the first task so that it should execute only first time and other task has to execute all the time.

Thanks

You can add a property expression on the task that will set the Disable property to True/False accordingly

Thanks,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

You can add a property expression on the task that will set the Disable property to True/False accordingly

Thanks,
Ovidiu Burlacu

Far be it from me to disagree with Ovidiu (who knows far more about SSIS then I ever will) but I think a better approach would be to make all of the tasks execute after an empty sequence container. You can then put an expression on the precedence constraint that goes between the sequence container and the task you only want to execute on the first iteration.

-Jamie

ForEach Loop Container

I am using a SQL Task to load the data needed for the loop into a variable, then using a script task to perform calculations. Is it possible to update a table from the script task with the calculated values?.

Thanks
You can use System.Data and ADO.Net inside a script task. You could even use an ADO.Net connection manager. Just write some normal VB.Net code to do your data access.

I don't know what you are doing, but I would question this method. Why not do it all in a single SQL Task? Why use a SQL task to do the update? Why not do it all in a script task?|||I want to test the performance difference between a cusor in sql 2000 and the loop in sql2005.|||

I wouldn't recommend that as a use of SSIS... instead, consider revisiting what you do with the cursors to be done using the data flow task.

regards,
ash

|||Let me explain what I am trying to do. I have +/- 600 performance reports to calculate each with 40 odd items. If I'm on the wrong path please point me in the right direction. I would appreciate any help.|||

Do all performance reports have the same structure just different data? Are all the calculations identical as well?

I'm intruiged by the problem and can work with you on how to do this in most efficient manner, we can then let the group know what we found.

regards,
ash

|||

Yes the structure for all reports are the same, the calculations are dependant on the specific measure.

May I contact you offline and not involve the forum until a suggestion is ready

Foreach loop container

Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop Container, and I don't know how to process it with Table, means for each record of this table I have to import from one folder some text files. so Idon't know how to connect my table to Foreach Loop container.

Thanks

1. Use Exec. SQL Task to query the table
2. Store the result record set in a variable
3. Use FE Loop to iterate over the record set

|||So it sounds like you want to loop over some files and then import that

file am I right?

Would this help?

Looping over files with the Foreach Loop

(http://www.sqlis.com/default.aspx?55)

Allan

"Mbd@.discussions.microsoft.com" wrote in

message

news:a05fa88a-80ad-4525-a833-370c4473b67a@.discussions.microsoft.com:

> Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop

> Container, and I don't know how to process it with Table, means for each

> record of this table I have to import from one folder some text files.

> so Idon't know how to connect my table to Foreach Loop container.

>

> Thanks|||Hi ,

thanks for your help, but is not what I need, maybe my request was not clear, so my question is : Looping over table with the Foreach Loop,

thanks in advance for your answer.

Mdb|||Hi,

Thanks for your replay, please how to do the third part ,

3.Use FE Loop to iterate over the record set

thanks|||

? how to do the third part

- In FE Loop use Foreach ADO Enumerator
- As ADO Object source variable - choose the variable used in the step (2) above
- In Variables Mapping page you can map column #1 to variable ..., column #2 to variable ..., etc

sql

Foreach loop container

Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop Container, and I don't know how to process it with Table, means for each record of this table I have to import from one folder some text files. so Idon't know how to connect my table to Foreach Loop container.

Thanks

1. Use Exec. SQL Task to query the table
2. Store the result record set in a variable
3. Use FE Loop to iterate over the record set

|||So it sounds like you want to loop over some files and then import that file am I right? Would this help? Looping over files with the Foreach Loop (http://www.sqlis.com/default.aspx?55) Allan "Mbd@.discussions.microsoft.com" wrote in message news:a05fa88a-80ad-4525-a833-370c4473b67a@.discussions.microsoft.com:
> Hi, I'm trying to learn SSIS and currently i testing the Foreach Loop
> Container, and I don't know how to process it with Table, means for each
> record of this table I have to import from one folder some text files.
> so Idon't know how to connect my table to Foreach Loop container. >
> Thanks|||Hi ,

thanks for your help, but is not what I need, maybe my request was not clear, so my question is : Looping over table with the Foreach Loop,

thanks in advance for your answer.

Mdb|||Hi,

Thanks for your replay, please how to do the third part ,

3.Use FE Loop to iterate over the record set

thanks

|||

? how to do the third part

- In FE Loop use Foreach ADO Enumerator
- As ADO Object source variable - choose the variable used in the step (2) above
- In Variables Mapping page you can map column #1 to variable ..., column #2 to variable ..., etc