June 13, 2008 at 9:19 am
Greetings and salutations...
I have never posted on a site like this before. I am EXTREMELY new to using SSIS. I am attempting to figure out how to extract data from an Excel Source. At this point, I need to figure out how to take a workbook and extract the data in from all of the worksheets in that book with the Foreach loop. I have gotten some pretty good input from some forums, but they seem to be coming up just short. At this point, I'm just testing it and I have a Foreach Loop Container in my Control Flow. Encapsulated in that, I have a Data Flow task. In the Foreach Loop Editor, in the collection I have the enumerator as "Foreach ADO.NET Schema Rowset Enumerator. In my Connection Manager, I have set up a .Net Providers for OleDb\Microsoft Jet 4.0 OLE DB Provider with the path of my file and have set the Extended Properties in the All tab of the Connection Manager to Excel 8.0. When I click the "Test Connection" button, it connects. Back in the Foreach Loop Editor, under Enumerator Configuration, the Connection is set to my connection that I previously discussed. The Schema selected is Tables. In the Varialbe Mappings, I have assigned a variable, User::SheetReceiver with an index of 2, to recieve the values as the foreach loop cycles. Then in the Data Flow, I have an excel source. The connection manager in it is set with the same connection and the Data access mode is "Table name or view name variable" and then the variable name is the User::SheetReceiver. I click the Preview button and it shows the columns from the first worksheet that it is to read in. The Colums heading of the Excel Source Editor is again, populated with all the Column names and each is selected. When I execute the package, it blows up and doesn't even drop into the Data Flow. How is this able to view the columns if that's the case. I am at a loss here and could really use some assistance.
Thanks.
Hogie
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply