Post reply

Importing the excel sheet names

  • June 4, 2010 at 5:17 am

    #225344

    Hi,

    I tried to get the excel sheet names from the excel sheet. By using the following method

    Created 2 variables

    Variable Name Datatype Value

    UVObjects Objects Objects

    UVWorksheets String 'sample$'

    In the control flow i used Dataflow task, For each loop container(Inside the For each loop container i used another Dataflow task)

    I used Dataflow task.....Inside the dataflow i used script component transformation to connect the excel and to get the excel sheet names and to store it in the output column...

    Then i connected it to the Recordset destination , i used the input columns from the script component and stored it in the variable name : UVObjects

    In For each loop container i used the For Loop enumerator with ADO source variable as UVObjects and then used the variable UVWorksheets with index as 0

    Inside the For each loop container i used Dataflow task...The Dataflow task contain excel source and unionall transformation...

    In excel source i used the Open Row set variable and selected Variable Name UVWorksheets

    Everything is working fine....but during the execution of Excel source it gives the error as

    [Excel Source [1]] Error: An OLE DB error has occurred. Error code: 0x80004005.

    [Excel Source [1]] Error: Opening a rowset for "Sample$" failed. Check that the object exists in the database.

    [DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN".

    I have checked in all the ways...Can anyone help me out where the mistake is taking place...It'll be very useful to me

  • June 4, 2010 at 6:16 am

    #1176472

    I think the problem lies in the fact that you can't configure an Excel Source dynamically.

    Each time that you change the sheet name, the metadata changes and SSIS can't handle that.

    I'm very afraid that you'll have to create static Excel Sources for each Excel sheet or write a script that reads the Excel file dynamically for you.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 2 (of 2 total)

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