Which connection should be used

Question

I am new to SSIS. Which connection is recomended and better to use with SQL 2005 database, Ole Db or ADO.NET connection?

 

Do correct me if I am wrong. I am thinking that ADO.NET will be better while using with SQL Server as database and if the database is different then SQL server then Ole DB connection should be used.

Answer 1

I think you're looking for a blanket answer, but it may help you decide depending on what you consider to be better.  For example:

 

1. Inserting Data: SSIS provides an OLE DB destination that exposes SQL Server's fastload functionality.  There is no out-of-the-box component dedicated to inserting data using ADO.Net.

2. Component Support: Some components only support one connection and not the other.  For example, the lookup component only supports OLE DB.

3. Metadata Change: The OLE DB Source adapter has better metadata management logic than the DataReader Source adapter.

4. Scripting: ADO.Net is preferred when used in scripts (the script task and transform only support VB.Net).

 

You should also look at the SSIS Connectivity White Paper.  There's a link to it in the Announcements section of this forum.

Answer 2

Thanks for the information.

 

I'll go through the connectivity document too.

 

Appreciate your help.

 

Thanks and regards,

Ashish Basran