Post reply

multiple excel files

  • March 10, 2009 at 10:03 am

    #126868

    I followed link to import multiple excel files into sql table.

    http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html

    Issue-

    by default it needs , this file -

    C:\Projects2005\SSIS_Excel_Loop\SSIS_Excel_Loop\email_book_01.xls

    then it is importing multiple excel files

    C:\Projects2005\SSIS_Excel_Loop\SSIS_Excel_Loop\email_book_02.xls

    C:\Projects2005\SSIS_Excel_Loop\SSIS_Excel_Loop\email_book_03.xls

    How to modify this to take any file at start?

    meaning

    the next time I have only say

    C:\Projects2005\SSIS_Excel_Loop\SSIS_Excel_Loop\email_book_04.xls

    C:\Projects2005\SSIS_Excel_Loop\SSIS_Excel_Loop\email_book_05.xls

    but the package failing because the first file is not present.

  • March 10, 2009 at 10:38 am

    #956937

    What we have done is setup the data flow to use a generic name like DataSouce.xls.

    You actually need a file by that name to get the data flow setup, after that you can delete the file.

    Then in the for each file loop we store the actual file name in a variable, then rename the file to DataSource.xls

    Run the data flow. At the end of the loop rename the file back to its original name ( we also move it to a processed folder or just delete it).

  • March 10, 2009 at 10:41 am

    #956940

    can you share that task and variables to me? I m new to SSIS and need this to be done asap.

  • March 10, 2009 at 11:09 am

    #956958

    I'm guessing you have a for each loop that is setup as "Foreach File Enumerator"

    If not create one.

    Add a variable (v1) for storing the original name

    Add a variable (v2) for the generic file name and give it a value like DataSource.xls

    For Each setup:

    in the collection section (make sure enumerator is For Each File)

    For the folder choose the folder where the files are

    if looking at only excel files the specify *.XLS for the Files

    Retrieve file name as Fully Qualified

    on the variable mapping section hit the drop down and pick v1 leave the index as 0

    put a file system task inside your loop (first item in the loop)

    In the general section:

    Is Destination variable - True

    Destination variable - v2

    overwrite destination - false (to be safe)

    operation - rename file

    Is source path var - True

    Source variable - V1 (this is the incoming file name from for each loop)

    Now there will be a file with the generic name in you folder

    You can run the data flow using the generic file name as the source.

    when you are done with the data flow put another File System Task in the loop and set it up like the first one but in the reverse order.

    This should get you going

  • March 10, 2009 at 11:11 am

    #956961

    r

  • March 10, 2009 at 11:40 am

    #956978

    by doing this the first time data flow task is not working , it needs default filename for validation.

    How to avoid this?

  • March 10, 2009 at 11:54 am

    #956986

    you could set delay validation to True for the excel connection manager.

  • March 10, 2009 at 12:11 pm

    #956997

    yep it works thank you.

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

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