Need help with ForEach Loop

Question

Need help with ForEach Loop

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

You're ignoring bad rows?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Janice Kelly SSC Enthusiast Points: 119 More actions · Answer 2

The Max Error Count on the parent objects is what I was missing also. My package is now running

John M., can you explain further about the Event Handler Propagate Variable? Where is this located?

John M.-477553 Grasshopper Points: 14 More actions · Answer 3

Jeff, in my situation I could ignore the bad rows and just segregate the bad files into another folder. Our urgent need is to process many files, from which we do not have a very large possibility for bad files. In the long term, I'm sure we'll want to add some smarts into the OnError event handler to work with the bad stuff.

Janice, the easieast way that I've found to work with the appropriate variables is to navigate down to them through the Package Explorer tab. After you've created an event handler on your data flow task, you'll be able to expand the Event Handler and OnError folders. The System :: Propagate variable is in that Variables folder.

This stuff is very new to me also, I found this Propagate variable method through much searching through the SQL Server 2005 Books Online. I'd be very curious to know if this is the best approach, but it seems to be meeting my needs for now.

Janice Kelly SSC Enthusiast Points: 119 More actions · Answer 4

Thanks John. It's amazing what you can find when you know where to look. I didn't know all those variables were available

Calvin Brown SSC Rookie Points: 30 More actions · Answer 5

Hi All,

I am afraid I am back with another puzzle. I have a package with a ForEach Loop container which loops thru a set of files (.mdb) in a folder to and imports them to SQL server. I have mapped the Directory property of the container to a variable and have created a package configuration file so that I can modify the location of the folder without having to recompile the package.

The problem I am facing is using a UNC path to point to the folder. I need to use a UNC path as the folder resides on a different box (webserver) from the database server. If I use the full non-UNC path (i.e. d:\inetpub\wwwroot\...) everything works perfectly. If I use the UNC share name the package does not find the files and returns the following warning.

Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

Does SSIS not support UNC paths for the Directory property or am I doing something wrong?

Thanks again for your help

Calvin

Calvin Brown SSC Rookie Points: 30 More actions · Answer 6

As an additional side note, I am still in development so the DB and Web server are on the same box. I just shared the folder on the local machine and trying to access it from SSIS as \\localhost\brmf_uploads

So everything is local at the moment.

Robert O'Byrne SSCommitted Points: 1967 More actions · Answer 7

I would remove the possibility of the unc path being an issue by using a local folder on the server. This will make sure your tasks and packages work properly. Then try adding the unc pathing. At this point you could pop the variable paths to the screen with a msgbox if you are setting variables up in a scipt task. Then you know if you've built the path properly.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

The problem with the UNC is that the server must be logged in as a "super domain user" than can "see" those other boxes and directories...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions