October 6, 2006 at 9:00 am
I'm trying to move the contents of a table on my source database which is SQL Server 2000 to an identical table on my SQL Server 2005 "datamart". I've tried using the OLE DB Source type of Data Flow Source, however I get a message saying that "The selected connection manager uses an earlier version of SQL Server Provider. Bulk inster operations require a connection that uses a SQL Server 2005 provider."
Should I be using a different Data Flow Source such as DataReader Source? Is there a way to change the connection manager to my SQL 2000 box so it "plays nice" with my SQL 2005 box?
Thanks
Ron
October 9, 2006 at 8:00 am
This was removed by the editor as SPAM
October 17, 2006 at 5:58 am
Hi There
You need to install ADO.Net 2 onto your SQL 2000 Box
October 18, 2006 at 5:57 am
Ron,
I have a silly question, so forgive me if it sounds strange, but have you tried to use the OLEDB for SQL Server instead? It should work for SQL 2000 to SQL 2k5.
If you've tried it and it didn't work, could you post the error?
November 12, 2007 at 11:58 am
I am having the same issue and I did try using the OLEDB for SQL Server but ended with the same error:
"The selected connection manager uses an earlier version of a SQL server provider. Bulk insert operations require a connection that uses a SQL Server 2005 provider".
Do you think I should try the ADO2.net on the SQL2K server??
November 12, 2007 at 12:14 pm
What tool are you using? The DTS Import / Export Wizard or SSIS? From which Server are you initiating the move, 2000 or 2005?
November 12, 2007 at 2:29 pm
Great questions: Here's the background:
Server 1 has SQL2005 and Visual Studio 2005 and does not contain any working data.
Server 2 has SQL2K and Enterprise Manager and contains the live data.
The goal is to create all the DTS packages I currently have on Server 2 (SQL2K) in Visual Studio 2005 on Server 1.
Therefore, in Visual Studio 2005, I have created an Integration Services Project. In this project I created a SQL OLE DB connection to the database located on Server 2 and the connection test is successful. Later, when creating a data flow task to write data to Server 2 (SQL2K), I get the error shown in the attachments.
I don't get the error if I write to a SQL2005 table so I think the problem is a compatibility issue with SQL2K.
Thank you for any insight in advance!
November 13, 2007 at 4:01 am
I'm a little confused. Why are you upgrading DTS to SSIS if you're still using SQL Server 2000 and not upgrading your database to 2005? If you're not upgrading your DBs, then don't upgrade your DTS to SSIS. It'll just muddy up the waters.
Your connection managers don't make it clear if you're moving data from files, non-SQL databases or SQL Server 2005. If you're moving stuff from 2005 to 2000, you're going to have a problem, because there is no compatibility in 2000 for 2005 stuff.
If you're only moving data, then I advise write the 2005 data to a text or CSV file, then create a DTS package to move the text / CSV to SQL 2000. This will avoid any datatype issues, etc.
On the other hand, if you're trying to upgrade all your DTS packages to send data to 2005 (which makes sense for upgrades), then don't make 2000 your Destination, make it your Source and send the data to 2005.
November 13, 2007 at 4:29 am
I would expect problems going FROM 2005 TO 2000 however I'm going FROM 2000 to 2005. I don't recall how I ended up going around this but I do know that I was not able to get this to work which is a real mystery why two tables with the same structure can't "talk" to one another between 2000 and 2005.
November 13, 2007 at 4:47 am
But in your previous post, you said you're writing "TO" SQL Server 2000. And that is what seems to be the problem to me. Check your destination source again. Verify you actually are writing to SQL Server 2005.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply