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 «««12345»»

Capturing The Error Description In A Stored Procedure Expand / Collapse
Author
Message
Posted Tuesday, April 24, 2007 6:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 6, 2007 6:13 AM
Points: 1, Visits: 1

subj: Using DTS to Generate and Email Excel Reports

Very useful information Joe - thanks for sharing, this technique will save a lot of time for me.

 

Post #360541
Posted Wednesday, May 2, 2007 2:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, December 13, 2014 6:08 PM
Points: 248, Visits: 547

This is very useful indeed.

I have a question though.  I need to change the excel spreadsheet name every day (i.e. I want to dynamically incorporate a time stamp in its name [e.g. Report_05022007]).  Is that possible?

 

Thanks guys!

Post #362768
Posted Wednesday, May 2, 2007 7:08 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 3, 2014 10:07 AM
Points: 434, Visits: 272

If you are using a transformation to fill your spreadsheet:

Create an Excel template that you always use for the destination. (Just the field names on the worksheet.) 

Upstream of the transform, create an ActiveX.  Write some VB code that creates the path on the fly based on date, and assign the path to a local var.  Copy the template workbook to that path using the FileSystemObject.  Assign the local var to a global string var. 

After that, create Dynamic Properties Task.  Assign the global var to the destination of the transformation.

After that comes the transformation.

If you have the Excel object model on the server, you can do your copy, move with that in the ActiveX script.  But it's bloaty.  Use the FSO.

If you use an Excel template like this, you won't need to clear the 'table'.  Just leave the template empty, and dump your transform to the worksheet by name.  You may find that you need to cast some of your fields to varchar to make them look pretty in the output.

Voila!



ZenDada
Post #362842
Posted Monday, July 21, 2008 12:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, December 13, 2014 6:08 PM
Points: 248, Visits: 547
Guys,

If we do not have the Microsoft Excel installed on the server where the template (created on my local machine with MS Excel installed) resides, will this approach still work?


Thanks a lot!
Post #537880
Posted Monday, July 21, 2008 12:10 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 3, 2014 10:07 AM
Points: 434, Visits: 272
sql_er (7/21/2008)
Guys,

If we do not have the Microsoft Excel installed on the server where the template (created on my local machine with MS Excel installed) resides, will this approach still work?


Thanks a lot!


Heck yeah, just use the FileSystemObject.


ZenDada
Post #537888
Posted Monday, July 21, 2008 3:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, December 13, 2014 6:08 PM
Points: 248, Visits: 547
Zendada,

Thank you for the reply - I am still a bit confused though.

My question was referring to the approach used by the original article. If I follow that approach as is, with no modifications, and the excel template will reside on the server with no MS Excel installed, will this approach work?


Thanks a lot!
Post #538008
Posted Monday, July 21, 2008 3:22 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 3, 2014 10:07 AM
Points: 434, Visits: 272
sql_er (7/21/2008)
Zendada,

Thank you for the reply - I am still a bit confused though.

My question was referring to the approach used by the original article. If I follow that approach as is, with no modifications, and the excel template will reside on the server with no MS Excel installed, will this approach work?


Thanks a lot!


Oh sorry, I thought you were referring to my post. I just re-read the article. I don't see anything in the article that requires the Excel object model on the server, so you don't need the excel app on it. The delete and create table statements don't require it. Make sure you use ticks and not apostraphes just as in the article. Neither does the SMTP code require Excel (instructions for that are in the article). Hope that helps.


ZenDada
Post #538017
Posted Tuesday, July 22, 2008 12:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, December 13, 2014 6:08 PM
Points: 248, Visits: 547
Zendada,

I don't think it is working. I tried it. Even though the DTS package executes with no errors, nothing happens - the original data remains in the excel spreadsheet.

The puzzling part is that it executes successfully. I tried just having the DROP TABLE component to see if the contents of the Excel spreadsheet would be deleted ... although the package executed successfully, the contents were not deleted.

Any suggestions?


Thank you!
Post #538726
Posted Tuesday, July 22, 2008 12:34 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 3, 2014 10:07 AM
Points: 434, Visits: 272
sql_er (7/22/2008)
Zendada,

I don't think it is working. I tried it. Even though the DTS package executes with no errors, nothing happens - the original data remains in the excel spreadsheet.

The puzzling part is that it executes successfully. I tried just having the DROP TABLE component to see if the contents of the Excel spreadsheet would be deleted ... although the package executed successfully, the contents were not deleted.

Any suggestions?


Thank you!


Hmm.... I am not a DTS programmer any more... so I am working from memory here... let's see...
try these little debugging thingies...

start with a fresh workbook. Make sure it has only one page in it. run the create table statement pointing at that one page. close the connection. go back and run the delete table statement.

i think the connection object can point to either a page or a range, and that the "table" is really a contiguous range. so play with that idea. if the create table didn't work before, try creating a range now, and change the connection to this range now instead of the page.

as i recall, the default setting for excel is not to close the connection which leaves the workbook in a read only state. so don't forget to change that setting.

while you are messing around with this, between runs, try opening the workbook, deleting every freaking row (rightclick delete - you know what i mean) - that clears any remembered range. And if the create range thing worked for you before, create your range again with only the column headings and no, or one, row in it.


ZenDada
Post #538750
Posted Tuesday, July 22, 2008 12:39 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 3, 2014 10:07 AM
Points: 434, Visits: 272
Oh and first thing - the data you have in there right now... how did it get there? was that with your create table statement and insert? Or did you simply export into the page? If the latter, your connection is pointing at the page, not the table (range).

ZenDada
Post #538759
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse