January 10, 2008 at 2:35 am
How to create a DTS package in SQL Server 2005 ?
January 10, 2008 at 11:27 am
In SQL Server 2005, DTS is changed to SSIS which you would find under Business Intelligence development studio.
January 10, 2008 at 9:52 pm
Can you help me with the steps to follow to create a DTS in sql server 2005 ?
January 11, 2008 at 8:52 am
I think you better get a book about SSIS. It is totally different from DTS. Some of the tasks are no longer used.
January 15, 2008 at 11:22 am
Yeah you probably will need a book (or two). SSIS is *completely* different from DTS.
Technet has a good introduction
http://www.microsoft.com/technet/prodtechnol/sql/2005/intro2is.mspx
And there's stuff in BOL of course too.
http://msdn2.microsoft.com/en-us/library/ms141767.aspx
And Samples
http://msdn2.microsoft.com/en-us/library/ms160740.aspx
One way to start with figuring out basic packages is to use the data import wizard to transfer some data between databases and save the package to the file system.
Reviewing those packages that did something basic you already understand can be a great place to start.
HTH and Good luck!
Skål - jh
January 16, 2008 at 2:51 am
As people have said, SSIS is the favoured tool for developing DTS-like functionality on SQL 2005 and onwards.
However, you can still use all the SQL 2000 functionality in SQL 2005, but there are some extra components you have to install.
1) Download and install the 'DTS Designer Components' from Microsoft.
2) Apply the fix described in KB917406. Some people do not need this, but most do.
The DTS functionality you have installed in SQL 2005 allows you to edit existing packages, but there is no 'New Package' option. The workaround for this is:
a) Create a template package on a SQL 2000 server.
b) Export the package to a structured storage file.
c) Import the package into SQL 2005. Make sure you use the DTS Designer import functionality, not the SSIS import DTS functionality!
d) Open the package in DTS Designer in SQL 2005
e) Do a Save As to create your desired new package name.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 17, 2008 at 6:37 am
I would add to Ed's reply that there is one step prior.
The first thing is to determine if there is a good reason to create a new DTS package instead of using SSIS. I would be concerned about putting new funcitonality into a depricated feature of SQL Server.
January 17, 2008 at 8:26 am
I have created SSIS packages which run on a SQL 2k5 server but connect to my SQL 2000 server and do work on it without any problem.
So if that's the reason you're trying to create a DTS package instead of SSIS package, don't worry about it. So long as you don't use T-SQL or objects that aren't available in SQL 2000 (like PIVOT or Assemblies), you shouldn't have any problems with designing a "good ole fashioned DTS pkg" using SSIS.
Just a note, though. Some ActiveX controls and .ini files / Dynamic Properties no longer work effectively in SSIS. So you need to get some training or at least a book on how to do SSIS before you try and build your package.
February 14, 2008 at 3:15 pm
There might be a very good reason to create DST packages in SSIS 2005.
Imagine the following scenario:
A company has 500 DTS packages working in MSSQL2000, interconnected between each other, calling each other, named appropriately etc.
The whole bunch has been just recently migrated as-is into legacy section of 2005.
It works as expected. The development team is in a rigorous process of re-writing the whole spiderweb of DTS into proper SSIS jobs, but the clients are demanding an urgent change to the existing DTS jobs, which run under 2005.
It would be an ugly and dangerous solution to try to quickly rewrite the part of the DTS army, which would be affected by the change, but it would rather be more wise to introduce a DTS change (create a new task, modify old tasks, etc) to satisfy the urgent call.
The core word here is "urgent".
I bet the gent who asked the original question had a similar case.
Cheers,
Valentin
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply