Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating instances of excel file


Creating instances of excel file

Author
Message
rs80
rs80
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 379
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!
kl25
kl25
Mr or Mrs. 500
Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)

Group: General Forum Members
Points: 514 Visits: 1875
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.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8322 Visits: 19470
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
rs80
rs80
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 379
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 Smile

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.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8322 Visits: 19470
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
rs80
rs80
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 379
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.
kl25
kl25
Mr or Mrs. 500
Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)

Group: General Forum Members
Points: 514 Visits: 1875
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.
rs80
rs80
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 379
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.
kl25
kl25
Mr or Mrs. 500
Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)

Group: General Forum Members
Points: 514 Visits: 1875
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.
rs80
rs80
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 379
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search