January 12, 2007 at 2:16 pm
Hi all,
Longtime IT guy but new to Sql Server(background DB2, then Oracle). I'm working for a small company that has purchased another company that has it's own system/db. The purchased company DB is sql server7.0(I'm assuming that sql server 2005). Our existing system is sql server 2000. They want to alot of data from thier existing system(solomon 5.5) into this new DB to reduce time with data entry. The physical tables are relatively different but the data is similar. I've read a bit on DTS but I'm not sure if DTS is too big of a hammer to use for this? I supposed I could just set up connection then write T-SQl to load the data. Should DTS really be reserved for Data warehouse apps where loads are done nightly to a reporting db or is it simple enough and a right fit for an initial load of a different transactional DB? Any input is appreciated.
January 12, 2007 at 2:49 pm
Hi Paul. First of all, SQL Server 7.0 is not SQL Server 2005. It is the version prior to SQL Server 2000. DTS is not just for warehousing data and it can be used for even simple one-time tasks using the DTS Wizard. I would say that your answer will depend on your comfort level with DTS and T-SQL. One of the advantages of DTS is that it is graphical and those who whom may be less than average skilled with T-SQL may benefit from this. If you are already savvy with T-SQL, it may be better to write a T-SQL script and save the time on learing DTS. Either way will work.
January 12, 2007 at 4:17 pm
Thanks John,
I might just go the DTS route as I have some time luxury and it couldn't hurt on a resume. A couple quick follow-ups, I was planning on inserting rows into the target tables by running the DTS tasks in the target database and connecting over to the source. In Oracle the DBA's would just build me a DB link to wherever I needed to get the data from, looks like the DTS documentation on MSDN says I can create or use an existing connection so I assume this is the same idea as a dblink? Will DTS work fine to do this kind of insert processing where I'm not dropping or truncing tables(as is usually done in Data Warehouse ETL processes) and I'm not going to be using staging tables either. And, in this situation, is the DTS procedure usually run in the target and connects to the source(s)?
Thanks again.
January 13, 2007 at 5:34 pm
Besides the DTS articles here, you might try http://www.sqldts.com as well.
January 15, 2007 at 10:02 am
Paul,
Creating a linked server would be the SQL Server equivalent to the Oracle DB link. You can get more info on this from BOL under 'lined servers'. Depending on your requirements, you’ve got a couple of options here:
1. Create a linked server and use that to reference the remote data in your Execute SQL tasks, stored procedures, etc.
2. Create a Connection object for the remote server and use the Transform Data task (data pump) to move and transform data.
Yes, DTS will work fine for INSERT processing. My personal preference would be to run the package on the Target server, but I am not a DTS guru. If you plan on going the DTS route, I would do as Steve suggested and read the SSC and SQLDTS articles on DTS that pertain to your task at hand. I imagine that there is a DTS best practices article out there somewhere that will be of use to you.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply