DTS from XML

Does anyone know how to create a dts package that will take a XML document and import the contents to a new table in sql.
I have looked in the tasks of DTS and cannot find the xml connection.
Any pointers to some samples would be great.
ThanksConsider upgrading to SQL Server 2005, which has extended support for XML including data import through the Integration Services application. (Interestingly, Integration Services does NOT export XML. Go figure...).sql

DTS for Import Export TO And From EXCEL

Hi All,
I want to design a DTS Package that will read an EXCEL Document (One Data
Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
will have a JOIN from Both the source and Export the result to another Excel
Document.
How Can I perform that using DTS?
I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
And 3) Excel Connection for Export the Result.
My Requirement is to get the value from One of the column from one of the
Sheet and use that values to get a Joined Record from TWO tables of SQL
Server.
Ex: -
Sheet2$ : Having Column "EmployeeID" with 100 rows.
IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
I want to export the LIST of the Departments for the Employee that are in
the Excel Sheet2.
Please Suggest how can I do that or any Better solution using DTS.
Thanks
PrabhatYou could use OPENDATASOURCE
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions
Or you can create a linked server of the source XL spreadsheet from the
SQL Server. You then query that and export to XL destination.
You cannot use the Excel connections to do this ........Yet.
Allan
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:not_a_mail@.hotmail.com:

> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another Exc
el
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat|||306397 How To Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another
> Excel
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat
>|||"Douglas Laudenschlager [MS]" <douglasl@.online.microsoft.com> wrote in
message news:OOnZmB$XFHA.2884@.tk2msftngp13.phx.gbl...
> 306397 How To Use Excel with SQL Server Linked Servers and Distributed
> Queries
> http://support.microsoft.com/?id=306397
> -Doug
> --
> Douglas Laudenschlager
> Microsoft SQL Server documentation team
> Redmond, Washington, USA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
Data
the
in
>

DTS drawbacks

I am trying to compile a case of why DTS packages need to be migrated to SSIS packages. Is there any document, whitepaper which can provide me drawbacks in DTS?

Thanks,

Vijay.

This will be completely situation dependent, and my reasons are different from yours... For us, it was simply the upgrade to SQL Server 2005. Some things continued to work, others didn't, so we converted packages.

But have you looked at the feature list of SSIS and compared it to DTS to build your own conclusions?

Phil|||I am aware of new features in SSIS like control-flow, data-flow demarcation, new transforms like SCD etc. But knowing some drawbacks in DTS like performance, scalability, support for XML can help. If there is any inputs on specific drawbacks in DTS as compared to SSIS, it will be helpful.|||

Vijay Thirugnanam wrote:

I am aware of new features in SSIS like control-flow, data-flow demarcation, new transforms like SCD etc. But knowing some drawbacks in DTS like performance, scalability, support for XML can help. If there is any inputs on specific drawbacks in DTS as compared to SSIS, it will be helpful.

SSIS performs and scales better than DTS by an order of magnitude.

As opposed to SSIS, DTS very much relies on the staging of data in order to transform it into something suitable for populating a data warehouse. SSIS very much relies on in-memory buffers to transform data, obviating the much of need to stage such data.

Also, DTS typically requires a significantly greater amount of custom coding to accomplish that which can be accomplished with SSIS.

|||

Try performing a loop in DTS (where you loop among various tasks not just inside and ActiveX script task) then try it in SSIS.

Also take a look at all of the work required to make a DTS package portable (able to move from test to prod without making changes to it) and compare to SSIS.

On this last see http://www.mutuallybeneficial.com/index_files/dts_ssis_packages_portable.htm for more info.

DTS document or ebook

Hi,

I need deep knowledge about DTS tasks. Can anyone send me an e-book or
related document about DTS packages. (Links on the Internet site are
generally advertisement or not working links).

Thanks,

Veysel Can DemirSQL Server Books Online is a good place to start. Then check out the
following sites:

www.sqldts.com
www.sqldev.net
http://vyaskn.tripod.com/sql_server...t_practices.htm

Here are some SQL Server DTS books:
http://vyaskn.tripod.com/sqlbooks.htm#dts
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/

"Veysel" <veysel_can_demir@.yahoo.co.uk> wrote in message
news:7d1e7961.0408110845.3d69a25d@.posting.google.c om...
> Hi,
> I need deep knowledge about DTS tasks. Can anyone send me an e-book or
> related document about DTS packages. (Links on the Internet site are
> generally advertisement or not working links).
> Thanks,
> Veysel Can Demir|||
Hi,

First of all thanks for answer. But there is a little problem, I think
that you did not understand me. I do not want book advertisements. I
need free e-books or pdf documents that I can teach me DTS.

Thanks,

Veysel Can

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||www.sqldts.com is a good place for info

DTC & SQL Server 2000 Security

Perhaps someone can answer this question. I was given a document from a Microsoft consultant and setting up a secure SQL Server 2000 server for my company. This document is being used to setup standards for our enterprise implementation of SQL Servers.

In the document, it has a procedure of disabling the MSDTC service stating that, "the DTC can be used by COM+ applications to manage transactions across multiple database servers." But it really doesn't suffieniently state why this is a good security practice. Can anyone out there tell me why this is a good idea.

Background: We use the Standard Edition, and currently have no distributed servers out there, nor do we have replication. A side note, just recently we have been asked to install a replicated server for fail-over capability. Will disabling the DTC service hinder us?

gpl :-)

Replication will only be affected if you have Immediate Updating subscriptions to transactional publications. Immediate Updating subscribers require MSDTC, since it's a 2 phase commit transaction relying on MSDTC service running.