September 12, 2008 at 2:58 pm
OK! got it to work. I have a File Task to copy a template over to my destination in a package. Then another package that has an Execute SQL Task to create the sheet and the data flow. I run the first package to copy the template and then the data flow package. Works wonderfully well.
September 15, 2008 at 2:56 am
Cool. That's one I've not tried yet but sounds like a slick solution and one I could make use of fairly soon.
I've only just read both your posts, and when I saw your comment about option 1, the first thing I wanted to know was "did you check the whole sheet?". As you now realise, there are some quirks about how the sheet is handled.
I've been caught out like that. I set up a template with a sheet containing just headings, saw the package write a few hundred rows, opened the workbook and all I got was the headings. However, scroll down, and there's my data at row 5000 or somewhere near. When I create a template now, I make sure I've deleted all empty rows, and then put the headings in.
B2B
September 18, 2008 at 5:17 am
hey
Also been struggling for days with this issue.
A simple task that used to be easily dealt with in SQL200 DTS - just overwrite or create a brand new destination spreadsheet each time a package runs - simple.
I used to be able to create a packahge to export data to excel in 5 minutes with DTS.
Now it takes days and i have to define the detail of dozens more objects.
This is nonsense.
Simple tasks being made overly complicated doesnt help anyone.
Creating excel templates, deleting sheets before your package starts, connections having to be named, connection managers needing to be redfeined for every opackage that uses them.
Errors about excel sheets of a certain name already exisiting causing packages to fail - i dont care just overwrite the excel file if it exists.
Not impressed 🙁
I thought these tools were supposed to make a DBA's life easier not harder.
September 18, 2008 at 5:27 am
Yep. Know what you mean. Been doing it on and off for a year and still very new really. If you think that's bad you wait till this happens:
Every time I make a change to the data that I want output to the spreadsheet, SSIS kindly goes through my output, resets all STR columns to WSTR, and then fails the package because they are incompatible. Nice...
I think I tried casting the data on extract once, and it probably worked, but that's just as big a pain. But that's Microsoft for you
November 21, 2008 at 8:30 am
We have a similar issue. Daily we replace a spreadsheet with a template spreadsheet and write out the new data set. All that works fine. The problem is that three of the columns should be numeric but excel keeps changing to text. The first time the package was created the initial "table" had the correct datatype. We are selecting integers and outputting integers. But in subsequent runs of the package the datatype is changed to text. This is a real problem. Daily someone has to edit the spreadsheet and convert the text to numeric -- which defeats the purpose of a scheduled job.
Does anyone know how to force the output into excel to retain the datatype? I have already saved the package as a VBS script and modified the output datatype. It appears to be an Excel issue. Thank you much for any help you can give.
November 21, 2008 at 8:50 am
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
November 21, 2008 at 10:21 am
Thank you much for your reply. I will try the dummy data trick today. Will let you know the outcome.
November 21, 2008 at 11:11 am
I tried putting the dummy data in the template and it worked perfect! Thank you, Thank you.:)
November 24, 2008 at 12:09 pm
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
November 24, 2008 at 1:02 pm
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.
November 24, 2008 at 1:18 pm
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
November 25, 2008 at 1:15 am
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
November 25, 2008 at 10:19 am
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.
July 29, 2009 at 5:27 am
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
December 10, 2009 at 11:52 am
This worked perfectly for me! Thank you for the suggestion.
-Jenni
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply