tag:blogger.com,1999:blog-2303058199815958946.post6809558866566801267..comments2024-03-28T10:36:35.016+01:00Comments on Microsoft SQL Server Integration Services: Custom SSIS Component: Foreach Excel Worksheet EnumeratorJoost van Rossumhttp://www.blogger.com/profile/01125981589974671317noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-2303058199815958946.post-45096831918579002132014-01-24T19:03:07.795+01:002014-01-24T19:03:07.795+01:00Hi Jean-Pierre, I can not replicate your problem. ...Hi Jean-Pierre, I can not replicate your problem. Could you contact me via the <a href="http://microsoft-ssis.blogspot.nl/p/contact-me.html" rel="nofollow">form</a>. Then we can communicate via email... easier/faster.Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-57565515325630946932014-01-24T17:40:39.065+01:002014-01-24T17:40:39.065+01:00All my spreadsheets have different number of sheet...All my spreadsheets have different number of sheets in it, but the format of the data is the same. I have noticed that it does not reset the enumerator object. example; spreadsheet = 45 sheets & spreadsheet = 24 sheets. when the first file loop go to spreadsheet 2 the enumerator is still trying to import 45 sheets even though it only have 24 sheets.<br /><br />Any advice. Thanks for the fast replyJP Voogthttps://www.blogger.com/profile/02624200599036972830noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-34228939399777675212014-01-23T22:05:28.152+01:002014-01-23T22:05:28.152+01:00What you could do is add a script task in the loop...What you could do is add a script task in the loop. Then add the two string variables from the loops as ReadOnlyVariables and then hit the Edit Script Button. In the main method add the messagebox:<br />public void Main()<br />{<br /> // TODO: Add your code here<br /> MessageBox.Show(Dts.Variables["User::myFilePath"].Value.ToString() + Environment.NewLine + Dts.Variables["User::myExcelSheet"].Value.ToString());<br /> Dts.TaskResult = (int)ScriptResults.Success;<br />}<br />This will show which sheet is active and then you can check whether it exists and whether the format is the same as the other sheets. Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-73211087977532912942014-01-22T11:04:39.057+01:002014-01-22T11:04:39.057+01:00If I us this component inside of another Foreach L...If I us this component inside of another Foreach Loop with a file enumerator on. The first Excel file get process 100% but the second one always fails. It looks like it is trying to read as sheet that does not existx. Example Woorbook has 24 sheets, the error that gets displayed "opening a rowset for Sheet25 failed"JP Voogthttps://www.blogger.com/profile/02624200599036972830noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-33335811952753080942013-05-07T07:58:00.878+02:002013-05-07T07:58:00.878+02:00I assume you now how the foreach loop works. This ...I assume you now how the foreach loop works. This enumerator returns the excel sheetname. You can use that in the excel source or to create a select query with an expression on a string variable and then use that variable as a source.<br /><br />But you can only use this if the format of all sheets is the same.Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-25571272510054784462013-05-06T20:52:51.737+02:002013-05-06T20:52:51.737+02:00Can you please explain how this works to read the ...Can you please explain how this works to read the all data from excel? Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-23289118312621475652012-10-23T23:39:26.368+02:002012-10-23T23:39:26.368+02:00Hi Anonymous, What exactly are you looking for? On...Hi Anonymous, What exactly are you looking for? On MSDN you can find how you can create your own custom enumerator: <a href="http://msdn.microsoft.com/en-us/library/ms136120(v=sql.110).aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms136120(v=sql.110).aspx</a>Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-9198038304777104782012-10-23T22:38:44.133+02:002012-10-23T22:38:44.133+02:00Do you have an example of this project that you ca...Do you have an example of this project that you can publish?Anonymousnoreply@blogger.com