Post reply

For each file enumerator processing the same file

  • August 31, 2010 at 11:19 am

    #226433

    Hi.

    First off, i have uploaded a screen shot of bit of my SSIS package that is giving me a headache.

    This part basically works its way through a collection of file, decided whether they should be processed or not and then does the processing. If the files are in the correct format then this works perfectly.

    As part of my error handling i am trying to catch files that should be processed but are actually blank. There are two tasks ("Reject file 3" and "Move error file 3") that should do this. They work under the assumption that the dataflow tasks will throw and error if the file is blank (I.E there are no columns defined in the work sheet).

    It all behaves as expected if there is one file in the directory (i.e. one file that is designed to be processed or one file that is designed to fail) but if there is more then one file then things get a little strange.

    Say in the directory there are two files, one is designed to be processed (by this i mean pass all the validation) and one that is designed to fail (just a blank worksheet). I would expect to have one record set imported into the database and one file rejected. Instead no files are rejected and the dataset is imported twice. If in the directory there are three files, one that passes the validation and two that fail, then i get the same dataset imported three times and so on

    I am using the file name variable that the for each loop passes through to link up with a record in the database and to send out emails so i know that it is processing the right file.

    Any thoughts?

  • August 31, 2010 at 2:03 pm

    #1214590

    I think you should look at your loop...

    1 file:

    everything is ok

    2 files:

    1 is ok

    1 failes

    1 is ok

    1 failes

    3 files:

    1 is ok

    2 failes

    1 is ok

    2 failes

    1 is ok

    2 failes

    I think you should invest what you have disabled at the end when testing local.

  • September 1, 2010 at 1:21 am

    #1214764

    Thanks for your reply but the disabled items do not effect the outcome of the loop. All they do is move or delete the files once processed.

  • September 2, 2010 at 4:10 am

    #1215478

    I think you should look at your for each loop, this is running for each file, so if you have 3 files it's running 3 times.

  • September 2, 2010 at 4:20 am

    #1215480

    rogierpronk-1038114 (9/2/2010)

    I think you should look at your for each loop, this is running for each file, so if you have 3 files it's running 3 times.

    Correct, but if i have three files it will run once for file1, once for file2 and once for file3. Not three times for file one.

  • September 5, 2010 at 5:17 pm

    #1216730

    Anyone else got any ideas?

  • September 6, 2010 at 5:10 am

    #1216863

    Ok, i have managed to replicate the behaviour in a simple package that is easy to test.

    I have package that contains a foreach loop container. That container is pointed at a location where i have two excel files, one correctly formatted and one that is blank.

    Within the container i have set up a dataflow task that has an excel connection as the source and an OLE DB Connection as the destination.

    I have tested this with just one file and it works fine. With two valid files it appears to work fine but actually just inserts the same data twice, which as rogierpronk-1038114 correctly pointed out, means a problem with my loop. (Sorry 🙁 )

    I have attached the test package so if someone could tell me what is wrong with my loop i would be most appreciative.

    Thanks

  • September 9, 2010 at 2:25 am

    #1218433

    I managed to sort this problem out a few days ago.

    It turns out that i was not setting the connection string for the excel connection properly.

    I did learn an interesting lesson though, when setting the connection string as a variable for *.XLS files as an expression, it should look something like this.

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FilePath] + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"

    And then setting in for *.XLSX files, it should look like this.

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::FilePath] + ";Extended Properties=\"Excel 12.0;HDR=YES\";"

    Notice that the jet engine is no longer used.

    Now maybe i was doing something wrong but i just could not create a connection to the *.XLSX file using the Jet engine. I got the error message "Could not find installable ISAM".

    I only tried using the Ace engine by chance and it worked perfectly.

    There is nothing on the internet about this so maybe this will help someone else.

Viewing 8 posts - 1 through 7 (of 7 total)

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