Trying to understand this from a n/w perspective.
We have multiple backup files that we try and copy(copy,xcopy or robocopy)
to a secondary server thats Gig attached on the same LAN. When we issue one
DOS Session to start copying an individual file, why doesnt it use the
entire throughput of the NIC ...as an example, the bytes sent/sec might be
say 2000000, when we invoke another session and start copying another file,
the bytes sent/sec now jumps to say around 3500000. So my question is why
with one DOS Session, it does not push at 3500000 . Is there a DOS
Limitation ? Trying to understand the n/w concepts here. The numbers I have
listed are not accurate but always noticed a major increase when multiple
sessions are opened for the file copying..
I know this is not a SQL Server related question but any help here would be
appreciated from the experts hereHassan,
There may be other limiting factors than the network bandwith. For instance
you have the Disk I/O, the controller's channel and bus bandwidth, cpu etc.
If the file being read from a single thread or in parallel? I would check
perfmon to ensure you don't have other bottlenecks affecting the tests.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OzV9xuKhDHA.1008@.TK2MSFTNGP12.phx.gbl...
> Trying to understand this from a n/w perspective.
> We have multiple backup files that we try and copy(copy,xcopy or robocopy)
> to a secondary server thats Gig attached on the same LAN. When we issue
one
> DOS Session to start copying an individual file, why doesnt it use the
> entire throughput of the NIC ...as an example, the bytes sent/sec might be
> say 2000000, when we invoke another session and start copying another
file,
> the bytes sent/sec now jumps to say around 3500000. So my question is why
> with one DOS Session, it does not push at 3500000 . Is there a DOS
> Limitation ? Trying to understand the n/w concepts here. The numbers I
have
> listed are not accurate but always noticed a major increase when multiple
> sessions are opened for the file copying..
> I know this is not a SQL Server related question but any help here would
be
> appreciated from the experts here
>|||With one DOS session, it looks like it can peak at a certain rate and then
when i open more and start copying other files, it increases and if i
cancel/close some sessions, i can see it decrease and was wondering why one
session cant take it all at first and if I open more DOS sessions, the rate
should remain the same ...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uAfiPvPhDHA.1340@.tk2msftngp13.phx.gbl...
> Hassan,
> There may be other limiting factors than the network bandwith. For
instance
> you have the Disk I/O, the controller's channel and bus bandwidth, cpu
etc.
> If the file being read from a single thread or in parallel? I would check
> perfmon to ensure you don't have other bottlenecks affecting the tests.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OzV9xuKhDHA.1008@.TK2MSFTNGP12.phx.gbl...
> > Trying to understand this from a n/w perspective.
> > We have multiple backup files that we try and copy(copy,xcopy or
robocopy)
> > to a secondary server thats Gig attached on the same LAN. When we issue
> one
> > DOS Session to start copying an individual file, why doesnt it use the
> > entire throughput of the NIC ...as an example, the bytes sent/sec might
be
> > say 2000000, when we invoke another session and start copying another
> file,
> > the bytes sent/sec now jumps to say around 3500000. So my question is
why
> > with one DOS Session, it does not push at 3500000 . Is there a DOS
> > Limitation ? Trying to understand the n/w concepts here. The numbers I
> have
> > listed are not accurate but always noticed a major increase when
multiple
> > sessions are opened for the file copying..
> >
> > I know this is not a SQL Server related question but any help here would
> be
> > appreciated from the experts here
> >
> >
>|||I see now. Not sure why that is unless each DOS session is just single
threaded and can't read as fast as the network can handle.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23wqnmaRhDHA.3272@.tk2msftngp13.phx.gbl...
> With one DOS session, it looks like it can peak at a certain rate and then
> when i open more and start copying other files, it increases and if i
> cancel/close some sessions, i can see it decrease and was wondering why
one
> session cant take it all at first and if I open more DOS sessions, the
rate
> should remain the same ...
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uAfiPvPhDHA.1340@.tk2msftngp13.phx.gbl...
> > Hassan,
> >
> > There may be other limiting factors than the network bandwith. For
> instance
> > you have the Disk I/O, the controller's channel and bus bandwidth, cpu
> etc.
> > If the file being read from a single thread or in parallel? I would
check
> > perfmon to ensure you don't have other bottlenecks affecting the tests.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OzV9xuKhDHA.1008@.TK2MSFTNGP12.phx.gbl...
> > > Trying to understand this from a n/w perspective.
> > > We have multiple backup files that we try and copy(copy,xcopy or
> robocopy)
> > > to a secondary server thats Gig attached on the same LAN. When we
issue
> > one
> > > DOS Session to start copying an individual file, why doesnt it use the
> > > entire throughput of the NIC ...as an example, the bytes sent/sec
might
> be
> > > say 2000000, when we invoke another session and start copying another
> > file,
> > > the bytes sent/sec now jumps to say around 3500000. So my question is
> why
> > > with one DOS Session, it does not push at 3500000 . Is there a DOS
> > > Limitation ? Trying to understand the n/w concepts here. The numbers I
> > have
> > > listed are not accurate but always noticed a major increase when
> multiple
> > > sessions are opened for the file copying..
> > >
> > > I know this is not a SQL Server related question but any help here
would
> > be
> > > appreciated from the experts here
> > >
> > >
> >
> >
>
Showing posts with label files. Show all posts
Showing posts with label files. Show all posts
Wednesday, March 28, 2012
Saturday, February 25, 2012
need urgent help with directory structure...
I accidently changed the directory sub folders orders in the
c:\Program Files\Microsoft SQL Server\MSSQL.3 folder (i believe it's
for reporting service)
now the reporting service won't start.
can anyone tell me what the sub direcotries should look like under that
directory?
thank you!!!Reporting Services
\LogFiles
\ReportManager
\...
\ReportServer
\...
\RSTempFiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1143834522.169647.108420@.i39g2000cwa.googlegroups.com...
>I accidently changed the directory sub folders orders in the
> c:\Program Files\Microsoft SQL Server\MSSQL.3 folder (i believe it's
> for reporting service)
> now the reporting service won't start.
> can anyone tell me what the sub direcotries should look like under that
> directory?
> thank you!!!
>
c:\Program Files\Microsoft SQL Server\MSSQL.3 folder (i believe it's
for reporting service)
now the reporting service won't start.
can anyone tell me what the sub direcotries should look like under that
directory?
thank you!!!Reporting Services
\LogFiles
\ReportManager
\...
\ReportServer
\...
\RSTempFiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1143834522.169647.108420@.i39g2000cwa.googlegroups.com...
>I accidently changed the directory sub folders orders in the
> c:\Program Files\Microsoft SQL Server\MSSQL.3 folder (i believe it's
> for reporting service)
> now the reporting service won't start.
> can anyone tell me what the sub direcotries should look like under that
> directory?
> thank you!!!
>
Monday, February 20, 2012
Need to update old area codes
I have files with area codes that are several years old. Everything I've seen about updating area codes deals with area codes that are current and are about to split in the near future. How would I go about bringing old area codes up to date?It's based on the area code and the exchange...go to usps site...|||Er, Brett, are you sure you want to point sync to the Postal Service? Why would they give a rat's ass about area codes?
Could you provide a URL?
I would love to have a consistant source of Area codes, not to mention international codes. right now I have to search around scrubbing various web sites for up to date lists.|||bpdWork, Which web sites are you using to get your lists? At the moment I'm not even sure what kind of data I need to do the job.|||Originally posted by sync
bpdWork, Which web sites are you using to get your lists? At the moment I'm not even sure what kind of data I need to do the job.
http://www.nanpa.com/nanp1/AllCodes.zip|||Thank you jbroyles. It seems that in order to use the NANPA data, the area codes that need updating must have dates associated with them. Is that correct? My data does not have dates.
Peter|||Originally posted by sync
Thank you jbroyles. It seems that in order to use the NANPA data, the area codes that need updating must have dates associated with them. Is that correct? My data does not have dates.
Peter
It seems you would be correct.|||bump|||[doooh]
postal service
[/dooh]
I used to have a linke...it might have been ATT...
try this
http://www.vzmultimedia.com/customersupport/areacodes/recent_changes.html
or this
http://www.consumer.att.com/global/english/usa_codes.html
There used to be a conversion list using exchanges....
Could you provide a URL?
I would love to have a consistant source of Area codes, not to mention international codes. right now I have to search around scrubbing various web sites for up to date lists.|||bpdWork, Which web sites are you using to get your lists? At the moment I'm not even sure what kind of data I need to do the job.|||Originally posted by sync
bpdWork, Which web sites are you using to get your lists? At the moment I'm not even sure what kind of data I need to do the job.
http://www.nanpa.com/nanp1/AllCodes.zip|||Thank you jbroyles. It seems that in order to use the NANPA data, the area codes that need updating must have dates associated with them. Is that correct? My data does not have dates.
Peter|||Originally posted by sync
Thank you jbroyles. It seems that in order to use the NANPA data, the area codes that need updating must have dates associated with them. Is that correct? My data does not have dates.
Peter
It seems you would be correct.|||bump|||[doooh]
postal service
[/dooh]
I used to have a linke...it might have been ATT...
try this
http://www.vzmultimedia.com/customersupport/areacodes/recent_changes.html
or this
http://www.consumer.att.com/global/english/usa_codes.html
There used to be a conversion list using exchanges....
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.
================================================== ====
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.
================================================== ====
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/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/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/subscript...rt/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
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/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/subscript...rt/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:
287739 XL2002: Error Message: The Following Features from Your Workbook
Will Not Be Saved in the XML Spreadsheet
http://support.microsoft.com/defaul...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.
========================================
==============
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/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/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/subscript...rt/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
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/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/subscript...rt/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:
287739 XL2002: Error Message: The Following Features from Your Workbook
Will Not Be Saved in the XML Spreadsheet
http://support.microsoft.com/defaul...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.
========================================
==============
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:
287739 XL2002: 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.
======================================================
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:
287739 XL2002: 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.
======================================================
Need to transfer many DBF files, while editing them at the same time
Alright, so here's my issue. I have a bunch of .dbf files, about 40 or so by summer's end, I'd say. These dbf files somewhat differ from each other as far as number of columns and data is concerned. I need to transfer all of these dbf files over to a SQL Server database so that I may write ASP pages for pulling statistics from them.
Basically, I have a table in the dbf with 3 columns. There's Column ID, Column A, and Column B. Column A is supposed to be there, and has X data. Column B is NOT supposed to be there, and has Y data. Now, each DBF file may or may not have Column B. If Column B DOESN'T exist, it's a simple matter of copying over the entire table to the SQL Server.
how do you assign new ID?|||I should use a temporary table ##table_name and insert there the A column, then the B column, truncate the original table and insert the data from the ##table_name back into the original table. (You will have to write a cursor to enforce the numbering)|||
I apologize, due to a case of misinformation among my fellow co-workers, I seem to have recieved the wrong information on this subject. Let me start over.
My problem is, this needs to be done in as easy a manner as possible, because we'll be getting more and more dbf files as we go along, and who knows what I'll be doing next summer when we'll probably get another 50 or so of these dbf files, so I can't upload them myself every time.
My idea is, I want to write an ASP page that scans a directory, checks for new dbf files (or changed dbf files, based on creation date), then reads all of the columns. If a specific column exists, the ASP page will do some extra stuff (see below) then upload to the SQL Server, if the column doesn't exist, it should just be a simple read and upload.
Now, if that specific column exists, what needs to be done is that each row must be checked, if data exists for that column, the entire row must be copied and re-inserted again with the data in that column replacing the data in another column. I hope that isn't too confusing.
Now here's my question, is this at all viable? Is it going to be quick and painless or will it take forever? Will I run into timeout issues?
Is ASP even the right language for me to be writing this in? I have experience with vb.net so I could write it in that if necessary.
I need this to be as easy and idiot-proof as possible, so that anyone can simply copy the dbf file to a specific folder, visit the index page of my stats web app for the files, and then simply wait for a short while as the new dbf file is processed and uploaded. Can this be done?
MGM outI don't get the idea exactly. You could mount the dbf's into sql server and then analyze it all there with some sp's. Thats an easier way I think.
I didn't get the thing about rewriting the record. Please provide more information|||
Quote:
Originally Posted by space1000
I don't get the idea exactly. You could mount the dbf's into sql server and then analyze it all there with some sp's. Thats an easier way I think.
I didn't get the thing about rewriting the record. Please provide more information
Basically, I have a table in the dbf with 3 columns. There's Column ID, Column A, and Column B. Column A is supposed to be there, and has X data. Column B is NOT supposed to be there, and has Y data. Now, each DBF file may or may not have Column B. If Column B DOESN'T exist, it's a simple matter of copying over the entire table to the SQL Server.
If Column B DOES exist, then we go through the entire table row by row. If the row has data (ie: Y data) in Column B, then we copy the entire row, give it a new ID, and replace whatever data was under Column A with Y data (the data that was under Column B), then remove Column B entirely.
I hope that explains it well...
Would it be fast if I first went though and checked for Column B, if it exists, to do the row adding as stated above, and THEN upload to SQL Server? Would I have to upload row by row or is there a quicker way?
MGM out|||
Quote:
Originally Posted by MGM
Basically, I have a table in the dbf with 3 columns. There's Column ID, Column A, and Column B. Column A is supposed to be there, and has X data. Column B is NOT supposed to be there, and has Y data. Now, each DBF file may or may not have Column B. If Column B DOESN'T exist, it's a simple matter of copying over the entire table to the SQL Server.
If Column B DOES exist, then we go through the entire table row by row. If the row has data (ie: Y data) in Column B, then we copy the entire row, give it a new ID, and replace whatever data was under Column A with Y data (the data that was under Column B), then remove Column B entirely.
I hope that explains it well...
Would it be fast if I first went though and checked for Column B, if it exists, to do the row adding as stated above, and THEN upload to SQL Server? Would I have to upload row by row or is there a quicker way?
MGM out
how do you assign new ID?|||I should use a temporary table ##table_name and insert there the A column, then the B column, truncate the original table and insert the data from the ##table_name back into the original table. (You will have to write a cursor to enforce the numbering)|||
Quote:
Originally Posted by ck9663
how do you assign new ID?
Quote:
Originally Posted by space1000
I should use a temporary table ##table_name and insert there the A column, then the B column, truncate the original table and insert the data from the ##table_name back into the original table. (You will have to write a cursor to enforce the numbering)
I apologize, due to a case of misinformation among my fellow co-workers, I seem to have recieved the wrong information on this subject. Let me start over.
We have 4 columns. We have ColID, ColNameA, ColNameB, and ColQuantityA. Now, most dbf files won't have the ColQuantityA, in which case the entire table simply needs to be uploaded to SQL Server.
Some DBF files WILL have the ColQuantityA column. If it exists, what I need to do is to copy over what's in ColNameA into ColNameB to signify that more than one exists. That is to say, ColNameA represents, for example, a restaurant in a city. If the ColQuantityA column exists and there's a number more than 1, then that means more than one restaurant exists in that city. So what I need to do, is to copy the exact name over from ColNameA into ColNameB to show that more than one exists. ColID won't be edited as I stated before, it will always stay the same for that row, and no new rows will be added either.
I hope I wrote that clearly enough. With all that in mind, what would be the best way (ie: fastest, easiest) to upload the DBF files to SQL Server taking into account that many row edits may be needed for some DBF files.
MGM out|||Has anyone come up with a solution yet? I would really like to get back to work on this project but cannot do so while this problem still exists...
MGM out
Subscribe to:
Posts (Atom)