Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error while importing multiple sheets of same excel into one table using ssis. Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 3:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 2:36 AM
Points: 2, Visits: 28
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.
Post #1443168
Posted Wednesday, April 17, 2013 11:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 2:36 AM
Points: 2, Visits: 28
I tried using foreach loop. But package is not successfully completed after debug the package. And there is no errors.

Where is the problem?


  Post Attachments 
1.png (6 views, 177.81 KB)
2.png (5 views, 162.71 KB)
3.png (4 views, 165.31 KB)
Post #1443606
Posted Friday, April 19, 2013 8:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 35, Visits: 119
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.
Post #1444445
Posted Monday, April 22, 2013 12:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:15 AM
Points: 79, Visits: 243
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
Post #1445114
Posted Monday, April 22, 2013 3:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 35, Visits: 119
Great example. I will definitely try to work your example tonite. Thanks for sharing it Vikas.
Post #1445197
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse