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

Hi,
When i save a DTS package within msdb i must provide a
valid login to save it. If i schedule the package with the
DTS schedule options (right click on the DTS object), it
will create a job with a dtsrun step with the login and
password, that i provided when it was saved, encrypted for
load the DTS. I'm i right '
Suppose i save the package with dtsadmin user, where can i
found the information about the user that saved the
package ' i need this info ' suppose i change the user's
password, my job will not run any more until i rebuild the
DTSrun encrytion with the new password.
Thanks
PhilNot exactly. The package is saved with an owner who is the
logged in user who created the package. You do not have to
provide any information to save a package.
If you schedule it through Enterprise Manager, it is
scheduled with the currently logged in users credentials.
The owner and the credentials used when scheduling the
package can be different. Additionally, if it's all done
through Windows authentication, you wouldn't have a user id
and password.
The user and password used in the encrypted string for
dtsrun is not stored anywhere.
In terms of changing passwords, that's part of the reason
that Windows authentication is preferred. The biggest reason
is that it's more secure but it also often eliminates
maintenance issues such as this.
-Sue
On Fri, 2 Apr 2004 09:37:34 -0800, "Phil"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>When i save a DTS package within msdb i must provide a
>valid login to save it. If i schedule the package with the
>DTS schedule options (right click on the DTS object), it
>will create a job with a dtsrun step with the login and
>password, that i provided when it was saved, encrypted for
>load the DTS. I'm i right '
>Suppose i save the package with dtsadmin user, where can i
>found the information about the user that saved the
>package ' i need this info ' suppose i change the user's
>password, my job will not run any more until i rebuild the
>DTSrun encrytion with the new password.
>Thanks
>Phil

DTA package handling multiple file formats

Hi,

Currently we get data from more then 200 different sources and all of
our vendors provide data in different file formats. The problem is we
have more then 100 DTS packages now and the maintainance is very
diffucult.
Every time our vendor changes the format we have to change in multiple
DTS packages.
Is anybody know what would be the right way of reducing the no. of DTS
packages.
The type of file formats we get are .xls .txt .dat .csv etc. and .txt
& .dat files comes with different delimitters. The # of columns also
varies from file to file. Is it possible to have a DTS package which
can handle diff file formats and loads data into a staging table and
from there based of the source of the file we can move data into
respective tables & columns.

We are using SQL SERVER 2000

Thanks in advance.

Subodh"Subodh" <sgoyal@.agline.on.ca> wrote in message
news:90104bf0.0501240846.58b2b293@.posting.google.c om...
> Hi,
> Currently we get data from more then 200 different sources and all of
> our vendors provide data in different file formats. The problem is we
> have more then 100 DTS packages now and the maintainance is very
> diffucult.
> Every time our vendor changes the format we have to change in multiple
> DTS packages.
> Is anybody know what would be the right way of reducing the no. of DTS
> packages.
> The type of file formats we get are .xls .txt .dat .csv etc. and .txt
> & .dat files comes with different delimitters. The # of columns also
> varies from file to file. Is it possible to have a DTS package which
> can handle diff file formats and loads data into a staging table and
> from there based of the source of the file we can move data into
> respective tables & columns.
> We are using SQL SERVER 2000
> Thanks in advance.
> Subodh

Personally, I would look at writing an external script or program in C#,
Python, Perl or whatever to manipulate the files and load the staging table.
The script could load the data directly to the staging table by dynamically
generating INSERTs, or it might transform the source files to your own
standard file format to be used with bcp.exe, BULK INSERT or the DTS Bulk
Insert task.

Your maintenance efforts would be then directed at the program, not at the
packages, which is probably a good thing - it's likely easier to modify one
module/class/object than 10 packages, and most languages have good library
support for parsing, tokenizing, regexes and so on. Or perhaps a hybrid
solution might work - an external program for proprietary file formats, and
standard DTS connections/tasks for the rest. You might also want to ask in
microsoft.public.sqlserver.dts to see if someone else has experienced a
similar situation.

Finally, since your basic issue (as I understood it) is that you have too
many file formats, you should consider agreeing a standard file format - at
least with your larger clients/vendors - rather than looking at it just as a
technical problem. I have no idea how easy that would be in your company's
situation, of course.

Simon|||
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!