SSIS Project Connection Manager Naming Error

I am sometimes asked, “Andy, how do I start a technical blog?” I demonstrate one way here – a way that has served me well with search engines.
1. Describe how the error occurred.
2. Copy the error message, post it as text.  
3. Describe the solution.

I am helping a large enterprise client migrate their data integration from another data integration platform to Azure-SSIS. It’s a two-step process:

Step 1: Convert the existing data integration operations to SSIS and test the conversion by deploying to an on-premises SSIS Catalog.

Step 2: Lift and shift the SSIS Catalog configurations from on-premises to Azure-SSIS (we will be using SSIS Catalog Compare for this part).

An SSIS Error

In the midst of designing SSIS solutions we encountered an interesting SSIS error:

Failed to start project

Exception deserializing the package “Operation is not valid due to the current state of the object.”. (Microsoft.DataTransformationServices.VsIntegration)

As a professional consultant who has been blogging about SSIS for 12 years and authored and co-authored a dozen books related to Microsoft data technologies, my first response was:

“Whut?!”

I bingled (credit for the term, “Bingle”: Ed Danger Watson) and found nothing helpful. I was beginning to think something unfortunate had happened to the client’s SQL Server Data Tools SSIS template when a crazy idea popped into my noggin. I said to myself:

Self? Could it be the odd-looking name of the Project Connection Manager?

Andy – circa 2019

The Odd-Looking Name of the Project Connection Manager

When you create a new SSIS Connection Manager, SSIS automagically names it for you. Awesome, right? Most of the time. But sometimes the default name created for a package connection manager doesn’t play nice with Project Connection naming requirements.

Don’t believe me? Try this:

Create a new SSIS package.
Add a package connection manager aimed at a named instance of SQL Server, such as “vDemo\Dev”:

Next, (attempt to) promote the package connection manager to a project connection manager:

Note the error:

The error message in this case reads:
Failed to convert the package connection mananager ‘vDemo\Dev.WeatherData’ to a project connection manager because ‘vDemo\Dev.WeatherData’ is not a valid stream name in the project. Rename the package connection manager and do the conversion again.
Object name ‘vDemo\Dev.WeatherData.conmgr’ is not valid. Name must contain only alphanumeric characters or underscores ‘_’.

The backslash – “\” – in the connection manager name – which works just fine for the Package Connection Manager – blocks promotion of the package connection manager to a Project Connection Manager.

Bummer.

Back to the problem at hand…

My client’s Project Connection Manager name included square brackets (it’s an Oracle thing…). Renaming the project connection manager – simply removing the square brackets – cleared the error:

The package now executes in the SSIS debugger:

SSIS is a complex and flexible software development platform. I learn new things with each and every consulting project. Why did I think of the connection manager name? In testing SSIS Catalog Compare version 4, I tried out a bunch of naming combinations. I was shocked to learn some characters are permitted (did you know you can create an SSIS Catalog Folder name that contains an apostrophe??).

Happy Integrating!

:{>

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, SSIS and Biml guy. I was cloud before cloud was cool. :{>

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.