Hello!
We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”.
If you have worked with SQL Server Integration Service in Denali CTP3, there is a potential bug in the code. This post will showcase a workaround to avoid this bug.
I have decided to divide this post in two parts:
- Replicate the bug
- Resolution of the bug
Before we proceed ahead, let us have a short revision about a new feature the IS team has provided in SQL Server 2012. This is called Project Connections and Package Connections.
This can be summarized as below:
Project Connection
These connection managers to data sources and data destinations are visible to any/all packages in the IS solution. For example: An ADO.NET connection created for package A, if made a project connection, can be re-used in subsequent packages (package B, package C, etc…)
Package Connection
This is the contra version of Project Connection. Such a connection can only be accessed within a package i.e. their scope is limited to the package they are created for.
I have composed a blog on this feature. Click here to access the content.
Replicate the bug
Let us create a new Integration Services project and now create a new connection manager. By default the connection manager created is a Project Connection. For example, an Excel Connection Manager created will have name like “Excel Connection Manager.conmgr”
Anytime, this connection can be switched back to a package connection by editing the existing connection.
If you were to create a similar connection from the connection manager tab, you can create a package connection and later switch to project connection, if the need arises.
Now, suppose we were to connect to a named instance of any server like <<serverName\instanceName>> any attempt made to make this connection as a project connection will fail, with an error message that the connection name has invalid token as connection name. (Error: <<connectionManagerName.conmgr>> is not a valid stream name in the project, to be precise)
What actually happened! The connection manager had a “\” (which separates the serverName with the instanceName)
So, if you were to make a connection (especially, to any named instance of any server) you will face this error.
Resolution of the bug
Fairly simple, and in fact a one liner solution to overcome this potential issue, till this is fixed in the final release.
Rename the package connection with some user friendly name which is devoid of any special characters
That’s all. Happy learning
In the next post, we shall see yet another exciting feature in SSIS which is called a parameterize the task.
Regards
Raunak Jhawar
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
