How to copy data from sql server 2005 to sql server 2008?
Posted on 2010-09-02
I need to transfer a 2 tables' data on a 4 hour basis from sql server 2005 to sql server 2008 .
First of all ,I am unable to register the 2008 server in 2005 SSMS .It gives me an error that this version is not supported. I have sp3 on sql server 2005 . What should I change to be able to do that.
I tried SSIS package . I did 'export data ' from SSMS and saved it as a ssis package. It ran successfully . But when I scheduled it as a job,the job failed giving the following error : (I replaced the actual names in the following error message for privacy purposes at the start with servername\account. Everything else is as it it)
Executed as user: servername\account. ...LEDB". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2010-09-02 11:39:28.00 Code: 0xC0202009 Source: 090920101130 Connection manager "DestinationConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: A connection attempt . The step failed.
Please help . Did someone ran into same kind of problem? How do I run the ssis package successfully. Why and where it can't connect when the same export can run successfully when ran manually and standalone??
Thanks a lot for any help !