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

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)

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

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

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.

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/

Sorry that was my typo in reply. I've written as you stated but still am getting the error.

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/

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?

Hi , Set Delay Validation to TRUE on Pkg Level. Thank you http://sqlage.blogspot.com/

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/