Post reply

Loop through multiple excel files

  • August 31, 2010 at 8:53 am

    #228509

    Hi,

    I want to loop through several excel files through SSIS and also through multiple sheets .

    Any help appreciated .

    Thanks,

  • August 31, 2010 at 10:00 am

    #1214442

    Hi

    Please bear in mind that this is only part of the solution you are looking for.

    In the past I have used the following in order to loop through different Excel files:

    - Create a Variable

    - Create a "For Loop" Container

    - In the Collection of your Conatiner you can specify the Folder and a mapping for the file names

    - In the Variable Mapping of the Container specify your Variable

    When you run this SSIS Package all the Tasks included as part of your Container will be able to access whatever file is specified in your Variable.

    Ludo

  • August 31, 2010 at 10:14 am

    #1214455

    I get the error when I add a data flow task inside the for each loop container . Inside the Data flow task, i added a execl source .

    In the excel Source Editor

    Data access mode : Table or view named variable

    Name of the excel sheet :variablename

    When I hit Columns I get the below error

    ===================================

    Error at ForEach [Connection manager "Excel Connection Manager 1"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".

    Error at Data Flow Task [Excel Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    (Microsoft Visual Studio)

    ===================================

    Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

  • August 31, 2010 at 10:32 am

    #1214469

    This may be due to the fact that you do not have a file with the correct name in the directory specified in the properties of your Container.

    Put an .xls file in there for test purposes and give it a go.

    Please note that you'll have to specify "delay validation" at property level (don't remember which one) so each task get validated only when it is executed.

    Hope this helps.

    Ludo

  • August 31, 2010 at 10:39 am

    #1214471

    I think that you also have to deal with the spreadsheet tab names - if they are unknown, SSIS won't be able to open up your spreadsheet.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 31, 2010 at 10:27 pm

    #1214719

    As wayne has already mentioned about Excel sheets,

    You may implement either an OPENROWSET query or a routine T-SQL with sheeet names

    For more customization...Script Task may be handy

    Raunak J

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

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