SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Overwriting Excel File with SSIS package


Overwriting Excel File with SSIS package

Author
Message
born2bongo
born2bongo
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 351
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
Ellen-477471
Ellen-477471
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 314
Thank you much for your reply. I will try the dummy data trick today. Will let you know the outcome.
Ellen-477471
Ellen-477471
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 314
I tried putting the dummy data in the template and it worked perfect! Thank you, Thank you.Smile
tinku.reddy
tinku.reddy
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 395
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
Ellen-477471
Ellen-477471
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 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.
tinku.reddy
tinku.reddy
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 395
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
born2bongo
born2bongo
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 351
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
tinku.reddy
tinku.reddy
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 395
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.
anandsushilkumar
anandsushilkumar
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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
singers06
singers06
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
This worked perfectly for me! Thank you for the suggestion.

-Jenni
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