Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Creating instances of excel file Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 12:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:41 PM
Points: 100, Visits: 313
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!
Post #1437048
Posted Friday, March 29, 2013 4:14 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
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.
Post #1437128
Posted Monday, April 1, 2013 1:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:40 AM
Points: 5,318, Visits: 12,366
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1437364
Posted Monday, April 1, 2013 7:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:41 PM
Points: 100, Visits: 313
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.
Post #1437443
Posted Monday, April 1, 2013 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:40 AM
Points: 5,318, Visits: 12,366
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1437447
Posted Monday, April 1, 2013 8:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:41 PM
Points: 100, Visits: 313
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.
Post #1437455
Posted Monday, April 1, 2013 8:57 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
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.
Post #1437472
Posted Monday, April 1, 2013 9:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:41 PM
Points: 100, Visits: 313
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.
Post #1437480
Posted Monday, April 1, 2013 9:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
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.
Post #1437492
Posted Tuesday, April 2, 2013 8:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:41 PM
Points: 100, Visits: 313
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.
Post #1437925
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse