Creating instances of excel file

  • 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!

  • 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.

  • An alternative way of achieving is to leave the name of the Excel destination static, and to use the File System task immediately after the data flow to copy the generated file to one with the date-stamped name which you require.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • kl25:

    Thanks for your reply.

    The value of the variable ExceptionReportPath is C:\Reports\ and the expression is set to C:\Temp\. So during runtime I'd like the excel file to get produced in C:\Temp with a datetime stamp. However, after running the package the data from the oledbsource loads the data into C:\Reports\ExceptionReport.xls.

    I verified point #2 that the expression of the ExcelFilePath is set to @[User:ExceptionReportName].

    Phil:

    Thanks for your reply 🙂

    Is there a best practice in this? I'd like to figure out what I'm doing wrong as (to me) it seems a simpler approach. But if I can't get it I'll try out using a file task.

  • I'm not sure about best practice, but as SSIS is so heavily dependent on design-time meta data, I try to avoid run-time meta-data tweaks where I can. I don't know whether that's a good practice, or just something I've picked up through years of working with SSIS.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I put a Script Component between the oledb source and excel destination to popup the value of ExceptionReportName. The value was C:\Reports\ExceptionReport.xls. I was expecting an excel file to be in C:\Temp\ExceptionReport_201304011029. For some reason, during runtime the expression of ExceptionReportName isn't being used.

    The intention of ExceptionReport Path is to be used as a parameter for the different environments (dev, uat, prod). So for example, the value of ExceptionReportPath in dev will be \\Serverdev\Reports\. After the package runs, there should be a file \\Serverdev\Reports\ExceptionReport_201304011031.xls.

    Hope this clarifies what I'm trying to do.

  • rs80 (4/1/2013)


    The value of the variable ExceptionReportPath is C:\Reports\ and the expression is set to C:\Temp\. So during runtime I'd like the excel file to get produced in C:\Temp with a datetime stamp. However, after running the package the data from the oledbsource loads the data into C:\Reports\ExceptionReport.xls.

    Can the value of @ExceptionReportPath be set to C:\Temp\ ? It's not clear why it needs to have the value of C:\Reports\ if you're wanting to change it to C:\Temp\ at runtime. Not sure if that would make a difference with the issue at hand, though, because if there was an issue with how you defined C:\Temp\ in the expression, I'd expect so see some kind of syntax error.

    You might double check that you've set both variables' 'Evaluate as Expression' property to True. I really doubt that's the issue but sometimes a really puzzling problem ends up being something small like that.

    In the end, you may be best to go with Phil's suggestion.

  • I didn't have evaluate as expression set to true for ExceptionReportName. After making that change and running the package, a package validation error comes up. "Error at Exceptions Report: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database." The value of ExceptionReportName is C:\Temp\ExceptionReport_201304111551.xls. I copied the excel file to C:\Temp\ and renamed the excel file to ExceptionReport_201304111551.xls. But I still get that error.

    Not sure what I'm doing wrong. I also tried setting the value of ExceptionReportPath to C:\Temp

    If I can't get this working soon I'll try Phil's method.

  • rs80 (4/1/2013)


    I didn't have evaluate as expression set to true for ExceptionReportName. After making that change and running the package, a package validation error comes up. "Error at Exceptions Report: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database." The value of ExceptionReportName is C:\Temp\ExceptionReport_201304111551.xls. I copied the excel file to C:\Temp\ and renamed the excel file to ExceptionReport_201304111551.xls. But I still get that error.

    Not sure what I'm doing wrong. I also tried setting the value of ExceptionReportPath to C:\Temp

    If I can't get this working soon I'll try Phil's method.

    It sounds like the package has moved beyond not generating the spreadsheet to having problems populating the worksheet (Sheet1$). It may be because the file name contains a timestamp. The time stamp will change each time you run the package so copying a file with the name won't help for the next time the package evaluates--the file's name will be different. If that's what's going on, it's probably best to go with Phil's suggestion. Build and populate the excel file with a static name then do a file system operation to rename the file. The timestamp shouldn't be an issue at that point.

  • Phil:

    Just to make sure I've understood your approach:

    1) In the data flow, I would load the data from the oledb source to the template.

    2) Then I would use a file task to copy the template to whatever file name and location I want.

    In this case wouldn't the template file keep on growing everytime I run the package with records from the oledb source? Do I have to purge the records in the template somehow? For example, run 1 of the package I add 10 records from the oledb source to the template, copy the file and rename it as ExceptionReport_201304021023. Run 2 of the package I add another 10 records from the oledb source to the template. In this case the template has 20 records and the exception report also has 20 records. However, I just want the last 10 records in the exception report.

    Hope my question makes sense.

  • rs80 (4/2/2013)


    Phil:

    Just to make sure I've understood your approach:

    1) In the data flow, I would load the data from the oledb source to the template.

    2) Then I would use a file task to copy the template to whatever file name and location I want.

    In this case wouldn't the template file keep on growing everytime I run the package with records from the oledb source? Do I have to purge the records in the template somehow? For example, run 1 of the package I add 10 records from the oledb source to the template, copy the file and rename it as ExceptionReport_201304021023. Run 2 of the package I add another 10 records from the oledb source to the template. In this case the template has 20 records and the exception report also has 20 records. However, I just want the last 10 records in the exception report.

    Hope my question makes sense.

    You have understood my suggestion well - makes sense and the answer is yes.

    Keep a blank copy of the template somewhere handy and use a FST to copy it over the top of the Excel destination file before each data flow runs, to avoid appending data.

    If you do a search on these forums, you will also find a technique to remove existing rows from an Excel spreadsheet using a SQL-like DELETE command. I've never implemented this though (I always try to export to CSVs, because mixing SSIS and Excel gives me palpitations).

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • palpitations 🙂

    Please confirm the control flow should have this process:

    1) fst to overwrite the original template w/ the existing template

    2) data flow task - oledb source to excel destination

    3) fst to copy the existing template to a different location and file name

  • Confirmed - that should do it. Good luck.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks kl25 and Phil for the help. I was able to implement the package using Phil's approach.

  • rs80 (4/2/2013)


    Thanks kl25 and Phil for the help. I was able to implement the package using Phil's approach.

    Glad to hear that you got it working! 🙂

Viewing 15 posts - 1 through 15 (of 24 total)

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