Home Forums SQL Server 2005 SQL Server 2005 Integration Services Creating instances of excel file RE: Creating instances of excel file

  • March 29, 2013 at 4:14 pm

    #1602186

    rs80 (3/29/2013)

    I'm using SQL Server 2008 BI. I have an oledb source outputting data into an excel destination. I want the file to be created as different instances with the timestamp. I've created one variable called: ExceptionReportName. The value is C:\Reports\ExceptionReport.xls. The expression for that variable is:

    (DT_I1) (DT_WSTR, 2)Datepart("mm", GetDate()) < 10 ? @[User::ExceptionReportPath] +"ExceptionReport_" + (DT_WSTR, 4)Datepart("yyyy", GetDate()) + "0" + (DT_WSTR, 2)Datepart("mm", GetDate()) + (DT_WSTR, 2)Datepart("dd", GetDate()) + (DT_WSTR, 2)Datepart("Hh", GetDate()) + (DT_WSTR, 2)Datepart("mi", GetDate()) + (DT_WSTR, 2)Datepart("ss", GetDate()) + ".xls": @[User::ExceptionReportPath] + "ExceptionReport_" + (DT_WSTR, 4)Datepart("yyyy", GetDate()) + (DT_WSTR, 2)Datepart("mm", GetDate()) + (DT_WSTR, 2)Datepart("dd", GetDate()) + (DT_WSTR, 2)Datepart("Hh", GetDate()) + (DT_WSTR, 2)Datepart("mi", GetDate()) + (DT_WSTR, 2)Datepart("ss", GetDate()) + ".xls"

    The value for for the variable ExceptionReportPath is C:\Reports\ and the expression is: C:\Temp\.

    The value for the ExcelFilePath property of the Excel Connection Manager is C:\Reports\ExceptionReport.xls and the expression is @[User::ExceptionReportName].

    When I run the report it adds the rows from the oledb source to C:\Reports\ExceptionReport.xls instead of creating a file as C:\Temp\ExceptionReport_20130329022012.

    Am I not suppose to use the ExcelFilePath property to create an instance of an excel file? After reading other posts I thought the right way was to use the ExcelFilePath property. What am I missing?

    Thanks!

    The ExcelFilePath property is the right place to use an expression for generating differently named excel files. Your expression for @ExceptionReportName evaluates like you're expecting. What's not clear is the @ExceptionReportPath variable. I'm not following your expression for it--it looks like it's just a different value. Here are a couple of things to check.

    1. How the @ExceptionReportPath variable expression is defined and whether it's set to evaluate as an expression.

    2. Be sure you've used @[User::ExceptionReportName] in the ExcelFilePath property in the expression section for the connection manager to the file you want to populate. It sounds like that's what you did but it's not completely clear.