Microsoft – SQL Server – Business Intelligence / Sql Server Data Tools (SSDT) – Error – Test connection failed because of an error in initializing provider. The ‘SQLNCLI.1’ provider is not recognized on the local machine.

Introduction

I am currently using SQL Server Data Tools to edit a DTSX package. Upon choosing one of the connection manager and choosing to edit it, I run into the error referenced in the error section.

Error

Textual:

Test connection failed because of an error in initializing provider.  The 'SQLNCLI.1'provider is not registered on the local machine.


Image:

TestConnectionFailedBecauseofAnErrorInInitializingProvider

Error Interpretation

We are unable to fully edit the package due to a missing provider or driver.  In this case, the missing provider is SQLNCLI.1

Provider Matrix

MS SQL Server Version Component Service Download Link
SQLNCLI.1 SQL Server 2005 http://www.microsoft.com/en-us/download/details.aspx?id=15748
SQLNCLI10.1 SQL Server 2008 – R2 http://www.microsoft.com/en-us/download/details.aspx?id=30440
 SQLNCLI11 SQL Server 2012 http://www.microsoft.com/en-us/download/details.aspx?id=35580

Provider Download Area & Instruction

Here are download URL and Instructions for recent SQL Server versions.

Microsoft SQL Server Native Client – v2005

  1. Access “Feature Pack for Microsoft SQL Server 2005 – November 2005” ( http://www.microsoft.com/en-us/download/details.aspx?id=15748 )
  2. Access the Install Instructions section
  3. Navigate to the “Microsoft SQL Server Native Client” sub-section
  4. Based on your OS, choose to download the applicable file

Microsoft SQL Server Native Client _ v2005

Microsoft SQL Server Native Client – v2008/R2

  1. Access “Microsoft® SQL Server® 2008 R2 SP2 Feature Pack” ( http://www.microsoft.com/en-us/download/details.aspx?id=30440 )
  2. Click on the downloads button
  3. Navigate to the sqlncli* files

MicrosoftSQLServer2008R2SP2FeaturePack

 

Microsoft SQL Server Native Client – v2012

  1. Access “Microsoft® SQL Server® 2012 Native Client” ( http://www.microsoft.com/en-us/download/details.aspx?id=29065 )
  2. Navigate to the “Install Instructions” section
  3. Navigate to the “MICROSOFT SQL SERVER CONNECTIVITY FEATURE PACK COMPONENTS” \ “Microsoft® SQL Server® 2012 Native Client”
  4. Based on your bitness, choose to download the x86 (32-bit) or x64 (64-bit) file

Package Description:

Microsoft SQL Server Native Client _ v2012_v1

Choose the download you want:

Microsoft SQL Server Native Client _ v2012_v2

Install

Install the downloaded package.

Connection Manager succeeded

Once installed, come back to your designer app and retry

TestConnectionSucceeded

Review loaded providers and drivers

Review ODBC Drivers

Via the OS Control panel, access the “ODBC Administrator” applet and review the list of ODBC Drivers:

ReviewInstalledProvidersUsingODBCAdministratorApplet

Review Data Link providers

You can also get a list of Data Link Providers, via editing creating and editing UDL files.

  • Launch Windows Explorer
  • Create a new text file (myudl.txt)
  • Rename the file from myudl.txt to myudl.udl
  • Right click on the file and from the drop down menu, select open
  • Access the “Provider” tab

DataLinkProperties

Review the list of listed providers.

Summary

In conclusion when you find yourself editing SQL Server Integration Services (SSIS) packages, you might need to locate and install the Data providers used during the original development.

In some cases, SQL Server Business Intelligence or SQL Server Data Tools (SSDT) might ask if you want to upgrade the components, but I will suggest you say No and stick with whatever version is installed on the actual SQL Server Hosting machine.

References

References – Download Section

 

References – Configuring & Using Data providers – UDL

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s