Post reply

Dynamic Excel destination

  • January 22, 2009 at 2:16 am

    #155161

    I am trying to export data into excel file using Excel destination. In the Connection manager when i try to provide static Excel File path then it works fine. But when i provide dynamic Excel path i am getting the following error

    "Error at Data flow task[Excel Destination[1480]];Opening a rowset for "Excel Destination" failed.Check that the object exists in the databse."

    I followed the below things to create dynamic excel destination

    (DelayValidation property of package and Excel destination , both are true.)

    1.I created a variable "XLFileRootDir" and assigned an expression - "D:/CDO/SSIS/"+"ExcelTarget" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) + "-" + (DT_WSTR,2)Datepart("mi",getdate()) + "-" + (DT_WSTR,2) Datepart("ss",getdate()) + ".xls"

    2.Then in the Excel connection manager i assigned "XLFileRootDir" variable to ExcelFilePath property.

    3.In Excel destination , i choosed data access mode as "Table or view" and created a new work sheet by cliking on New button. The worksheet created successfully.

    But when i try to go to Mapping Tab or click on Preview button i use to get the same error.

    Plz help on this

  • January 26, 2009 at 11:36 am

    #932748

    Set up your connection manager with static path. Then, before the actual export, use a script task to change the connection string of the connection manager.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • January 26, 2009 at 9:59 pm

    #932987

    Alvin Ramard (1/26/2009)

    Set up your connection manager with static path. Then, before the actual export, use a script task to change the connection string of the connection manager.

    You could also use a variable with an initial value that's valid at design time.

    This initial path should point to an excel file that is in the same format as your target files.

    Then change the value of the variable to the result of your expression during your package run.

    This saves using a script task.

    In some cases, using '/' as the path avoids 'file exists' errors - I am not sure if this works for Excel.

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

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