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.
Test connection failed because of an error in initializing provider. The 'SQLNCLI.1'provider is not registered on the local machine.
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 Download Area & Instruction
Here are download URL and Instructions for recent SQL Server versions.
Microsoft SQL Server Native Client – v2005
- Access “Feature Pack for Microsoft SQL Server 2005 – November 2005” ( http://www.microsoft.com/en-us/download/details.aspx?id=15748 )
- Access the Install Instructions section
- Navigate to the “Microsoft SQL Server Native Client” sub-section
- Based on your OS, choose to download the applicable file
Microsoft SQL Server Native Client – v2008/R2
- Access “Microsoft® SQL Server® 2008 R2 SP2 Feature Pack” ( http://www.microsoft.com/en-us/download/details.aspx?id=30440 )
- Click on the downloads button
- Navigate to the sqlncli* files
Microsoft SQL Server Native Client – v2012
- Access “Microsoft® SQL Server® 2012 Native Client” ( http://www.microsoft.com/en-us/download/details.aspx?id=29065 )
- Navigate to the “Install Instructions” section
- Navigate to the “MICROSOFT SQL SERVER CONNECTIVITY FEATURE PACK COMPONENTS” \ “Microsoft® SQL Server® 2012 Native Client”
- Based on your bitness, choose to download the x86 (32-bit) or x64 (64-bit) file
Choose the download you want:
Install the downloaded package.
Connection Manager succeeded
Once installed, come back to your designer app and retry
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:
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
Review the list of listed providers.
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.
- SQL Native Client 10 – provider name change
References – Download Section
- Feature Pack for Microsoft SQL Server 2005 – November 2005
- Microsoft® SQL Server® 2008 R2 SP2 Feature Pack
- Microsoft® SQL Server® 2012 SP1 Feature Pack
References – Configuring & Using Data providers – UDL
- Creating and Configuring Universal Data Link (.udl) Files