April 22, 2011 at 12:46 pm
I've got The ForEach Loop set up to loop through a folder on the local server
Config:
- Foreach File Enumerator
- Folder: F:\ETLDrop\work
- Files: RRINVHDR*.dat
- Retrieve File Name: Fully Qualified
- Variable Mapping: User::RRINVHDR
Variable Mapped:
Name: RRINVHDR
Scope: Package Level
Data Type: String
Value: F:\ETLDrop\work\RRINVHDR.dat
I can get many files name RRINVHDR.dat and they're named as such:
RRINVHDR.dat
RRINVHDR1.dat
RRINVHDR2.dat
When there is more than one file in the folder and the Loop completes successfuly and loops again, I get:
Description: The connection "F:\ETLDrop\work\RRINVHDR.dat" is not found. This error is thrown by Connections collection when the specific connection element is not found.
It's almost like when the 2nd interation of the loops begins, it still has the original file name.
Has anyone else encountered this?
I have attached the package in hopes someone can assist (renamed to .txt of course)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 22, 2011 at 2:40 pm
This has been resolved. In case anyone's interested the issues lays with setting the variable beforehand.
Variable Mapped:
Name: RRINVHDR
Scope: Package Level
Data Type: String
Value: F:\ETLDrop\work\RRINVHDR.dat
The value should be set to an empty string. In the DELETE File portion of Foreach Loop in the package, the Delay Validation must be set to TRUE, otherwise it will attempt to look back at the variable for iterating through the next enumerator in the loop itself.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 27, 2011 at 8:48 am
Well I thought this was fixed.
Randomly it appears to rear its ugly head.
Again, everything in the package runs successfully (all GREEN) yet there are errors with the foreach loop. The container picks up the file and processes it just fine, but appears to loop again looking for the same file (at least this is how it appears)
The exact error is:
Error: 0xC001000E at A-Tables: The connection "F:\ETLDrop\work\AFFIL.dat" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Does anyone have any suggestions?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 27, 2011 at 9:22 am
I have run into problems with this myself.
They all went away when I decided to just move each file to an Archive folder in the ForEach Loop, and then delete all files from the processed or Archive folder after the Loop was complete.
Sometimes the connection object does not "let go" of the file before the Delete File process tries to execute.
However moving the file to another folder in the same folder the Loop runs in has never failed for me.
I have found issues with deleting all the files in that folder using a File Management object in the same package. However I have made a generice file clean-up package that uses config file settings to delete files in a date range or all dates from any folder in the config.
Let me know if this helps. I got a LOT of tricks and experiance with Flat File and excel file SSIS packages.
Even had to write a .net program to create and publish Excel files to SharePoint for dynamic KPI's. That was a really fun work around.
April 27, 2011 at 1:21 pm
I disabled the DELETE file control and replaced it with a simple script and it appears to have worked. Go figure :S
I really don't get this SSIS...what on earth did MS do to make half the things work and the other half a complete mess...DTS was so much better in my opinion (at least I don't recall the headaches I have with SSIS). So far half of my entire SSIS project is Script Tasks and Execute SQL Tasks because I've had so many issues getting things to work (seems to defeat the purpose altogether eh?)
Thanks for the advice, it helped!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 27, 2011 at 1:34 pm
SSIS was designed to be like Power Center, which also has a lot of buggy stuff if you don't follow thier ETL rules.
I like SSIS becuase it solves Many issues that the VB6 VBA DTS code base had.
SSIS was also mostly partner tested, which means that certain ETL rules and guidelines that are not used by everyone were in place.
Things like Archive files after loading in a loop was tested, but deleting the active file with a foreach loop was considered.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply