January 10, 2013 at 2:39 am
Phil Parkin (1/10/2013)
Wow - you've done this before? Nice.
Not in SSIS, but I have manually - just to learn how to - no reason not to do it in SSIS though.
I actually found out about it because I was struggling with reading XLSX files in some bespoke software and decided to use the OpenXML SDK, which then taught me a bit about the structure of the xlsx file.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 10, 2013 at 4:16 am
Nice offering MM. The technique is one I have seen outlined before but not necessarily applied to SSIS. While no one step is difficult there are quite a few of them. What you have are the makings of an outline for a nice article on working with Excel in SSIS 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 10, 2013 at 6:06 am
opc.three (1/10/2013)
Nice offering MM. The technique is one I have seen outlined before but not necessarily applied to SSIS. While no one step is difficult there are quite a few of them. What you have are the makings of an outline for a nice article on working with Excel in SSIS 😉
Thanks for the nice words.
Maybe it seems like a lot of steps, but it really is easy - just create the template, save it, unzip it and store it somewhere accessible.
Then SSIS generates the Sheet.xml - easy enough - and zips it all up - easy enough.
There are really only two steps to the SSIS part, unless you want to unzip it every time, but I can't see the point in that.
It only gets more complicated if you also want to use the Shared Strings feature, but that is just another XML file to create in SSIS - one more step.
As for an article...can I squeeze that in sometime in 2020 ... maybe?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 10, 2013 at 3:01 pm
mister.magoo (1/10/2013)
opc.three (1/10/2013)
Nice offering MM. The technique is one I have seen outlined before but not necessarily applied to SSIS. While no one step is difficult there are quite a few of them. What you have are the makings of an outline for a nice article on working with Excel in SSIS 😉Thanks for the nice words.
Maybe it seems like a lot of steps, but it really is easy - just create the template, save it, unzip it and store it somewhere accessible.
Then SSIS generates the Sheet.xml - easy enough - and zips it all up - easy enough.
There are really only two steps to the SSIS part, unless you want to unzip it every time, but I can't see the point in that.
It only gets more complicated if you also want to use the Shared Strings feature, but that is just another XML file to create in SSIS - one more step.
As for an article...can I squeeze that in sometime in 2020 ... maybe?
I'm wondering whether that <sheet> XML could be produced in pure T-SQL. If someone took the time to work this out, it would be a real service to the community. If people pledge enough beer money, I might even be tempted to investigate ... 😀
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 11, 2013 at 9:13 am
Phil Parkin (1/10/2013)
If people pledge enough beer money, I might even be tempted to investigate ... 😀
😀 Like your motivation 😀
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 8, 2016 at 9:34 am
Just because I have spent the better part of a month trying to achieve something like this:
It can't be done.
Even in SSIS 2014, using the Excel Destination means "dump the record-set on the first available row."
This means:
* Use a template all you like - it won't help the underlying problem.
* Column headers are preserved. Headers and explanation "boxes" are preserved as well. This serves many people, but isn't what is asked here.
* If you try to format anything under a column header, you will only move the dump down one more row.
* The dump is - a dump. Nothing fancy can be done to the data.
I finally figured that it required coding, one way or another! I just had to choose between writing VBA code into a macro and save that to the template. Or just accept my fate and start diving into the mysteries of the Script Task. The latter is much more versatile than the former, and I personally prefer to involve as few technologies as possible for a single job. (Performance costs is the only thing which will ever make me think otherwise). Besides, I don't like macro-enabled spreadsheets to run wild in my company. And if I am engaging in writing VBA - why bother using SSIS at all? VBA can do exactly the same: Get data, do stuff with data, write data to a (number of new) spreadsheets - and format these to your heart's content.
July 11, 2016 at 7:54 am
mister.magoo (1/9/2013)
If you are using xlsx files, there is a way that is not too difficult if you are methodical...
- Create your template file with formatting and some sample data.
- Use your favourite zip tool (I like 7zip) to unzip the XLSX file (it is a zip archive that contains xml files)
- Navigate to the xl\worksheets folder to find the xml file for your worksheet(s)
e.g. Sheet1.xml- Study the xml. You will see it contains a "sheetData" section which contains your sample data
<sheetData>
<row r="1" spans="1:4" x14ac:dyDescent="0.25">
<c r="A1" t="s">
<v>0</v>
</c>
<c r="B1" t="s">
<v>1</v>
</c>
<c r="C1" t="s">
<v>2</v>
</c>
<c r="D1" t="s">
<v>3</v>
</c>
</row>
<row r="2" spans="1:4" x14ac:dyDescent="0.25">
<c r="A2" s="4" t="s">
<v>4</v>
</c>
<c r="B2" s="3" t="s">
<v>5</v>
</c>
<c r="C2" s="2">
<v>4441234</v>
</c>
<c r="D2" s="1">
<v>24883</v>
</c>
</row>
<row r="3" spans="1:4" x14ac:dyDescent="0.25">
<c r="A3" s="4" t="s">
<v>4</v>
</c>
<c r="B3" s="3" t="s">
<v>5</v>
</c>
<c r="C3" s="2">
<v>4441234</v>
</c>
<c r="D3" s="1">
<v>24883</v>
</c>
</row>
</sheetData>
- Each "row" element represents a row in your sheet.
- Each "c" element represents a cell in the row
- The "r" attribute is the cell address
- the t="s" attribute tells Excel the cell contains a "Shared String" - read up on these - if you have a lot of repeating values you can use these to save space in the file. For Shared Strings, the "v" node contains the Shared String number.
- The "v" element is the cell value (or shared string number)
- Now all you have to do is reproduce the "Sheet1.xml" file in SSIS / SQL Query and then zip your new file along with the other supporting files back up into a new XLSX file that has all your original formatting
Wow.. that's one nice idea!
I have been trying to do something similar (getting a formatted excel out from SSIS) but apart from having to dive into the Excel API (there're quite a few like Aspose, Epplus) couldn't come up with something that was easy and/or works!
MM's idea here is really exciting, with the only challenge , it seems, being the generation of Sheet.xml (if i have been following this correctly)..
Could we not just have SSIS dump the data into an excel (unformatted and all) and unzip this excel first, which would give us the Sheet.xml we're looking for?
We could then just replace the other Sheet.xml (the one we have after unzipping the formatted/template excel) with this one and zip it together with the rest of the files/folders.
Do we really need to build a logic to create Sheet.xml when SSIS already does that for us when it creates the unformatted excel?
As I said , I may have not been following this correctly enough, but would love if someone could help me understand it any better..
But really appreciate this post by MM, looks like we're heading somewhere with this..
July 11, 2016 at 9:31 am
I agree that there is a fun project there. Looks to me like it should be possible, with some fancy T-SQL (a Script Component would be easier, but less flexible), to generate the <sheetData> XML.
The rest of the solution is easy enough, in principle, to implement.
An alternative approach might be to generate the unformatted spreadsheet and then 'poke' the required formatting tags (whatever they may look like) into the unpacked XML...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 30, 2017 at 10:44 am
This is probably too late but how about creating SSRS, creating subscription that never runs and kicking off this subscription from SSIS either to Excel file where formatting created by SSRS or send via email?
October 19, 2022 at 11:16 pm
The SSRS option can be a useful & less complex option if there is a lot of formatting to apply.
Another option to consider if the requirement relates to formatting based on the data is based on the "XLSX Template File" idea previously mentioned, and to define CONDITIONAL FORMATTING in the template to handle the formatting requirements.
For example: A conditional formatting rule on an entire column [C] that "highlights in yellow & bold" a value in column [J] if that row contains text "overdue" in column [F] and text "external" in column [T]
Setting as many conditional formatting rules in the XLSX template's columns as required enables exception formatting to be data-driven in the SSIS Excel output.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply