none
How to load data from OLEDB source table to Excel sheet on a daily basis and file name as GETDATE? RRS feed

  • Question

  • Hi Experts,

    I've a source table in one server and I want to load some fields of that table with data as is into an Excel sheet everyday.

    Everyday at some specific time, this package has to run and have to save this excel sheet in one file path inside another server with that package run date.

    Could you please help me in implementing this?

    Thanks in advance

    Monday, August 27, 2012 1:51 PM

Answers

  • Why complicate more than necessary?

    Create a Connection Manger to your Excel-File

    Go to properties of that Connection Manager, choose "Expression" and than "ConnectionString"

    In expression (i.e.): "C:\temp\MyExcelSheet" +  (DT_STR, 8, 1252)
    ( YEAR(GETDATE()) * 10000 + MONTH( GETDATE()  ) * 100 + DAY(GETDATE())) + ".xlsx"

    In your Dataflow use this Connection Manager for Destination

    • Marked as answer by SQL2012BI Wednesday, August 29, 2012 5:17 AM
    Monday, August 27, 2012 2:43 PM
  • Hi ,

    Set Delay Validation to TRUE on Pkg Level.

    Thank you


    http://sqlage.blogspot.com/

    • Marked as answer by SQL2012BI Wednesday, August 29, 2012 5:17 AM
    Tuesday, August 28, 2012 1:40 PM

All replies

  • NoorBi,

    what you can create a package, which has execute sql task with specific fields you want to put into the Excel file.

    and you can use Script task to create Excel file and map every field you have in your query.

    you need to use streamWriter1.Write(Row.fieldname); to write an Excel file.

    give a location where you want to put this file and that is it.

    these are the simple steps.


    Regards, Chirag Patel (ETL Engineer)

    Monday, August 27, 2012 2:33 PM
  • Why complicate more than necessary?

    Create a Connection Manger to your Excel-File

    Go to properties of that Connection Manager, choose "Expression" and than "ConnectionString"

    In expression (i.e.): "C:\temp\MyExcelSheet" +  (DT_STR, 8, 1252)
    ( YEAR(GETDATE()) * 10000 + MONTH( GETDATE()  ) * 100 + DAY(GETDATE())) + ".xlsx"

    In your Dataflow use this Connection Manager for Destination

    • Marked as answer by SQL2012BI Wednesday, August 29, 2012 5:17 AM
    Monday, August 27, 2012 2:43 PM
  • Hi Christa,

    Thanks for the reply but its giving the error when I set connection manager as below in expression...

    Nonfatal error occured while saving the package:

    Error at SAPBICustomer [Connection Manager "Excel Connection Manager"]: The connection string format is not valid. it must consist of one or more components of the form X=Y, seperated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

    Error at SAPBICustomer: The result of the expression ""C:\\Users\\Public\\Documents\\SAP BI\\SAPBI"+(DT_STR), 8, 1252)
    ( YEAR(GETDATE()) * 10000 + MONTH( GETDATE()  ) * 100 + DAY(GETDATE())) + ".xlsx"" on property "connection string" cannot be written to the property, The expression was evaluated, but cannot be set on the property.

    Please suggest.

    Tuesday, August 28, 2012 9:56 AM
  • The above error is cause because the expression you have used to set your filename is incorrect. It has an additional bracket in it.

    Use this expression to set your filename:

    "C:\\Users\\Public\\Documents\\SAP BI\\SAPBI" + (DT_STR,8,1252)(YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE())) + ".xlsx"


    http://btsbee.wordpress.com/

    Tuesday, August 28, 2012 10:55 AM
  • Sorry that was my typo in reply. I've written as you stated but still am getting the error.
    Tuesday, August 28, 2012 11:32 AM
  • Are you setting the expression on the connection manager correctly. Please refer the snapshot where am setting the connection via an expression and it sets the path correctly.

    You might also want to check whether the account you are using to run your packages has the appropriate rights for creating a file at the path mentioned.


    http://btsbee.wordpress.com/

    Tuesday, August 28, 2012 12:58 PM
  • Yes, I've done the same way and Evaluate Expression also succeeded. But when running package am getting error.

    For your information am creating 97-2003 xls file in destination file path. am using SQL SERVER 2008 R2 and Kept debugging run time for 64 bit as false.

    Is that causing the error?

    Tuesday, August 28, 2012 1:31 PM
  • Hi ,

    Set Delay Validation to TRUE on Pkg Level.

    Thank you


    http://sqlage.blogspot.com/

    • Marked as answer by SQL2012BI Wednesday, August 29, 2012 5:17 AM
    Tuesday, August 28, 2012 1:40 PM
  • Ok. The problem is because the connection string in an excel connection is much more than just a file path. So set your connection property to

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\\Users\\Public\\Documents\\SAP BI\\SAPBI" + (DT_STR,8,1252)(YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE()))  + ".xls;Extended Properties=\"Excel 8.0;HDR=NO\";"


    http://btsbee.wordpress.com/

    Tuesday, August 28, 2012 2:04 PM