none
Error while loading data from sql server table to Excel sheet RRS feed

  • Question

  • Hi Experts,

    I've created a SSIS package to load data from SQL Server table to Excel sheet with date in other server.

    I've included this SSIS package in SQL Job to execute it on daily basis.

    Yesterday, on first day it executed successfully and loaded data but today it failed with below error.

    Description: "Component "Excel Destination failed validation and returned validation status "VS_IS BROKEN".

    One or more component failed validation.

    Could you please help me in finding where is the problem?

    For your Information, below are the steps involved in creation of package.

    Drag Data Flow task.

    1--In data flow, dragged OLEDB Source, then selected data access mode as SQL Command and wrote select statement.

    2-- Dragged Excel Destination. Connected them and then created Excel Connection manger by using Wizard. Defined where to put the file.

    3--Created a variable called ExcelFileName. selected the properties of this variable.

    Expression is "D:\\MyExcelFolder\\report_"+Replace(Replace(Replace(Replace((DT_WSTR,50)(getdate()),"-","")," ","_"),":",""),".","_")+".xls"

    output will be like this D:\MyExcelFolder\report_20120829_145946_836000000.xls

    4--Opened the Excel Destination. Provided the Excel Connection Manager in the OLEDB connection Manager.

    Given Data Access Mode as table or view.

    Selected New for the Name of the Excel Sheet.

    Mapped columns.

    Then in Excel connectioanger properites, selected property--->FileExcelpath and then Expression-- @[User::ExcelFileName]

    Set Delay Validation property to true for this connection in propery pane.

    5-variable property and set it to EvaluteExpression property to True.

    6. Included this SSIS package in SQL job

    7. Job executed successfully yesterday but failed today with the above error.

    Thursday, August 30, 2012 9:33 AM

Answers

All replies

  • Do you create the Ecel file somewhere? I don't directly see it in your list.

    The excel file needs to exist before you try to load data into it.


    MCTS, MCITP - Please mark posts as answered where appropriate.


    Thursday, August 30, 2012 9:51 AM
  • Actually My requirement is to create a new excel file daily with todays date.

    For the first time Excel file was there. my expectation is from second time onwards it will create a new with date as I've mentioned that in connection properties.

    am I wrong?

    Thursday, August 30, 2012 10:38 AM
  • You're wrong, the Excel needs to exist.
    If you write a SQL INSERT statement, do you expect that it creates the database and/or table for you?

    An example on how to dynamically create the Excel:

    http://geekepisodes.com/sqlbi/2011/creating-excel-files-xls-dynamically-from-ssis/


    MCTS, MCITP - Please mark posts as answered where appropriate.

    • Marked as answer by SQL2012BI Thursday, August 30, 2012 11:02 AM
    Thursday, August 30, 2012 10:44 AM
  • Thanks for the reply with link. But in that am not able to follow steps.

    Could you please let me know the steps to achieve.

    It would be great help.

    Thursday, August 30, 2012 10:51 AM
  • It's a really clear article - with pictures and everything - so I'm not sure how I can add to this.

    What part are you struggling with?


    MCTS, MCITP - Please mark posts as answered where appropriate.

    Thursday, August 30, 2012 10:52 AM
  • Thanks very much. I was struggling in implementing the Data flow task.

    Now I've achieved it.

    Thanks again.

    Thursday, August 30, 2012 11:02 AM
  • OK good. If you happen to have more questions later on, just ask them.

    MCTS, MCITP - Please mark posts as answered where appropriate.

    Thursday, August 30, 2012 11:07 AM