April 16, 2008 at 4:14 pm
Im building a SSIS package that exports data from table A and Table B to table C....all tables are located on a SQL 2000 database and server A.
Im building my SSIS package on a SQL 2005 server.
When I try to define my connection manager between Table A and Table C (my SQL 2000 database on Server A), I continue to receive the message:
"The selected connection manager uses an earlier version of SQL Server Provider. Bulk insert operations require a connection that uses a SQL Server 2005 provider."
What can I do to make this work ?...I dont want to build a DTS package, but rather a SSIS package that looks at a SQL 2000 database.
Thank you
April 16, 2008 at 4:23 pm
Looks like I solved it by using a OLEDB destination connection instead of a SQL Server destination, even though both point to the same location.
I'd still be interested, if anyone knows, why one works over the other ?
Thanks
April 17, 2008 at 4:51 am
Whenever I see that problem, it's because people are trying to use SSIS to pull data into a 2000 destination from a 2005 source. Unfortunately, SQL 2000 doesn't recognize SSIS providers, so you can't use it that way. You have to export the 2k5 stuff to files to import into 2000 using some other method.
If, however, you're using 2k5 as the destination, then you shouldn't have that problem.
Lastly, the SQL Server Destination connector only works on your local machine. You have to use OLE DB to connect to any remote SQL Server (a database not on your local box).
Does that help?
April 17, 2008 at 8:50 am
Your answer is consistent with what I discovered in my environment.
Thank you
April 17, 2008 at 9:08 am
You're welcome. Always glad to help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply