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

Overwriting Excel File with SSIS package Expand / Collapse
Author
Message
Posted Friday, November 21, 2008 8:50 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, July 4, 2014 5:29 AM
Points: 157, Visits: 350
I'm pretty sure that what I do is not the best way but if you try it, you should get over that problem.

My template has headings in the first row and dummy data in the second. So text fields are filled with a character to their maximum size, and number fields are populated with the biggest number likely to come their way. If there is a decimal point that is shown too.

So fill text fields with all 'A's or similar, set integers to 123456 (for as many positions as you need). Decimals will be 1234.56, and dates will be 01/01/2001 or whatever format you prefer.

Your ssis package will then populate from line 3 onwards, and you need a macro that runs on the workbook open event that detects the dummy line and deletes it.

I'm ok with this until we need to make a change to the spreadsheet. The package then turns all the text fields into WSTR format, and then gives an error. I don't know how many times I have edited the advanced settings to change each field back to STR.

You can bet there is a better way to do this, but I have asked and asked, all over this and other boards, and no-one has answered yet.

Recently I have discovered the joys of sp_makewebtask as an alternative way of creating excel output. Indeed I am increasingly avoiding SSIS wherever possible as, without any expert advice, it is more trouble than it is worth.

Here's an example of makewebtask that I have just built:

DECLARE @outputfile NVARCHAR(255)
DECLARE @query NVARCHAR(500)
SET @outputfile = 'e:\mirror_files\bliff.xls'
SET @query = 'EXEC [dbo].[usp_Report_PM_Weekly_Overdue_Reforecast] @RefcastDetectStart = N' + CHAR(39) + '2008-11-14'+ CHAR(39) + ', @RefcastDetectEnd = N' + CHAR(39) + '2008-11-21'+ CHAR(39)

EXEC sp_makewebtask
@outputfile,
@query,
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Weekly overdue, due in next 28 day and reforecast reports'

It has certain restrictions but I've normally been able to accomplish what I need with an Excel macro.

Hope that helps

B2B
Post #606593
Posted Friday, November 21, 2008 10:21 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, Visits: 314
Thank you much for your reply. I will try the dummy data trick today. Will let you know the outcome.
Post #606676
Posted Friday, November 21, 2008 11:11 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, Visits: 314
I tried putting the dummy data in the template and it worked perfect! Thank you, Thank you.:)
Post #606736
Posted Monday, November 24, 2008 12:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:19 PM
Points: 39, Visits: 390
Hi guys,

I was trying the same thing but i got an error while exporting the data from SQL to excel.

[Excel Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
[Excel Destination [16]] Error: Opening a rowset for "Export" failed. Check that the object exists in the database.
[DTS.Pipeline] Error: "component "Excel Destination" (16)" failed validation and returned validation status "VS_ISBROKEN".

I have no idea what that means
Actually, I used a file system task followed by a Data flow task.
In Data flow task I used oledb source and Excel destination.
In File system Task I used option delete file.

Guys I am new to this SSIS. Please help me in learning things.
Thank you
Post #607789
Posted Monday, November 24, 2008 1:02 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, Visits: 314
You do not want to use the option to delete the Excel file. The filesystem task should be to copy the template file to the "real" file that is your data task destination.
Post #607826
Posted Monday, November 24, 2008 1:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:19 PM
Points: 39, Visits: 390
Hi ellen ,

Thanks for the quick reply.
Can you plz tell me in detail What exactly I should do in File System Task.

Thank you again
Post #607833
Posted Tuesday, November 25, 2008 1:15 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, July 4, 2014 5:29 AM
Points: 157, Visits: 350
tinku.reddy. Ellen and I are trying to write Excel files on a regular basis. I don't have Office installed on my server, so I can't do any detailed manipulation of the spreadsheet - only write simple output to it.

I need output with pivots and charts that are based on the data I have just written, so I have written a lot of Excel code to format these, and it is triggered from the Workbook_Open event. As well as this, it seems that SQL Server is pretty clueless about Excel destinations unless Excel is on the server, so I came up with the idea of the dummy line to tell it what kind of data to expect.

I therefore have an empty spreadsheet, with all the code already in it, which I use as a template. The column headings are in place, so I can map my extracted data to the right workbook column, and there is a line of dummy data to tell SSIS how to configure the output.

I have an SSIS package the same as yours, with a File System Task that copies the template workbook to an output area and a Data Flow task that writes my data to the copy workbook. The original template stays where it is ready for the next run.

My customers open the copy of the workbook (which has been sent to them with a sendmail task), and the code in the workbook automatically starts, creating and formatting pivots and charts, and deleting the dummy line.

There is no need to delete an old spreadsheet. The file system task that copies the template to the output area is configured to overwrite the previous file.

Sorry about the long answer but I'm hoping it's a good example for you of how to do this and why you might want to.

As I said above, I'm not convinced it's a good way - it seems incredibly clumsy. A total botch, even. But no-one has offered a better solution.

I only do this kind of thing when I need to schedule a job. For ad-hoc Excel reports I have created a number of store procedures that can be called from Excel. I find that I only need 4 or 5 parameter driven generic extracts to fulfill all our reporting requirements, and I can then use VBA to format the extracted data as necessary.

Hope this all helps

B2B
Post #608138
Posted Tuesday, November 25, 2008 10:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:19 PM
Points: 39, Visits: 390
First of all I appreciate Your Patience and Thanks a TON
for the whole thing.

I did exactly what you said and It worked.
Initially I got some errors and i resolved some and Its working fine know
Thank you B2b for ur detailed explanation.

Post #608532
Posted Wednesday, July 29, 2009 5:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 29, 2009 5:25 AM
Points: 1, Visits: 0
Hi,

Please go through the following links. You will get a solution.

http://www.bigresource.com/MS_SQL--SSIS-Dynamic-Excel-File-Name--HbPRu1XN.html
http://dbaspot.com/forums/ms-sqlserver/357322-excel-template-required-export-ssis.html

with regards
SSSK
Post #761394
Posted Thursday, December 10, 2009 11:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 15, 2011 3:37 PM
Points: 1, Visits: 7
This worked perfectly for me! Thank you for the suggestion.

-Jenni
Post #832476
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse