|
|
You're in the
sql server dts
group:Stay with DTS or move to SSIS?
sql server dts:
and it's worked fine, and still does in SQL 2005 using the backward compatibility components. We've got existing code that uses the DTS API, and I'm not sure we need to move to SSIS if DTS is working for us, considering the cost of porting, retesting, etc. What are the arguments for moving from DTS to SSIS? Will DTS support be dropped by Microsoft? Other than performance increases which I assume are in SSIS, are there any technical reasons why moving to SSIS is the right choice?...other than the cool factor? Thanks. Mike
Rick,
Thanks for your input. Mike [quoted text, click to view] "Rick Brandt" <rickbrandt2@hotmail.com> wrote in message news:W6IJg.22895$gY6.11207@newssvr11.news.prodigy.com... > Michael Cessna wrote: >> We've been using DTS for several years as part of a commercial >> application, and it's worked fine, and still does in SQL 2005 using >> the backward compatibility components. We've got existing code that >> uses the DTS API, and I'm not sure we need to move to SSIS if DTS is >> working for us, considering the cost of porting, retesting, etc. What >> are the arguments for moving from DTS to SSIS? Will DTS support be >> dropped by Microsoft? Other than performance increases which I assume >> are in SSIS, are there any technical reasons why moving to SSIS is >> the right choice?...other than the cool factor? > > I can only say that our experience (pulling data from an IBM ISeries) is > that SSIS is LOTS slower and LOTS more problematic (mostly with data type > conversions). If you need something with a ton of complexity then SSIS > might be better suited, but for simple bulk data transfer DTS is way > better. > > My best example. I have a DTS package that pulls 40 tables a night from > our ISeries to our SQL Server box. The entire DTS package takes around 40 > minutes. An SSIS pacakage to pull over the single largest table (50ish > fields and a little over 2 million rows) takes well over an hour. That > same single table with DTS takes 12 minutes. > > We have also run into data types on several tables that we simply could > not make work with SSIS. The middle layer uses dot-net types and some of > them just won't go (even with transform steps added). > > My opinion is that most of the appeal of SSIS is that it uses Visual > Studio for the IDE and many people are already familiar with that > environment. > > -- > Rick Brandt, Microsoft Access MVP > Email (as appropriate) to... > RBrandt at Hunter dot com > > >
[quoted text, click to view]
Michael Cessna wrote: > We've been using DTS for several years as part of a commercial > application, and it's worked fine, and still does in SQL 2005 using > the backward compatibility components. We've got existing code that > uses the DTS API, and I'm not sure we need to move to SSIS if DTS is > working for us, considering the cost of porting, retesting, etc. What > are the arguments for moving from DTS to SSIS? Will DTS support be > dropped by Microsoft? Other than performance increases which I assume > are in SSIS, are there any technical reasons why moving to SSIS is > the right choice?...other than the cool factor? I can only say that our experience (pulling data from an IBM ISeries) is that SSIS is LOTS slower and LOTS more problematic (mostly with data type conversions). If you need something with a ton of complexity then SSIS might be better suited, but for simple bulk data transfer DTS is way better. My best example. I have a DTS package that pulls 40 tables a night from our ISeries to our SQL Server box. The entire DTS package takes around 40 minutes. An SSIS pacakage to pull over the single largest table (50ish fields and a little over 2 million rows) takes well over an hour. That same single table with DTS takes 12 minutes. We have also run into data types on several tables that we simply could not make work with SSIS. The middle layer uses dot-net types and some of them just won't go (even with transform steps added). My opinion is that most of the appeal of SSIS is that it uses Visual Studio for the IDE and many people are already familiar with that environment. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
Hello Michael,
I agree with Rick SSIS could do more complexsity jobs than DTS and sometimes it may be slower than DTS due to data type transfer etc. If DTS can meet your requirement at present you may consider migrate all stuff later. However, DTS support is for compatbility purpose and may not be supported in next version of SQL Server. Managing and Deploying SQL Server Integration Services http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx Upgrading DTS packages to SQL Server Integration Services http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration/ 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.
Thanks Peter!
Mike [quoted text, click to view] ""privatenews"" <petery@online.microsoft.com> wrote in message news:R7l2f5WzGHA.400@TK2MSFTNGXA01.phx.gbl... > Hello Michael, > > I agree with Rick SSIS could do more complexsity jobs than DTS and > sometimes it may be slower than DTS due to data type transfer etc. If DTS > can meet your requirement at present you may consider migrate all stuff > later. However, DTS support is for compatbility purpose and may not be > supported in next version of SQL Server. > > Managing and Deploying SQL Server Integration Services > http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx > > Upgrading DTS packages to SQL Server Integration Services > http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration/ > > 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. >
I prefer SSIS over DTS, even for very simple tasks. SSIS is far more
powerful. However, the learning curve may be slightly steeper than for DTS. The main thing is to learn it properly. Unfortunately many of us just start hacking without taking the time to understand the product properly. Without understanding it properly you will get all kinds of problems and you will think it's the product's fault. Do not compare the speed in the debugging environment with SSIS's true speed. To get an idea of its true speed, do CTRL+F5 to execute packages in the SSIS Designer. It is typically mega percentages faster than DTS! Take a look at this post, for example: http://weblogs.sqlteam.com/mladenp/archive/2006/05/26/9992.aspx Charles Kangai, MCT, MCDBA Author of Learning Tree's 4-day course: "SQL Server 2005 Integration Services" http://www.learningtree.com/courses/134.htm Author of Learning Tree's 4-day course: "SQL Server Reporting Services" http://www.learningtree.com/courses/523.htm [quoted text, click to view] "Michael Cessna" wrote:
> We've been using DTS for several years as part of a commercial application, > and it's worked fine, and still does in SQL 2005 using the backward > compatibility components. We've got existing code that uses the DTS API, and > I'm not sure we need to move to SSIS if DTS is working for us, considering > the cost of porting, retesting, etc. What are the arguments for moving from > DTS to SSIS? Will DTS support be dropped by Microsoft? Other than > performance increases which I assume are in SSIS, are there any technical > reasons why moving to SSIS is the right choice?...other than the cool > factor? > > Thanks. > > Mike > >
Hi Rick,
I believe FastParse is only available on text file sources. There is a whitepaper on performance tuning, and there are lots of things to consider. The paper is partly written by Donald Farmer, one of the Microsoft SSIS product managers. I thought I knew SSIS until I listened to Donald's talk. The URLs are below: Webcast: http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032298086&EventCategory=5&culture=en-US&CountryCode=US White paper: http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx Cheers, Charles Kangai, MCT, MCDBA Author of Learning Tree's 4-day course: "SQL Server 2005 Integration Services" http://www.learningtree.com/courses/134.htm Author of Learning Tree's 4-day course: "SQL Server Reporting Services" http://www.learningtree.com/courses/523.htm [quoted text, click to view] "Rick Brandt" wrote:
> Charles Kangai wrote: > > I prefer SSIS over DTS, even for very simple tasks. SSIS is far more > > powerful. However, the learning curve may be slightly steeper than > > for DTS. The main thing is to learn it properly. Unfortunately many > > of us just start hacking without taking the time to understand the > > product properly. Without understanding it properly you will get all > > kinds of problems and you will think it's the product's fault. > > > > Do not compare the speed in the debugging environment with SSIS's true > > speed. To get an idea of its true speed, do CTRL+F5 to execute > > packages in the SSIS Designer. It is typically mega percentages > > faster than DTS! Take a look at this post, for example: > > http://weblogs.sqlteam.com/mladenp/archive/2006/05/26/9992.aspx > > Is that "fast parse" option only available on flat file extracts? The speed > differences I was describing are based on running SSIS and DTS packages from > scheduled Agent jobs, not from the debugging environment. > > -- > Rick Brandt, Microsoft Access MVP > Email (as appropriate) to... > RBrandt at Hunter dot com > >
It's possible that DTS will be faster for small volumes of data. However, for
much larger volumes, SSIS is supposed to be faster. Personally, I haven't done much speed testing. However, I think the point you make is mentioned in one of the links. Cheers, Charles Kangai, MCT, MCDBA Author of Learning Tree's 4-day course: "SQL Server 2005 Integration Services" http://www.learningtree.com/courses/134.htm Author of Learning Tree's 4-day course: "SQL Server Reporting Services" http://www.learningtree.com/courses/523.htm email alias: charles email domain: kangai.demon.co.uk [quoted text, click to view] "Rick Brandt" wrote:
> Charles Kangai wrote: > > Hi Rick, > > > > I believe FastParse is only available on text file sources. > > > > There is a whitepaper on performance tuning, and there are lots of > > things to consider. The paper is partly written by Donald Farmer, one > > of the Microsoft SSIS product managers. I thought I knew SSIS until I > > listened to Donald's talk. The URLs are below: > > > > Webcast: > > http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032298086&EventCategory=5&culture=en-US&CountryCode=US > > > > White paper: > > http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx > > Thanks for the links. > > What I found odd with the testing on my "problem table" is that even when I did > nothing but read the rows into a row counter (no transformation and no > destination) it still took a lot longer than the DTS package. This was tested > both with the same ODBC driver that the DTS packages uses and with an OLEDB > driver. It appears that the actual pulling of data from the source is what is > slower. > > -- > Rick Brandt, Microsoft Access MVP > Email (as appropriate) to... > RBrandt at Hunter dot com > > >
[quoted text, click to view]
Charles Kangai wrote: > I prefer SSIS over DTS, even for very simple tasks. SSIS is far more > powerful. However, the learning curve may be slightly steeper than > for DTS. The main thing is to learn it properly. Unfortunately many > of us just start hacking without taking the time to understand the > product properly. Without understanding it properly you will get all > kinds of problems and you will think it's the product's fault. > > Do not compare the speed in the debugging environment with SSIS's true > speed. To get an idea of its true speed, do CTRL+F5 to execute > packages in the SSIS Designer. It is typically mega percentages > faster than DTS! Take a look at this post, for example: > http://weblogs.sqlteam.com/mladenp/archive/2006/05/26/9992.aspx Is that "fast parse" option only available on flat file extracts? The speed differences I was describing are based on running SSIS and DTS packages from scheduled Agent jobs, not from the debugging environment. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
[quoted text, click to view]
Charles Kangai wrote: > Hi Rick, > > I believe FastParse is only available on text file sources. > > There is a whitepaper on performance tuning, and there are lots of > things to consider. The paper is partly written by Donald Farmer, one > of the Microsoft SSIS product managers. I thought I knew SSIS until I > listened to Donald's talk. The URLs are below: > > Webcast: > http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032298086&EventCategory=5&culture=en-US&CountryCode=US > > White paper: > http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx Thanks for the links. What I found odd with the testing on my "problem table" is that even when I did nothing but read the rows into a row counter (no transformation and no destination) it still took a lot longer than the DTS package. This was tested both with the same ODBC driver that the DTS packages uses and with an OLEDB driver. It appears that the actual pulling of data from the source is what is slower. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
[quoted text, click to view]
Charles Kangai wrote: > It's possible that DTS will be faster for small volumes of data. > However, for much larger volumes, SSIS is supposed to be faster. > Personally, I haven't done much speed testing. However, I think the > point you make is mentioned in one of the links. I actually found the opposite. The smaller to medium tables were closer. DTS was always faster, but on some tables we're talking about 5 seconds versus 7 seconds. It seemed to me that the size of the *row* was the the biggest factor. SSIS doesn't seem to like wide tables at all. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |