migrating ssdt project 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 ssis toolbox:

and the cdc source component looks like:

And cannot be opened as well.

If I create a new Visual Studio 2017 Project it has the cdc source component in the ssis toolbox:

and I can work with it:

Counterblast

If you can post a screenshot of the upgrade operation result (the HTML page shown after upgrade), i can more clarify the problem. – Encrata

I think that this is active thread at Microsoft Connect. It is not fixed yet – Gunnery

@Counterblast can you provide the upgrade report? – Gunnery

Please do what @Encrata recommended - "create a ssis 2017 package that contains CDC Source with the same configuration, and open both dtsx files (the new package and the upgraded package) with a text editor, and compare both XML syntax of the CDC Source". I would add - post both XML fragments here. Perhaps this issue can be fixed manually by altering XML document. – Steele

Answer 1

I was researching this issue a bit and found that some people have success migrating packages by just changing the target version of the packages from 2012 to 2016. Have a look at this blog post to see it in action.

Longsufferance

This is the correct answer, just had to change target version as shown in the blog post. Thanks!!!! – Counterblast

There is a problem with this. When we did this we later noticed that the Task Scripts had become empty – Counterblast

Answer 2

Some Suggestions that can put you on the right way

First of all, it is good to post the upgrade results, so it can more clarify the issue. Also upgrade operations are not always working 100%, i faced many issue after upgrading package, which in many times caused to recreate packages from scratch.

(1) Compare both packages XML

.dtsx files are written in XML

One thing i can suggest, create a ssis 2017 package that contains CDC Source with the same configuration, and open both dtsx files (the new package and the upgraded package) with a text editor, and compare both XML syntax of the CDC Source, i can leads you to the issue cause

(2) Compare both packages properties

Comparing PackageFormatVersion, VersionBuild, and other properties can lead to the issue

Also try comparing both packages version informations, so from the PackageFormatVersion (if it is the same between the two packages that mean that they are both targeted to SQL Server 2012 so you need to change the Target version in the solution properties) and other field you can get if there is any difference or it can lead you to the issue.

Read more about getting packages informations in my answer on the following question: Automate Version number Retrieval from .Dtsx files

Also read more about changing target version in this Microsoft article - Version targeting in Integration Services projects section

---

If suggestions above failed

If none of the first suggestions leads you to the issue cause, i didn't find a precise cause. but i find some links showing some problem when upgrading SQL server or when deployment is on a machine with different SQL server version - which I cannot affirm that this is 100% true - but i think that there are many issue concerning the CDC that are not fixed by Microsoft.

You can see this from the following links as example:

• CDC functionality may break after upgrading to the latest CU for SQL Server 2012, 2014 and 2016
• SSIS 2012 - CDC Source Component - Deployment Error
• Breaking change in Change Data Capture This problem is still marked as Active, which mean that it is not fixed yet
• Issues upgrading an SSIS solution/package from Visual Studio 2012 to VS 2013
• SSIS Package upgrade problem

Encrata

Very detailed answer, but after trying every link you posted, my issue is still unresolved. – Counterblast