Ssdt project migration from visual studio 2012 to 2017 cdc source not opening

Question

We migrated packages from Visual Studio 2012 to 2017. There is no cdc source component in the sys toolbox:

and the cdc source component is as follows:

And it cannot be opened.

If I create a new Visual Studio 2017 project, it has the original cdc component in the ssis toolbar:

and I can work with him:

Answer 1

I was a little versed in this problem and found that some people successfully migrate packages by simply changing the target version of the packages from 2012 to 2016. Check out this blog post to see it in action.

Answer 2

Some suggestions that may lead you to the right path

First of all, it’s useful to publish the update results so that it can clarify the problem more clearly. In addition, the update operations do not always work 100%, I encountered many problems after updating the package, which in many cases caused the packages to be recreated from scratch.

(1) Compare both XML packages

.dtsx files are written in XML format

One thing I can offer is to create a ssis 2017 package containing a CDC source with the same configuration and open both dtsx files (new package and updated package) with a text editor and compare the XML syntax of the CDC Source , I can cause a problem .

(2) Compare both package properties

Comparing PackageFormatVersion, VersionBuild, and other properties may cause a problem

Also try to compare the version information of both packages, therefore from PackageFormatVersion (if it is the same between the two packages that they are both aimed at SQL Server 2012, so you need to change the target version in the solution properties) and another field that you can get if there is any Any difference, or it may lead you to a problem.

More information about getting package information in my answer on the following question: Automate version number Extract from .Dtsx files

Also read more about changing the target version in a Microsoft article - version targeting in the Integration Services project section

---

If the above suggestions do not match

If none of the first sentences leads you to a problem, I have not found the exact cause. but I find some links showing some problems when updating the SQL server or when the deployment is on a machine with a different version of the SQL server - which I cannot claim to be 100% true ; but I think there is a lot of CDC related issue that are not fixed by Microsoft.

You can see this from the following links as an example:

• CDC functionality may fail after upgrading to the latest CU for SQL Server 2012, 2014, and 2016
• SSIS 2012 - CDC Source Component - Deployment Error
• Violation of a change in Data Capture Data This problem is still marked as active, which means that it has not yet been fixed.
• Problems upgrading SSIS solution / package from Visual Studio 2012 to VS 2013
• SSIS Package Update Issue