Is it advicable to use DTS package as a tranformation media in transactional
replication or should transformation be done at Stored Procedure level on
Subscriber?
Under what circumstances it will be a good idea to use DTS package.
Thank you very much.
only use transformable subscription with hetergeneous subscribers. For SQL
Server subscribers there are other ways (stored procedures being one of
them) of transforming the data in route which offer far greater performance.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:C9B7E873-C6A2-40CC-8EE5-0DAC1D1A7508@.microsoft.com...
> Is it advicable to use DTS package as a tranformation media in
> transactional
> replication or should transformation be done at Stored Procedure level on
> Subscriber?
> Under what circumstances it will be a good idea to use DTS package.
> Thank you very much.
Showing posts with label dts. Show all posts
Showing posts with label dts. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
advicce needed, which task to choose in DTS package
Hi guys, I need to bits of advice.
I get data from several tables in database A and export it into database B. In this instance both databases are SQL I use a DDQ task to do this with VB script to provide the logic to insert the record if it dosnt exist and update the record if it does. What I would like to know is if there is a more effcient task that can perform this function, something that dosnt require the vb script
The 2nd piece of advice I'd like to know is concerning getting data from an oracle database and importing it into a sql database through the use of csv files. This process very slow (up to 8 hours). Is there way of using some kind of DTS package to speed things up? Or is that not possible.
many thanks
SQL2005 Integration Services would be great for this scenario. You can add a lookup task and handle your control of flow all in the one package. If you're using SQL2K, could you perhaps dump all the data into a holding table and then run a simple INSERT statement to put in the new values?
If you're taking data from Oracle, you could just create a DTS/SSIS package to query Oracle direct and pump the data into your SQL Server. I'd have thought this would be the most efficient way.
HTH!
|||Thanks for that. In the case of using a datapump in SQL2k, do yuo have an example of some tsql I could use to insert new records and update existing ones?
many thanks
|||I think you'd need a DTS package with the following flow:
SQLConn1.SrcTable -> DDQ -> SQLConn2.HoldingTable -> T-SQL Task
Your T-SQL Task could be an SQL stored procedure which did something like the following:
Code Snippet
CREATE PROC AddRecords
AS
UPDATE DstTable
SET Col1 = Hld.Col1, Col2 = Hld.Col2
FROM DstTable Dst
INNER JOIN HoldingTable Hld
ON Dst.Col4 = Hld.Col4
INSERT INTO DstTable
SELECT *
FROM HoldingTable Hld
WHERE NOT EXISTS (SELECT * FROM DstTable Dst WHERE Dst.Col4 = Hld.Col4)
DELETE
FROM HoldingTable
Of course, this is quite simplified. You'll need to change your JOIN and EXISTS clause to include all the columns which define a record as being unique.
You may also want to break this stored procedure up into 3 T-SQL tasks which you could run after the DDQ.
HTH!
|||Thanks for that.
advicce needed, which task to choose in DTS package
Hi guys, I need to bits of advice.
I get data from several tables in database A and export it into database B. In this instance both databases are SQL I use a DDQ task to do this with VB script to provide the logic to insert the record if it dosnt exist and update the record if it does. What I would like to know is if there is a more effcient task that can perform this function, something that dosnt require the vb script
The 2nd piece of advice I'd like to know is concerning getting data from an oracle database and importing it into a sql database through the use of csv files. This process very slow (up to 8 hours). Is there way of using some kind of DTS package to speed things up? Or is that not possible.
many thanks
SQL2005 Integration Services would be great for this scenario. You can add a lookup task and handle your control of flow all in the one package. If you're using SQL2K, could you perhaps dump all the data into a holding table and then run a simple INSERT statement to put in the new values?
If you're taking data from Oracle, you could just create a DTS/SSIS package to query Oracle direct and pump the data into your SQL Server. I'd have thought this would be the most efficient way.
HTH!
|||Thanks for that. In the case of using a datapump in SQL2k, do yuo have an example of some tsql I could use to insert new records and update existing ones?
many thanks
|||I think you'd need a DTS package with the following flow:
SQLConn1.SrcTable -> DDQ -> SQLConn2.HoldingTable -> T-SQL Task
Your T-SQL Task could be an SQL stored procedure which did something like the following:
Code Snippet
CREATE PROC AddRecords
AS
UPDATE DstTable
SET Col1 = Hld.Col1, Col2 = Hld.Col2
FROM DstTable Dst
INNER JOIN HoldingTable Hld
ON Dst.Col4 = Hld.Col4
INSERT INTO DstTable
SELECT *
FROM HoldingTable Hld
WHERE NOT EXISTS (SELECT * FROM DstTable Dst WHERE Dst.Col4 = Hld.Col4)
DELETE
FROM HoldingTable
Of course, this is quite simplified. You'll need to change your JOIN and EXISTS clause to include all the columns which define a record as being unique.
You may also want to break this stored procedure up into 3 T-SQL tasks which you could run after the DDQ.
HTH!
|||Thanks for that.
Thursday, March 22, 2012
Adventures with 64 bit SQL servers and legacy systems
First of all, thanks to everyone who helped me figure out how to run a
legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
else has this problelm, you also have to download this from Microsoft:
Microsoft SQL Server 2000 DTS Designer Components
Now, my question is this: We have two critical procedures that use the
open query via linked server. I don't think I can modify this to use
DTS. I am having trouble creating a linked server with the 32 bit
driver. It works on the 2000 box and I have had difficulty finding
documentation of how to successfully do this. Erro is as follows:
Cannot initialize the data source object of OLE DB provider - Is there
any way I can modify the default drivers - I need the old 32 bit ODBC
driver that doesn't appear in the list to pull in our Thorougbred
Basic legacy data...
Thanks - any ideas would be appreciated.
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1174072527.860042.123270@.n76g2000hsh.googlegr oups.com...
> First of all, thanks to everyone who helped me figure out how to run a
> legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
> else has this problelm, you also have to download this from Microsoft:
> Microsoft SQL Server 2000 DTS Designer Components
> Now, my question is this: We have two critical procedures that use the
> open query via linked server. I don't think I can modify this to use
> DTS. I am having trouble creating a linked server with the 32 bit
> driver. It works on the 2000 box and I have had difficulty finding
> documentation of how to successfully do this. Erro is as follows:
> Cannot initialize the data source object of OLE DB provider - Is there
> any way I can modify the default drivers - I need the old 32 bit ODBC
> driver that doesn't appear in the list to pull in our Thorougbred
> Basic legacy data...
> Thanks - any ideas would be appreciated.
>
You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
always run a 32bit instance of SQL 2005 on 64bit Windows.
David
|||On Mar 16, 5:05 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1174072527.860042.123270@.n76g2000hsh.googlegr oups.com...
>
>
>
>
>
> You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
> always run a 32bit instance of SQL 2005 on 64bit Windows.
> David- Hide quoted text -
> - Show quoted text -
I know, that was my suggestion, but I was out-voted by the network
administrators...thanks for the reply. I will have to find a way to
work around this.
legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
else has this problelm, you also have to download this from Microsoft:
Microsoft SQL Server 2000 DTS Designer Components
Now, my question is this: We have two critical procedures that use the
open query via linked server. I don't think I can modify this to use
DTS. I am having trouble creating a linked server with the 32 bit
driver. It works on the 2000 box and I have had difficulty finding
documentation of how to successfully do this. Erro is as follows:
Cannot initialize the data source object of OLE DB provider - Is there
any way I can modify the default drivers - I need the old 32 bit ODBC
driver that doesn't appear in the list to pull in our Thorougbred
Basic legacy data...
Thanks - any ideas would be appreciated.
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1174072527.860042.123270@.n76g2000hsh.googlegr oups.com...
> First of all, thanks to everyone who helped me figure out how to run a
> legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
> else has this problelm, you also have to download this from Microsoft:
> Microsoft SQL Server 2000 DTS Designer Components
> Now, my question is this: We have two critical procedures that use the
> open query via linked server. I don't think I can modify this to use
> DTS. I am having trouble creating a linked server with the 32 bit
> driver. It works on the 2000 box and I have had difficulty finding
> documentation of how to successfully do this. Erro is as follows:
> Cannot initialize the data source object of OLE DB provider - Is there
> any way I can modify the default drivers - I need the old 32 bit ODBC
> driver that doesn't appear in the list to pull in our Thorougbred
> Basic legacy data...
> Thanks - any ideas would be appreciated.
>
You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
always run a 32bit instance of SQL 2005 on 64bit Windows.
David
|||On Mar 16, 5:05 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1174072527.860042.123270@.n76g2000hsh.googlegr oups.com...
>
>
>
>
>
> You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
> always run a 32bit instance of SQL 2005 on 64bit Windows.
> David- Hide quoted text -
> - Show quoted text -
I know, that was my suggestion, but I was out-voted by the network
administrators...thanks for the reply. I will have to find a way to
work around this.
Adventures with 64 bit SQL servers and legacy systems
First of all, thanks to everyone who helped me figure out how to run a
legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
else has this problelm, you also have to download this from Microsoft:
Microsoft SQL Server 2000 DTS Designer Components
Now, my question is this: We have two critical procedures that use the
open query via linked server. I don't think I can modify this to use
DTS. I am having trouble creating a linked server with the 32 bit
driver. It works on the 2000 box and I have had difficulty finding
documentation of how to successfully do this. Erro is as follows:
Cannot initialize the data source object of OLE DB provider - Is there
any way I can modify the default drivers - I need the old 32 bit ODBC
driver that doesn't appear in the list to pull in our Thorougbred
Basic legacy data...
Thanks - any ideas would be appreciated."Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1174072527.860042.123270@.n76g2000hsh.googlegroups.com...
> First of all, thanks to everyone who helped me figure out how to run a
> legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
> else has this problelm, you also have to download this from Microsoft:
> Microsoft SQL Server 2000 DTS Designer Components
> Now, my question is this: We have two critical procedures that use the
> open query via linked server. I don't think I can modify this to use
> DTS. I am having trouble creating a linked server with the 32 bit
> driver. It works on the 2000 box and I have had difficulty finding
> documentation of how to successfully do this. Erro is as follows:
> Cannot initialize the data source object of OLE DB provider - Is there
> any way I can modify the default drivers - I need the old 32 bit ODBC
> driver that doesn't appear in the list to pull in our Thorougbred
> Basic legacy data...
> Thanks - any ideas would be appreciated.
>
You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
always run a 32bit instance of SQL 2005 on 64bit Windows.
David|||On Mar 16, 5:05 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1174072527.860042.123270@.n76g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
> You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
> always run a 32bit instance of SQL 2005 on 64bit Windows.
> David- Hide quoted text -
> - Show quoted text -
I know, that was my suggestion, but I was out-voted by the network
administrators...thanks for the reply. I will have to find a way to
work around this.
legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
else has this problelm, you also have to download this from Microsoft:
Microsoft SQL Server 2000 DTS Designer Components
Now, my question is this: We have two critical procedures that use the
open query via linked server. I don't think I can modify this to use
DTS. I am having trouble creating a linked server with the 32 bit
driver. It works on the 2000 box and I have had difficulty finding
documentation of how to successfully do this. Erro is as follows:
Cannot initialize the data source object of OLE DB provider - Is there
any way I can modify the default drivers - I need the old 32 bit ODBC
driver that doesn't appear in the list to pull in our Thorougbred
Basic legacy data...
Thanks - any ideas would be appreciated."Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1174072527.860042.123270@.n76g2000hsh.googlegroups.com...
> First of all, thanks to everyone who helped me figure out how to run a
> legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
> else has this problelm, you also have to download this from Microsoft:
> Microsoft SQL Server 2000 DTS Designer Components
> Now, my question is this: We have two critical procedures that use the
> open query via linked server. I don't think I can modify this to use
> DTS. I am having trouble creating a linked server with the 32 bit
> driver. It works on the 2000 box and I have had difficulty finding
> documentation of how to successfully do this. Erro is as follows:
> Cannot initialize the data source object of OLE DB provider - Is there
> any way I can modify the default drivers - I need the old 32 bit ODBC
> driver that doesn't appear in the list to pull in our Thorougbred
> Basic legacy data...
> Thanks - any ideas would be appreciated.
>
You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
always run a 32bit instance of SQL 2005 on 64bit Windows.
David|||On Mar 16, 5:05 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1174072527.860042.123270@.n76g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
> You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
> always run a 32bit instance of SQL 2005 on 64bit Windows.
> David- Hide quoted text -
> - Show quoted text -
I know, that was my suggestion, but I was out-voted by the network
administrators...thanks for the reply. I will have to find a way to
work around this.
Tuesday, March 20, 2012
Adventures with 64 bit SQL servers and legacy systems
First of all, thanks to everyone who helped me figure out how to run a
legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
else has this problelm, you also have to download this from Microsoft:
Microsoft SQL Server 2000 DTS Designer Components
Now, my question is this: We have two critical procedures that use the
open query via linked server. I don't think I can modify this to use
DTS. I am having trouble creating a linked server with the 32 bit
driver. It works on the 2000 box and I have had difficulty finding
documentation of how to successfully do this. Erro is as follows:
Cannot initialize the data source object of OLE DB provider - Is there
any way I can modify the default drivers - I need the old 32 bit ODBC
driver that doesn't appear in the list to pull in our Thorougbred
Basic legacy data...
Thanks - any ideas would be appreciated."Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1174072527.860042.123270@.n76g2000hsh.googlegroups.com...
> First of all, thanks to everyone who helped me figure out how to run a
> legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
> else has this problelm, you also have to download this from Microsoft:
> Microsoft SQL Server 2000 DTS Designer Components
> Now, my question is this: We have two critical procedures that use the
> open query via linked server. I don't think I can modify this to use
> DTS. I am having trouble creating a linked server with the 32 bit
> driver. It works on the 2000 box and I have had difficulty finding
> documentation of how to successfully do this. Erro is as follows:
> Cannot initialize the data source object of OLE DB provider - Is there
> any way I can modify the default drivers - I need the old 32 bit ODBC
> driver that doesn't appear in the list to pull in our Thorougbred
> Basic legacy data...
> Thanks - any ideas would be appreciated.
>
You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
always run a 32bit instance of SQL 2005 on 64bit Windows.
David|||On Mar 16, 5:05 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1174072527.860042.123270@.n76g2000hsh.googlegroups.com...
>
>
> > First of all, thanks to everyone who helped me figure out how to run a
> > legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
> > else has this problelm, you also have to download this from Microsoft:
> > Microsoft SQL Server 2000 DTS Designer Components
> > Now, my question is this: We have two critical procedures that use the
> > open query via linked server. I don't think I can modify this to use
> > DTS. I am having trouble creating a linked server with the 32 bit
> > driver. It works on the 2000 box and I have had difficulty finding
> > documentation of how to successfully do this. Erro is as follows:
> > Cannot initialize the data source object of OLE DB provider - Is there
> > any way I can modify the default drivers - I need the old 32 bit ODBC
> > driver that doesn't appear in the list to pull in our Thorougbred
> > Basic legacy data...
> > Thanks - any ideas would be appreciated.
> You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
> always run a 32bit instance of SQL 2005 on 64bit Windows.
> David- Hide quoted text -
> - Show quoted text -
I know, that was my suggestion, but I was out-voted by the network
administrators...thanks for the reply. I will have to find a way to
work around this.sql
legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
else has this problelm, you also have to download this from Microsoft:
Microsoft SQL Server 2000 DTS Designer Components
Now, my question is this: We have two critical procedures that use the
open query via linked server. I don't think I can modify this to use
DTS. I am having trouble creating a linked server with the 32 bit
driver. It works on the 2000 box and I have had difficulty finding
documentation of how to successfully do this. Erro is as follows:
Cannot initialize the data source object of OLE DB provider - Is there
any way I can modify the default drivers - I need the old 32 bit ODBC
driver that doesn't appear in the list to pull in our Thorougbred
Basic legacy data...
Thanks - any ideas would be appreciated."Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1174072527.860042.123270@.n76g2000hsh.googlegroups.com...
> First of all, thanks to everyone who helped me figure out how to run a
> legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
> else has this problelm, you also have to download this from Microsoft:
> Microsoft SQL Server 2000 DTS Designer Components
> Now, my question is this: We have two critical procedures that use the
> open query via linked server. I don't think I can modify this to use
> DTS. I am having trouble creating a linked server with the 32 bit
> driver. It works on the 2000 box and I have had difficulty finding
> documentation of how to successfully do this. Erro is as follows:
> Cannot initialize the data source object of OLE DB provider - Is there
> any way I can modify the default drivers - I need the old 32 bit ODBC
> driver that doesn't appear in the list to pull in our Thorougbred
> Basic legacy data...
> Thanks - any ideas would be appreciated.
>
You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
always run a 32bit instance of SQL 2005 on 64bit Windows.
David|||On Mar 16, 5:05 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1174072527.860042.123270@.n76g2000hsh.googlegroups.com...
>
>
> > First of all, thanks to everyone who helped me figure out how to run a
> > legacy DTS with a 32 bit driver on a 64 bit server. In case anyone
> > else has this problelm, you also have to download this from Microsoft:
> > Microsoft SQL Server 2000 DTS Designer Components
> > Now, my question is this: We have two critical procedures that use the
> > open query via linked server. I don't think I can modify this to use
> > DTS. I am having trouble creating a linked server with the 32 bit
> > driver. It works on the 2000 box and I have had difficulty finding
> > documentation of how to successfully do this. Erro is as follows:
> > Cannot initialize the data source object of OLE DB provider - Is there
> > any way I can modify the default drivers - I need the old 32 bit ODBC
> > driver that doesn't appear in the list to pull in our Thorougbred
> > Basic legacy data...
> > Thanks - any ideas would be appreciated.
> You can't use a 32bit OleDb provider in 64bit SQL Server. But you can
> always run a 32bit instance of SQL 2005 on 64bit Windows.
> David- Hide quoted text -
> - Show quoted text -
I know, that was my suggestion, but I was out-voted by the network
administrators...thanks for the reply. I will have to find a way to
work around this.sql
Monday, March 19, 2012
Advanced topics/features/usage of DTSs
Hello,
I know the basics of DTSs and have been using them for a while. Now I REALLY want to learn the advanced concepts related to DTS's e.g.
How to make use of the "Inner Package Global Variables" Tab (which is visible in the DTS Designer "window")
How to make use of the "Outer Package Global Variables" Tab (which is visible in the DTS Designer "window")
How to make use of the "Outer Package Global Variables" Tab (which is visible in the menu bar option #4 Designer "window")
Many advance thanks for your help.I know the basics of DTSs and have been using them for a while. Now I REALLY want to learn the advanced concepts related to DTS's
I can think of much better uses for your time. DTS was a klunky tool to begin with, poorly implemented and poorly integrated into the SQL Server environment. I would never have recommended that anybody use it for anything beyond simple data transfers, and now that it has been completely dropped from the latest SQL Server version 2005 it might be more useful for you to study Latin.|||Hmmm... Thanks for the feedback.
At the moment I don't think my college will be shifting to SQL 2005 and that is why I thought that I'd get better on it. However you have given me some useful food for thought by saying that SQL 2005 doesn't offer DTS's at all.|||I can think of much better uses for your time. DTS was a klunky tool to begin with, poorly implemented and poorly integrated into the SQL Server environment. I would never have recommended that anybody use it for anything beyond simple data transfers, and now that it has been completely dropped from the latest SQL Server version 2005 it might be more useful for you to study Latin.
Dammit man! Every time I feel smart, you tell me I've been doing it all wrong. Now I'm going to rewrite some huge processes. I hope we don't migrate to 2005 anytime soon.|||SQL Server 2005 dropped the DTS utility, and it has been replaced by something called Integration Services which is an even bigger monstrosity.
The two utilities have the same concept, but they are sufficiently different that in-depth knowledge of DTS packages is not going to help you with Integration Services.|||Will I still be able to execute ActiveX scripts?|||Amo, Amas, Amat, Amamus, Amatis, Amant...|||Hey, isn't that basically the Barney song in Latin?
("I love you, you love me...")
What's that have to do with ActiveX and DTS?|||yes, you can executed Active-X script, in more than one way in SSIS. You certainly can do it, and for getting DTS packages running quickly, it is really convenient... When you try to do "heavy lifting" with Active-X in SSIS, you'll be reminded very quickly just how many better choices SSIS offers!
Push for the conversion to SQL 2005. Push hard!
-PatP
I know the basics of DTSs and have been using them for a while. Now I REALLY want to learn the advanced concepts related to DTS's e.g.
How to make use of the "Inner Package Global Variables" Tab (which is visible in the DTS Designer "window")
How to make use of the "Outer Package Global Variables" Tab (which is visible in the DTS Designer "window")
How to make use of the "Outer Package Global Variables" Tab (which is visible in the menu bar option #4 Designer "window")
Many advance thanks for your help.I know the basics of DTSs and have been using them for a while. Now I REALLY want to learn the advanced concepts related to DTS's
I can think of much better uses for your time. DTS was a klunky tool to begin with, poorly implemented and poorly integrated into the SQL Server environment. I would never have recommended that anybody use it for anything beyond simple data transfers, and now that it has been completely dropped from the latest SQL Server version 2005 it might be more useful for you to study Latin.|||Hmmm... Thanks for the feedback.
At the moment I don't think my college will be shifting to SQL 2005 and that is why I thought that I'd get better on it. However you have given me some useful food for thought by saying that SQL 2005 doesn't offer DTS's at all.|||I can think of much better uses for your time. DTS was a klunky tool to begin with, poorly implemented and poorly integrated into the SQL Server environment. I would never have recommended that anybody use it for anything beyond simple data transfers, and now that it has been completely dropped from the latest SQL Server version 2005 it might be more useful for you to study Latin.
Dammit man! Every time I feel smart, you tell me I've been doing it all wrong. Now I'm going to rewrite some huge processes. I hope we don't migrate to 2005 anytime soon.|||SQL Server 2005 dropped the DTS utility, and it has been replaced by something called Integration Services which is an even bigger monstrosity.
The two utilities have the same concept, but they are sufficiently different that in-depth knowledge of DTS packages is not going to help you with Integration Services.|||Will I still be able to execute ActiveX scripts?|||Amo, Amas, Amat, Amamus, Amatis, Amant...|||Hey, isn't that basically the Barney song in Latin?
("I love you, you love me...")
What's that have to do with ActiveX and DTS?|||yes, you can executed Active-X script, in more than one way in SSIS. You certainly can do it, and for getting DTS packages running quickly, it is really convenient... When you try to do "heavy lifting" with Active-X in SSIS, you'll be reminded very quickly just how many better choices SSIS offers!
Push for the conversion to SQL 2005. Push hard!
-PatP
Sunday, March 11, 2012
advanced editor
I am used to DTS packages in sql server 2000 and now I want to use Integrated
services to create 'DTS' packages. Going to a .net users group meeting, I was
told to use the 'Advanced editor' since it would help me alot. However, I
can not find
the 'advanced editor' in sql server 2005. Thus can you tell me where the
'advanced editor'is and what are some good uses of the advanced editor?
Thanks!
Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> I am used to DTS packages in sql server 2000 and now I want to use
> Integrated services to create 'DTS' packages. Going to a .net users
> group meeting, I was told to use the 'Advanced editor' since it would
> help me alot. However, I can not find the 'advanced editor' in sql
> server 2005. Thus can you tell me where the 'advanced editor'is and what
> are some good uses of the advanced editor?
Are you looking in SQL Server Mangement Studio? You find precious little
support for Integration Services there. You should have a look at
Business Intelligence Development Studio (BIDS), which really is Visual
Studio under a custom name.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I did find it was in Business Intelligence part of Visual studio.net. But
once you are in BIS, where do you find the advanced editor?
Thanks!
Diane Stein
"Erland Sommarskog" wrote:
> Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> Are you looking in SQL Server Mangement Studio? You find precious little
> support for Integration Services there. You should have a look at
> Business Intelligence Development Studio (BIDS), which really is Visual
> Studio under a custom name.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> I did find it was in Business Intelligence part of Visual studio.net. But
> once you are in BIS, where do you find the advanced editor?
Now you asking me questions that goes over my head. :-) I don't use SSIS
much, or rather not at all. I just wanted to save you from looking in
SSMS. But isn't this "Advanced Editor" simply the Package Designer you
get when you create an Integration Services project?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The "Advanced Editor' most likely refers to a property dialog for SSIS
components that allows you to set any property available on that component
and configure a lot of settings beyond what's typically available on the
regular Properties dialog for the same component.
In general, to use new SSIS designer, you start BIDS application, and create
a new SSIS project (click New -> Project, and pick SSIS project from the
list). This will create a new SSIS package within a project and open it up.
From there you will see the package's design surface, where you can drop
control flow components from a toolbox window, connect them into a flow and
configure. If you right click on any component, you will see an 'Advanced
Editor' option. If you have more questions, there is a separate SSIS forum
where you will find more experts ready to answer those.
Regards,
Maciek Sarnowicz
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99E4C3B06E16BYazorman@.127.0.0.1...
> Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> Now you asking me questions that goes over my head. :-) I don't use SSIS
> much, or rather not at all. I just wanted to save you from looking in
> SSMS. But isn't this "Advanced Editor" simply the Package Designer you
> get when you create an Integration Services project?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
services to create 'DTS' packages. Going to a .net users group meeting, I was
told to use the 'Advanced editor' since it would help me alot. However, I
can not find
the 'advanced editor' in sql server 2005. Thus can you tell me where the
'advanced editor'is and what are some good uses of the advanced editor?
Thanks!
Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> I am used to DTS packages in sql server 2000 and now I want to use
> Integrated services to create 'DTS' packages. Going to a .net users
> group meeting, I was told to use the 'Advanced editor' since it would
> help me alot. However, I can not find the 'advanced editor' in sql
> server 2005. Thus can you tell me where the 'advanced editor'is and what
> are some good uses of the advanced editor?
Are you looking in SQL Server Mangement Studio? You find precious little
support for Integration Services there. You should have a look at
Business Intelligence Development Studio (BIDS), which really is Visual
Studio under a custom name.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I did find it was in Business Intelligence part of Visual studio.net. But
once you are in BIS, where do you find the advanced editor?
Thanks!
Diane Stein
"Erland Sommarskog" wrote:
> Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> Are you looking in SQL Server Mangement Studio? You find precious little
> support for Integration Services there. You should have a look at
> Business Intelligence Development Studio (BIDS), which really is Visual
> Studio under a custom name.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> I did find it was in Business Intelligence part of Visual studio.net. But
> once you are in BIS, where do you find the advanced editor?
Now you asking me questions that goes over my head. :-) I don't use SSIS
much, or rather not at all. I just wanted to save you from looking in
SSMS. But isn't this "Advanced Editor" simply the Package Designer you
get when you create an Integration Services project?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The "Advanced Editor' most likely refers to a property dialog for SSIS
components that allows you to set any property available on that component
and configure a lot of settings beyond what's typically available on the
regular Properties dialog for the same component.
In general, to use new SSIS designer, you start BIDS application, and create
a new SSIS project (click New -> Project, and pick SSIS project from the
list). This will create a new SSIS package within a project and open it up.
From there you will see the package's design surface, where you can drop
control flow components from a toolbox window, connect them into a flow and
configure. If you right click on any component, you will see an 'Advanced
Editor' option. If you have more questions, there is a separate SSIS forum
where you will find more experts ready to answer those.
Regards,
Maciek Sarnowicz
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99E4C3B06E16BYazorman@.127.0.0.1...
> Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> Now you asking me questions that goes over my head. :-) I don't use SSIS
> much, or rather not at all. I just wanted to save you from looking in
> SSMS. But isn't this "Advanced Editor" simply the Package Designer you
> get when you create an Integration Services project?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Friday, February 24, 2012
ADO, ADO.NET v. DTS
What is it about a DTS that allows you to move a table from one server (not
just a database) to another much faster than say, doing it all with ADO or
ADO.NET with forward-read-only cursor/datareader? Don't they use the same
drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
one record at a time, doesn't DTS essentially have to do the same thing? It
has to copy all the data, move it, and insert one row after another into a
table ... assume the database is in simple recovery mode, that there are no
triggers, and only one constraint (Primary Key).
Thanks in advance!
Mark
Hi
Run a DTS package and at the same time run a SQL Server Profiler session
(source and destination servers). You will see exactly how it does it.
Regards
Mike
"Mark" wrote:
> What is it about a DTS that allows you to move a table from one server (not
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the same
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing? It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
>
|||To add to Mike's response, bulk insert techniques are the fastest method to
load large amounts of data into SQL Server. These include the BULK INSERT
Transact-SQL statement, ODBC BCP and OLE DB IRowsetFastLoad. DTS uses
IRowsetFastLoad or BULK INSERT, depending on the task.
Higher level APIs like ADO and ADO.NET don't currently expose this
functionality but there may be enhancements with SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <field027@.idonotlikejunkmail.umn.edu> wrote in message
news:O2bHH8fCFHA.3940@.TK2MSFTNGP09.phx.gbl...
> What is it about a DTS that allows you to move a table from one server
> (not
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the
> same
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing?
> It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are
> no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
just a database) to another much faster than say, doing it all with ADO or
ADO.NET with forward-read-only cursor/datareader? Don't they use the same
drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
one record at a time, doesn't DTS essentially have to do the same thing? It
has to copy all the data, move it, and insert one row after another into a
table ... assume the database is in simple recovery mode, that there are no
triggers, and only one constraint (Primary Key).
Thanks in advance!
Mark
Hi
Run a DTS package and at the same time run a SQL Server Profiler session
(source and destination servers). You will see exactly how it does it.
Regards
Mike
"Mark" wrote:
> What is it about a DTS that allows you to move a table from one server (not
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the same
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing? It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
>
|||To add to Mike's response, bulk insert techniques are the fastest method to
load large amounts of data into SQL Server. These include the BULK INSERT
Transact-SQL statement, ODBC BCP and OLE DB IRowsetFastLoad. DTS uses
IRowsetFastLoad or BULK INSERT, depending on the task.
Higher level APIs like ADO and ADO.NET don't currently expose this
functionality but there may be enhancements with SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <field027@.idonotlikejunkmail.umn.edu> wrote in message
news:O2bHH8fCFHA.3940@.TK2MSFTNGP09.phx.gbl...
> What is it about a DTS that allows you to move a table from one server
> (not
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the
> same
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing?
> It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are
> no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
ADO, ADO.NET v. DTS
What is it about a DTS that allows you to move a table from one server (not
just a database) to another much faster than say, doing it all with ADO or
ADO.NET with forward-read-only cursor/datareader? Don't they use the same
drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
one record at a time, doesn't DTS essentially have to do the same thing? It
has to copy all the data, move it, and insert one row after another into a
table ... assume the database is in simple recovery mode, that there are no
triggers, and only one constraint (Primary Key).
Thanks in advance!
MarkHi
Run a DTS package and at the same time run a SQL Server Profiler session
(source and destination servers). You will see exactly how it does it.
Regards
Mike
"Mark" wrote:
> What is it about a DTS that allows you to move a table from one server (not
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the same
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing? It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
>|||To add to Mike's response, bulk insert techniques are the fastest method to
load large amounts of data into SQL Server. These include the BULK INSERT
Transact-SQL statement, ODBC BCP and OLE DB IRowsetFastLoad. DTS uses
IRowsetFastLoad or BULK INSERT, depending on the task.
Higher level APIs like ADO and ADO.NET don't currently expose this
functionality but there may be enhancements with SQL 2005.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <field027@.idonotlikejunkmail.umn.edu> wrote in message
news:O2bHH8fCFHA.3940@.TK2MSFTNGP09.phx.gbl...
> What is it about a DTS that allows you to move a table from one server
> (not
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the
> same
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing?
> It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are
> no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
just a database) to another much faster than say, doing it all with ADO or
ADO.NET with forward-read-only cursor/datareader? Don't they use the same
drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
one record at a time, doesn't DTS essentially have to do the same thing? It
has to copy all the data, move it, and insert one row after another into a
table ... assume the database is in simple recovery mode, that there are no
triggers, and only one constraint (Primary Key).
Thanks in advance!
MarkHi
Run a DTS package and at the same time run a SQL Server Profiler session
(source and destination servers). You will see exactly how it does it.
Regards
Mike
"Mark" wrote:
> What is it about a DTS that allows you to move a table from one server (not
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the same
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing? It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
>|||To add to Mike's response, bulk insert techniques are the fastest method to
load large amounts of data into SQL Server. These include the BULK INSERT
Transact-SQL statement, ODBC BCP and OLE DB IRowsetFastLoad. DTS uses
IRowsetFastLoad or BULK INSERT, depending on the task.
Higher level APIs like ADO and ADO.NET don't currently expose this
functionality but there may be enhancements with SQL 2005.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <field027@.idonotlikejunkmail.umn.edu> wrote in message
news:O2bHH8fCFHA.3940@.TK2MSFTNGP09.phx.gbl...
> What is it about a DTS that allows you to move a table from one server
> (not
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the
> same
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing?
> It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are
> no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
ADO, ADO.NET v. DTS
What is it about a DTS that allows you to move a table from one server (not
just a database) to another much faster than say, doing it all with ADO or
ADO.NET with forward-read-only cursor/datareader? Don't they use the same
drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
one record at a time, doesn't DTS essentially have to do the same thing? It
has to copy all the data, move it, and insert one row after another into a
table ... assume the database is in simple recovery mode, that there are no
triggers, and only one constraint (Primary Key).
Thanks in advance!
MarkHi
Run a DTS package and at the same time run a SQL Server Profiler session
(source and destination servers). You will see exactly how it does it.
Regards
Mike
"Mark" wrote:
> What is it about a DTS that allows you to move a table from one server (no
t
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the sam
e
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing?
It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are
no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
>|||To add to Mike's response, bulk insert techniques are the fastest method to
load large amounts of data into SQL Server. These include the BULK INSERT
Transact-SQL statement, ODBC BCP and OLE DB IRowsetFastLoad. DTS uses
IRowsetFastLoad or BULK INSERT, depending on the task.
Higher level APIs like ADO and ADO.NET don't currently expose this
functionality but there may be enhancements with SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <field027@.idonotlikejunkmail.umn.edu> wrote in message
news:O2bHH8fCFHA.3940@.TK2MSFTNGP09.phx.gbl...
> What is it about a DTS that allows you to move a table from one server
> (not
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the
> same
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing?
> It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are
> no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
just a database) to another much faster than say, doing it all with ADO or
ADO.NET with forward-read-only cursor/datareader? Don't they use the same
drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
one record at a time, doesn't DTS essentially have to do the same thing? It
has to copy all the data, move it, and insert one row after another into a
table ... assume the database is in simple recovery mode, that there are no
triggers, and only one constraint (Primary Key).
Thanks in advance!
MarkHi
Run a DTS package and at the same time run a SQL Server Profiler session
(source and destination servers). You will see exactly how it does it.
Regards
Mike
"Mark" wrote:
> What is it about a DTS that allows you to move a table from one server (no
t
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the sam
e
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing?
It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are
no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
>|||To add to Mike's response, bulk insert techniques are the fastest method to
load large amounts of data into SQL Server. These include the BULK INSERT
Transact-SQL statement, ODBC BCP and OLE DB IRowsetFastLoad. DTS uses
IRowsetFastLoad or BULK INSERT, depending on the task.
Higher level APIs like ADO and ADO.NET don't currently expose this
functionality but there may be enhancements with SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <field027@.idonotlikejunkmail.umn.edu> wrote in message
news:O2bHH8fCFHA.3940@.TK2MSFTNGP09.phx.gbl...
> What is it about a DTS that allows you to move a table from one server
> (not
> just a database) to another much faster than say, doing it all with ADO or
> ADO.NET with forward-read-only cursor/datareader? Don't they use the
> same
> drivers behind the scenes? While I know that ADO/ADO.NET are dealing with
> one record at a time, doesn't DTS essentially have to do the same thing?
> It
> has to copy all the data, move it, and insert one row after another into a
> table ... assume the database is in simple recovery mode, that there are
> no
> triggers, and only one constraint (Primary Key).
> Thanks in advance!
> Mark
>
Subscribe to:
Posts (Atom)