Data Import from SQL 2000 to SQL 2005

Question

Data Import from SQL 2000 to SQL 2005

SkyBVI

SSCoach

Points: 15128
More actions
April 14, 2011 at 9:00 am

#235666

Hi,
I will be needing some data from sql 2000 version to export to sql 2005.
Last time , I did manually, like went into sql 2005 open the db, tasks, import data from sql 2000...
It worked fine.
Now the demand of the project is that I should be able to do it automatically not manually...
How can I do thru SSIS or any other possible method?
I dont know much of SSIS..
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

SSIS can move data around as much as you want, but if you are new to the product you are going to have to spend some time getting up to speed and then come back with more-specific questions.

Referring to 'some data' is also a bit nebulous. One table or hundreds of tables? Tens of rows or trillions? All data, or a selection? Just INSERT or INSERT-and-UPDATE required?

Also, as this is SQL Server to SQL Server, have you considered using just T-SQL?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SkyBVI SSCoach Points: 15128 More actions · Answer 2

Phil Parkin (4/14/2011)
SSIS can move data around as much as you want, but if you are new to the product you are going to have to spend some time getting up to speed and then come back with more-specific questions.
Referring to 'some data' is also a bit nebulous. One table or hundreds of tables? Tens of rows or trillions? All data, or a selection? Just INSERT or INSERT-and-UPDATE required?
Also, as this is SQL Server to SQL Server, have you considered using just T-SQL?

Thanks for replying..

My whole purpose of doing is that I want data from 2 tables in sql 2000, there is a join arnd 50k rows of data are imported back to sql 2005..

But even after that I have more work to do as I want to save it as a package and then do a lot of t sql stuff in sql 2005 for getting the output..

So, I want to put all these, in a ssis package whcih i can run from sql server agent jobs...

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 3

I think I am getting near to the answer of my own question...:-P

Regards
Sushant Kumar
MCTS,MCP

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

SKYBVI (4/14/2011)
I think I am getting near to the answer of my own question...:-P

So far, you haven't mentioned anything that can't be done in a stored proc ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SkyBVI SSCoach Points: 15128 More actions · Answer 5

Phil Parkin (4/14/2011)
SKYBVI (4/14/2011)
I think I am getting near to the answer of my own question...:-P
So far, you haven't mentioned anything that can't be done in a stored proc ...

How can i import data from sql 2000 within a stored proc?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

Have you tried connecting to the 2000 instance from within 2005 Management Studio? Once that's in place (use a linked server), you can just use standard queries to move data around.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SkyBVI SSCoach Points: 15128 More actions · Answer 7

Phil Parkin (4/15/2011)
Have you tried connecting to the 2000 instance from within 2005 Management Studio? Once that's in place (use a linked server), you can just use standard queries to move data around.

Linked server is not working...:(

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

What provider did you choose and what error message did you get? Please copy/paste the exact text.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin