I thought picking the correct Provider would have been the easiest task! I
need to connect to both SQL Server 2000 and 2005. I can't seem to make the
correct selection among .NET, .NET for OLEDB or Native OLEDB. I thought that
the correct choices would have been ADO.NET or Native SQL but now with all
the choices available want to know which one is really the correct choice.

Also, when configuring the SQL Server Destination object, I have found that
I am limited to OLE DB connection managers only. Why is that? Should I (for
the same reason) use an OLE DB provider for my Source?

--
Michael Hockstein

Hello Michael,

I understand that you'd like to know if you shall choose among .NET, .NET
for OLEDB or Native OLEDB to connect to SQL 2000/2005 in SSIS. Also, you
found you can only choose OLE DB connection managers when configuring the
SQL Server Destination object. If I'm off-base, please let me know.

From Books Online:

=================================
An ADO.NET connection manager enables a package to access data sources by
using a .NET provider. This connection manager is typically used to access
data sources such as Microsoft SQL Server 2005, and also data sources
exposed through OLE DB and XML in custom tasks that are written in managed
code by using a language such C#.

When you add an ADO.NET connection manager to a package, SQL Server 2005
Integration Services (SSIS) creates a connection manager that is resolved
as an ADO.NET connection at run time, sets the connection manager
properties, and adds the connection manager to the Connections collection
on the package.

Several SQL Server 2005 Integration Services (SSIS) tasks and data flow
components use an OLE DB connection manager. For example, the OLE DB source
and OLE DB destination use this connection manager to extract and load
data, and the Execute SQL task can use this connection manager to connect
to a SQL Server database to run queries.

The OLE DB connection manager is also used to access OLE DB data sources in
custom tasks written in unmanaged code that uses a language such as C++.
=================================


Net provider for OLEDB wraps OLEDB provider with managed provider code.
Since SQL native OLEDB is not wrapped by ADO/ADO.net and it shall has
better performance and support new feature of SQL 2005. If you need to
connect to both SQL 2005/2000, I think you shall consider SQL native OLEDB
Please see the following article for details:

http://blogs.msdn.com/dataaccess/arc...26/412161.aspx

http://msdn.microsoft.com/data/ref/sqlnative/

I have extracted the following information:

================================

But when would you actually want to use SQL Native Client as opposed to
MDAC, or even ADO.NET? The answer is – only if you are upgrading existing
or developing new COM-based (or native) applications that will target the
new features of SQL Server 2005. If you don’t need any of the new features
of SQL Server 2005, then you don’t need to use SQL Native Client, your
existing OLE DB and ODBC code will work just fine. Of course, if you have
or are planning on moving to a managed code base for data access, then the
ADO.NET data access classes of the .NET Framework is what you should use.
=================================

As for SQL Server Destination object, OLE DB connection manager is forced
and this is limitation of SSIS. Please rest assured that your feedback on
this is routed to the proper channel. In the meantime, I also encourage you
submit via the link below

http://lab.msdn.microsoft.com/produc...k/default.aspx


Also, you may want to see the following link for comparsion of some
destination adapter comparsion:

SSIS: Destination Adapter Comparison
http://blogs.conchango.com/jamiethom...8/14/4344.aspx

If you have further questions or concerns on this, please feel free to
let's know. Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks for all the information. It seems that you really need to select the
correct provider AND component. For example, I can't use the SQL Destination
if I'm using SQL 2000, rather I need to use OLE DB Destination. However, if I
am using SQL Server 2005, I can use the SQL Destination a a number of
different providers.

I can now get the connections and respective sources/destinations to work
correctly. Understand why it has to be this way? Not by a long shot.
--
Michael Hockstein


""privatenews"" wrote:

Hello Michael,

You are welcome! I agree better documents shall be published regarding
choosing the right connection manager/providers and please rest assured
your feedback on this is routed to the proper channel. In the meantime, I
also encourage you submit via the link below

http://lab.msdn.microsoft.com/produc...k/default.aspx

Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


================================================== ===

This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====