Need to transform table data to excel 2003 files

Hi,
Can I use SQL Server Integration Services to generate Excel 2003 spread
sheets based on or database data?
We already have Excel 2003 templates and we need to generate excel 2003 xls
files based on our database table data. What would b the best way to do
that?
Any help would be appreciated,
Max
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:O1R9oeXGHHA.3616@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Can I use SQL Server Integration Services to generate Excel 2003 spread
> sheets based on or database data?
> We already have Excel 2003 templates and we need to generate excel 2003
> xls files based on our database table data. What would b the best way to
> do that?
I did something like this once for an Intranet reporting application. The
best solution (easy and performant) that I came up with was to grab the data
using SELECT ... FOR XML and perform an XSL transformation on it. Excel
2003 has a rich set of features in the XML format (though I don't think you
can embed tables and charts in the Excel XML format files).
|||Hello Mike,
To understand the issue better, I'd like to know how you want to use the
2003 template when transform the data from database to spreedsheet. An
example might be more clear.
You shall be able to export data directly to Excel file in SQL Server. In
Management Studio (2005), right click the database->Tasks->Export data,
follow the wizard to select source and select Microsoft Excle as
destination, and select the destination path of excel file.
Also, in SSIS, you could be more flexisble to get the source of the data.
1. You could use the OLEDB or datareader data source, and use select SQL
query to get the data you want.
2. You could use Excel Destination as the data destination and select Excel
file or create new Excel as you want.
3. YOu also be able to select the proper columns for mapping in able object
and use other data folow transformation if necessary.
As Mike mentioned, you could use "SELECT ... FOR XML" to get XML format of
the data and do transformation by using XSL.
As for as I know, Excel destination does not include configuration to
create new file from xlt template. This might be finished before
transformation by using an Activex scipt task or a Script task. For example
by using Activex script task you may use code like:
Dim sTemplatePath, sTargetPath, sSourceFileName, sTargetFileName
sSourceFileName = "temp.xlt"
sTargetFileName = "test.xls"
sTemplatePath = "C:\"
sTargetPath = "C:\"
sTargetFileName = sTargetPath & year(now) & "-" & month(now) & "-" &
day(now) - 1 & " " & sTargetFileName
DTSGlobalVariables("sFileName").Value = sTargetFileName
sSourceFileName = sTemplatePath & sSourceFileName
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile sSourceFileName, sTargetFileName
Set oFSO = Nothing
If you have any update or comments, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:32UR4gaGHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Mike,
> To understand the issue better, I'd like to know how you want to use the
> 2003 template when transform the data from database to spreedsheet. An
> example might be more clear.
I don't "want to", I already "did". It's been done. Now if you really want
to help me instead of Max, tell me how to embed charts and graphics in Excel
XML format documents.

> You shall be able to export data directly to Excel file in SQL Server. In
> Management Studio (2005), right click the database->Tasks->Export data,
> follow the wizard to select source and select Microsoft Excle as
> destination, and select the destination path of excel file.
> Also, in SSIS, you could be more flexisble to get the source of the data.
> 1. You could use the OLEDB or datareader data source, and use select SQL
> query to get the data you want.
> 2. You could use Excel Destination as the data destination and select
> Excel
> file or create new Excel as you want.
> 3. YOu also be able to select the proper columns for mapping in able
> object
> and use other data folow transformation if necessary.
> As Mike mentioned, you could use "SELECT ... FOR XML" to get XML format of
> the data and do transformation by using XSL.
> As for as I know, Excel destination does not include configuration to
> create new file from xlt template. This might be finished before
> transformation by using an Activex scipt task or a Script task. For
> example
> by using Activex script task you may use code like:
>
> Dim sTemplatePath, sTargetPath, sSourceFileName, sTargetFileName
> sSourceFileName = "temp.xlt"
> sTargetFileName = "test.xls"
> sTemplatePath = "C:\"
> sTargetPath = "C:\"
> sTargetFileName = sTargetPath & year(now) & "-" & month(now) & "-" &
> day(now) - 1 & " " & sTargetFileName
> DTSGlobalVariables("sFileName").Value = sTargetFileName
> sSourceFileName = sTemplatePath & sSourceFileName
> Dim oFSO
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> oFSO.CopyFile sSourceFileName, sTargetFileName
> Set oFSO = Nothing
> If you have any update or comments, please feel free to let's know. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hello Mike,
Sorry for my fault. My reply is for Max insead of you.
The XML Spreadsheet format describes the contents of the workbook by
persisting cell data and formulas, cell formats, worksheet settings, and
workbook settings. Some Excel features cannot be persisted in XML. The
following Excel features cannot be persisted in XML: Charts
OLE Objects
Drawing shapes or AutoShapes
VBA Projects
Group and Outline
Please see the following article for details:
287739XL2002: Error Message: The Following Features from Your Workbook
Will Not Be Saved in the XML Spreadsheet
http://support.microsoft.com/default.aspx?scid=kb;EN-US;287739
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====