Skip to content

SQL Server connection Manager Tips

November 7, 2017

Following are the highlights of OLE DB and ADO.NET connections:

  • OLE DB connection is supported by majority of tasks/transformations in SSIS, but a few tasks/transformations like Bulk Insert Task, Lookup Transformation etc. support only OLE DB connection.
  • ADO.NET connection is supported by many tasks/transformations in SSIS, but a few tasks/transformations like Data Profiling Task, CDC Control Task etc. support only ADO.NET connection.
  • Mapping Parameters in Execute SQL Task is very convenient and clear and easy to understand using ADO.NET Connection.
  • OLE DB connection (being a native connection) is a bit faster than ADO.NET in most scenarios.

Among various connection types supported by Execute SQL Task, ADO.NET is the only connection type which allows specifying the parameter mapping clearly.

Adding an “Application Name” property to a SSIS connection string to easily trace the queries being executed by SSIS package

 

Advertisements

From → ETL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: