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!
:{>
Comments