SQL Server Performance

Import excel files realtime

Discussion in 'SQL Server 2005 Integration Services' started by arkiboys, Nov 6, 2007.

  1. arkiboys Member

    Using SSIS, I am successfully importing data from excel files into a table in sql server 2005.
    These excel files are ALWAYS open and are being updated from external sources i.e. third party tools.
    After doing alot of investigations, I have reached the conclusions as follows:
    When the excel files are NOT being updated, then the ssis package works
    BUT when the excel files are being updated, then the ssis package does not work. The error is:
    [Excel Source [749]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    Please note that the excel files MUST be open so that they get updated by the third party tool.
    AND, every few minutes i.e. 5 mins, my ssis package should import these excel files.
    How is it possible to load the excel data while it is being updated by external third party.
  2. satya Moderator

  3. arkiboys Member

    Not quite.
    This one is to do with the files being locked. The other thread was to do with getting the connectionstring right and setting the delay validation property.
    Any thougts on this thread pleas?
  4. satya Moderator

    I believe I have answered similar question (may be posted by you during last week) that, in thsi case you could copy the relevant excel file when it is generated to relevant SQL directory to open for SSIS ETL process and then delete them. Though it is bit of duplication but avoids the error as the file open requirement is a must here.
  5. arkiboys Member

    Do you mean copy the excel files (Which are being updated realtime) using ssis into a folder and then use ssis again to import the data from the copied files?Does ssis allow copy of files while they are being updated realtime?Thanks
  6. satya Moderator

    Don't use SSIS to copy those files, rather run a different job to copy those files and also drop the file after ETL is finished.
  7. satya Moderator

    Its a drawback within EXCEL to close the file inorder to perform updates, in your case you must do the same to get latest updates and until they are completed the users will not be able to manage it.
  8. khateeb82 New Member

    hi arkiboys ,
    i want from u to help me , please .
    i have the same thing and i cant do any thing i see ur msg here and in microsoft forum .. under title import excel to sql 2005 - realtime.
    really i have same problem .
    i have third party updat my excel sheet and i cant close it and i want to update this data always as real time in my sql server.
    befor 2 week i serch for that and today i catch ur msg here and in microsoft forum.
    please help me .. how i can update my excel into sql server without closing excel file.
    please tell me in details .
    please and thank u arkiboy.
    i serch about ur email but i cant find it.
    my email : khateeb82@hotmail.com
    again thank u so much for ur help.
  9. arkiboys Member

    Not sure if this will help you:
    At present the excel files get updated constantly from third party data,
    On every cell change event in excel, there is a copy of the excel file into a .csv in another directory,
    every minute, ssis package reads the .csv files.
    The only issue I have noticed is:
    every minute when the ssis package wants to read the .csv files, if one of these .csv files is being re-written by the excel files, then ssis job gives an error of:
    can not open xxx.csv file and therefore the ssis package will not be able to read the .csv at that moment...
    Thought about deleting the .csv files after they are imported into sql server but do not think this will work because, ssis job may not see a .csv file when it is deleted.
    Have not found a better solution to this yet.

Share This Page