June 27, 2008 at 12:33 pm
Setting 2 sql servers: one runs sql 2005, the other sql 2000
Data is sent daily to the sql 2005 server where it is processed using a custom SSIS package. The process works well. A new business "wrinkle" arose where we now need the same DB to be updated daily on the sql 2000 server.Since our SSIS packages uses a single sql conneciton (originally pointing to a sql 2005 DB) I thought it would be simple to re-point that connection to the same-named DB on the sql 2000 server. I ran the tweaked SSIS package and found it failed immediately.
On the sql 2005 box, in SSIS, on the SQL Destination Editor box (which now points to the sql 2000 box) I see the following error message:
"The selected connection manager uses an earlier version of a Sql server provider. Bulk insert
operations require a connection that uses a Sql 2005 provider".
The connection manager uses "Native OLEDB\SQL Native Client" provider. I tried changing the providers on the Sql Destination Editor box but still get the same error.
What can I do about this?
TIA,
Barkingdog
June 27, 2008 at 1:06 pm
You are using a "SQL Server Destination" component. This is a bulk operator component that has two requirements. First, the server must be local to the server executing the SSIS package. Second, the server it is connecting to must be a SQL 2005 or later version.
The bulk operation it is using did not exist in SQL 2000. The local server only requirement is also one that many people seem to not know.
You need to use an OLEDB destination. With this, you can use the "Fast Load" options and that will use bulk-copy which was available in SQL 2000 as well.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply