Error while importing multiple sheets of same excel into one table using ssis.

  • Hi,

    I am importing multiple sheets of same excel into one table using ssis.

    I am following below flow.

    Data flow task->Excel Source->Look up->OLE DB Destination.

    I have created a variable(User::Sheet123).

    Now, in Excel Source Editor, I am using variable name(User::Sheet123) in Data access mode(Table name or view name variable).

    Now the problem is, if i click on Preview, i am getting below error

    Index and length must refer to a location within the string. Parameter name: length(mscorlib).

    And there is no spaces in sheet names.

    My sheet names are

    Sheet1, Sheet2.

    Excel - 2007 and SQL Server - 2008

    Please help me.

  • I tried using foreach loop. But package is not successfully completed after debug the package. And there is no errors.

    Where is the problem?

  • Can you provide some more information on this SSIS package, you are working on. Some of us can try to replicate it on our end.

    Question: Is you different sheets in excel has same structure or its different. I can think of something like header table and detail table structure.

  • You can find a working example in this post.

    SSIS: How to loop through Multiple Excel sheets and load them into a SQL Table?

    Vikash Kumar Singh || www.singhvikash.in

  • Great example. I will definitely try to work your example tonite. Thanks for sharing it Vikas.

Viewing 5 posts - 1 through 4 (of 4 total)

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