September 24, 2008 at 2:31 pm
hope attached example will help you a little.
September 24, 2008 at 3:42 pm
you have three files including the dummy one. Are they all in the same folder at the time when you execute the foreach loop container?
For my case I only have one file in the folder - always.
I even tried and changed my sample file to not include the data; if the sample or dummy file AND the file with data are in the folder it works; once i removed the dummy file or sample file from the folder it broke.
So i guess the trick is that you have to leave the dummy file in the folder? While the dummy file has no data but heading you're not loading anything but the real file.
But I need to move the file to another archive folder after it's loaded.
i also have the file system task in a foreach loop container. Once I execute it then both files are moved to the archive folder including the dummy one while I need to keep the dummy one in the original folder for the next load to succeed.
September 24, 2008 at 5:25 pm
when you look at my package, you see that it skips the dummy file if checkdummy is false. if you use script task or archive file to move your file to another, it will not move dummy file. it only move the file that was loaded to database. So, your dummy file will be there always. hope you got it works.
September 25, 2008 at 12:05 am
South African Public Holiday, so didnt get a chance to look at this posting.
I am confused here....
So you want the for each file enumerator to process even if there is no file in the folder? That is easy, simply make sure that the delay validation is set to true. This means that it wont give a warning when there is no file to process.
No need for a dummy file in other words.
In any event, I will look at your file and see if I can spot the issue. This all is under the proviso that the file layout consistently remains the same.
~PD
September 25, 2008 at 1:20 am
I had a look at your package, and as I said before, the connectionstring means squat in the for each file enumerator.
If you look at Excel Connection Manager 2, there is an expression on the connectionstring. Let the connectionstring take care of itself. I suggest you delete this expression.
You want the expression on the excel file path. You need to add FilewejustFound here, no other funky expressions required.
Quick question, I see you enumerate once on the dataflow, execute a proc and enumerate a second time on moving the file.
Why not enumerate once only, perform data flow, execute proc and move in the same enumerator.
Try the above and let me know, if you still get stuck, post with a revised package.
~PD
September 25, 2008 at 7:09 am
PD
your advice is awesome.I can get rid of dummy file. Thank you . thank you.
AM
September 25, 2008 at 8:07 am
I only want the enumerator to process only one file each week when the package runs. And yes I did set the DelayValidation to True.
And you can't delete the expression and leave the connectionstring property on; you would get error if you do so.
Also, you can't put the variable @User::FileWeJustFound in the Excel File Path box. It's not allowed.
I'm almost about to give up. MS made such a simple thing so complicated. I just don't understand why the way I did it works perfect for flat file then why not Excel and it works for multiple files but not one file. Plus, I added the header row, there was no header row in the original Excel. It doesn't even work for excel with header row let alone excel without header row.
September 25, 2008 at 8:46 am
PD, Even if I in the properties of Excel Connection Manager 2 I changed from connectionstring to excel file path and used the variable FileWeJustFound it doesn't work. I tried this days ago. (in my previous post I misunderstood you by thinking that you meant the excel path from the edit screen from the Excel Connection Manager 2)
At this point i'm almost pretty confident that it doesn't work for one file. Have you tried that yourself since you are so confident that it works? Just put one file in the folder and that filename is different from the sample file you used while designing the package. Tell me you don't get any error?
Thanks very much everybody for putting time into discussing this question!!
September 25, 2008 at 9:03 am
Jenny,
Look at this package. All you need to do is change your database connection,variables and its expression.
1. change connection to your database
2. change variables values : SourcePath,Dstpath and FileName(File name can be anything with .xls)
ConnectionString for your excel file connection manage will be like this
3. change excel connection manager to
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::SourcePath]+ @[User::FileName] +";Extended Properties=\"Excel 8.0;HDR=YES\";"
4. For loop collection, choose name and extenion only.
Good Luck.
September 25, 2008 at 9:05 am
one more thing . Change DelayValidation of for loop container to True. It does work with one file or no files in the folder. Don't give up. I know you are almost there.
September 25, 2008 at 9:45 am
ayemya, Thanks so much for your continued help!! Just one change made it work.
The most import part which you mentioned and which I missed is to change the DelayValidation for CONTAINER to be True also. I have that set to True only for the Excel Connection Manager as that's what's mentioned in the link you provided and nowhere else it's mentioned that the same should be done for Container. So if you only have one file you have to have that set to True for the Container also from the Container's property page. I neglected this part without even thinking further as the first example you gave me also had that set to false for Container.
Thanks again and I think we can close this now.
September 25, 2008 at 9:57 am
I am glad that you did it. Cool!
September 26, 2008 at 4:23 am
Glad to hear you finaly got it Jenny. Take the rest of the week off.
September 26, 2008 at 8:00 am
Thanks! I hope so, but can't, busy with new ssis proj again. You'll see more questions posted by me. For new ETLs we'll use SSIS, all our old ETLs we use DTS and we just leave it like that without the need to convert them to SSIS now. Can't imagine if we convert them all and i'm the only person working on ETL.
Again thanks all for the valuable input!
September 28, 2008 at 11:50 pm
I was off on Friday and read all the posts.
Congratulations Jenny on getting this to work. Good luck on your SSIS projects!
~PD
Viewing 15 posts - 31 through 45 (of 53 total)
You must be logged in to reply to this topic. Login to reply