locked
Issue in For-each Loop Container RRS feed

  • Question

  • Hi All,

    I have used for-each loop container for loading excel sheet contains multiple sheets with same structure. It is loading data into SQL table even there is no data in sheets.

    Could any one help me on this?

    Regards,

    Ven

    Monday, August 10, 2015 11:13 AM

Answers

  • Hi ven_2014,

    According to your description, the issue is that it is loading data into SQL table even there is no data in excel sheet when you loading data from multiple excel sheets to a SQL table.

    After testing the issue in my environment, it loads data perfectly. The following similar thread is for your reference:http://stackoverflow.com/questions/7411741/how-to-loop-through-excel-files-and-load-them-into-a-database-using-ssis-package. You can just create one variable SheetName with one Foreach Loop Container based on the thread to achieve your requirement.

    If the issue is still exist, please provide us more information about your steps. So that we can make further analysis and help you out.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Tuesday, August 11, 2015 9:16 AM

All replies

  • Hi Ven,

    Are you following approach.

    http://www.codeproject.com/Tips/378129/Dynamically-Configure-Excel-in-Foreach-Loop-Contai

    http://www.codeproject.com/Tips/395541/How-to-load-data-from-multiple-Excel-sheets-to-any

    Please follow the above approach, I am sure you won't face any problem.


    Please Dont forget to mark as answer. It helps others to find relevant posts to the same question. Milan Das

    Monday, August 10, 2015 1:02 PM
  • Hi All,

    I have used for-each loop container for loading excel sheet contains multiple sheets with same structure. It is loading data into SQL table even there is no data in sheets.

    Could any one help me on this?

    Regards,

    Ven

    Hi Ven,

    In order to filter blank rows in the sheet, we need to set the data access mode in the OLEDB Source as "SQL Command from Variable". You might be using the sheet name from the variable. Instead of doing that add another variable "User::SQLCommand" as string and set the expression as,

    "SELECT * FROM " + @[User::SheetName] + " WHERE ID <> ''   "   

    Finally select the variable name "User::SQLCommand" from the variable dropdown list in the OLEDB Source Editor.


    Regards, RSingh


    Monday, August 10, 2015 2:27 PM
  • Hi ven_2014,

    According to your description, the issue is that it is loading data into SQL table even there is no data in excel sheet when you loading data from multiple excel sheets to a SQL table.

    After testing the issue in my environment, it loads data perfectly. The following similar thread is for your reference:http://stackoverflow.com/questions/7411741/how-to-loop-through-excel-files-and-load-them-into-a-database-using-ssis-package. You can just create one variable SheetName with one Foreach Loop Container based on the thread to achieve your requirement.

    If the issue is still exist, please provide us more information about your steps. So that we can make further analysis and help you out.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Tuesday, August 11, 2015 9:16 AM