I have a solution file, originally developed in SSDT 2012 by the book (TK 70-463) authors. I open it in Visual Studio 2013 (with upgrading) as I do not have SSDT for SQL Server 2012 installed.
For SQL Server, I have 2008 R2, 2012, and 2014 installed on the machine.
After I test the solution with two projects successfully, I deploy the projects to SSISDB in SQL Server 2012, no problem! But when I validate the project or the packages, I get the error as above in the title.
Reason:
Visual Studio 2013 is not compatible with SQL Server 2012.
Well, I thought that I may re-deploy it to SQL SERVER 2014. So I try to create a catalog in SSISDB in SQL Server 2014. Oops, I get an error: sql server denali is required to install integration services.
Reason:
I have already created a catalog in SQL Server 2012. SSIS is per-machine-based. I could try to delete the catalog in SQL Server 2012 and create one in SQL Server 2014. But I decide to keep the catalog in SQL Server 2012. So I need to install SSDT for SQL Server 2012.
I re-run the SQL Server 2012 installation again. Surprisingly, the data tool option is already selected and dimmed in the shared section. It seems that it was installed, although it is actually not.
Solution:
Step 1 - Download the
Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012
Step 2 - Install it - Choose new installation (not add a feature for existing instance) for this x86 tool, otherwise, you will get the error on system architecture.
Step 3 - Open the solution file in the SSDT 2012. Note you cannot use the upgraded one in Visual Studio 2013 (downgrade is not supported), you need to download the original book code file developed in SSDT 2012.
Step 4 - Re-deploy and validate it. Hoops,
Error:
There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server... The AcquireConnection method call to the connection manager AW2012_ODBC failed with error code 0xC0014009...
Solution 1
Set DelayValidation to True for the package, alternatively
Solution 2
Check 32-bit runtime on the Validate Project interface
By the way, you can find the connection manager's ID via right click and then view code.
<?xml version="1.0"?>
<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:ObjectName="TK463DW"
DTS:DTSID="{6F1C3D18-CA41-438C-A363-5195A087BBEE}"
DTS:CreationName="OLEDB">
<DTS:ObjectData>
<DTS:ConnectionManager
DTS:ConnectionString="Data Source=localhost\SQL2012;Initial Catalog=TK463DW;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
</DTS:ObjectData>
</DTS:ConnectionManager>
