RE: DTS 2000 package error when connecting to SQL Server 2005
- From: EroPro <EroPro@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 17 Jan 2007 12:28:00 -0800
Todd,
This worked, thank you very much for your help. I would need to do the same
for the other 100 packages we have :)
"Todd C" wrote:
I doubt that will work..
For the purpose of getting to SQL Server 'Objects', DTS uses an object model
that cannot access SQL 2005 objects. SQL Server 2005, on the other hand uses
the new DMO object model that is backward compatible with 2000 server objects.
SSIS is not really that big of a beast. Yes, there is a lot to learn but try
this:
Open the BIDS (Business Intelligence Design Studio) on the SQL 2005 machine.
Start a new Project, and make it an Integration Services Project.
In the Solution Explorer on the right, right click on the SSIS Packages node
and select "New Import and Export Wizard"
Follow along in the wizard, assigning the Source (SQL 2000), Destination
(SQL 2005), and then select the objects to transfer.
Let me know how it goes.
todddotchittendenatmorantugdotcom
--
Todd Chittenden
"EroPro" wrote:
Hi Todd and thanks for your response.
I am not too familiar with SSIS and unfortunately the timing is not letting
me to learn it just yet. Would this work if I save the DTS package on the sql
server 2005 machine and run it from there?
"Todd C" wrote:
DTS and SQL 2000 use a different object model than SQL 2005. If you are
trying to transfer database objects from a 2005 db to a 2000 db, then you
won't be able to use DTS (pulling). Use SSIS on the machine that is already
running 2005 and 'push' the objects over to the 2000 db.
HTH
--
Todd Chittenden
"EroPro" wrote:
I have a DTS package located on a SQL Server 2000 database that copies SQL
Server Objects task. In the properties window of the "Copy SQL Server Objects
Task" when I specify the source server, which is a SQL Server 2005 database,
I get the following error:
Microsoft [SQL-DMO]
You must use SQL Server 2005 management tools to connect to this server.
Any help would be appreciated.
Thanks.
- Prev by Date: Re: xml
- Next by Date: Re: Users permission to run DTS without being sysadmin
- Previous by thread: Re: CDONTS causing an error?!
- Next by thread: scheduling a t-sql stored procedures name in table column without creating a job for each one??
- Index(es):
Relevant Pages
|
