Expression Builder of Connection Manager not showing Variables

Question

I'm having this exact same problem I've been looking aroung but this is the only place I've seen the same issue and it is not resolved.

Does anyone knows what the problem might be?

I checked in Visual Studio and my SSIS version is 11.0.2100.60 (not a trial, not a beta).

EDIT: These are the steps I'm taking and the issue

First I choose a Connection Manager, right-click, select properties and click in Expressions option

Then in Property Expression Builder choose Connection String property and click in Expression option

Finally, in the Expression Builder dialog there is no option for variables, in every page I've read says that there should be a Variables node in there

Am I missing something?

Answer 1

The Microsoft SSIS tutorial have these confusing statements:

1. In the Connection Managers pane, right-click Sample Flat File Source Data, and select Properties.
2. In the Properties window make sure the PackagePath starts with \Package.Connections. If not, in the Connection Managers pane, right-click Sample Flat File Source Data, and select Convert to Package Connection. \Package.Connections was initially displayed in the properties window, although I had not yet converted to Package Connection. After reading this thread, I right clicked on Sample Flat File Source Data and then Convert to Package Connection and was able to complete the lesson. Thanks for the answers!

Answer 2

i had the same problem and it was because the flat file source was set as a project source so i had to convert it into a package connection.

Right-click on the source and choose convert to package connection

Maybe this will help.

Answer 3

SSIS 2012 has introduced the concept of Project level connection managers. What I see on the referenced post on the MSDN forums it the user has created a project level flat file connection manager and is unable to configure it with a local variable. Assuming that is the problem, my answer follows.

An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.

Logically, if a thing is shared across a project, how can something that only exists in one file configure that resource? That configuration change would only work when Package1 is executing. When Package2 fires, unless the same variable and same expression was applied to the shared resource, you would experience different outcomes. That'd be a maintenance nightmare, which you might already experience if you don't have strong configuration practices.

If I create a Flat File Connection Manager at the project level, I can only reference variables that are also at the project level. Except there are not variables at the project level. Instead, they are called Parameters.

To that end, I created a Parameter called SomeProjectParameter

I then created a package, Package1.dtsx, and added 2 Flat File Connection Managers: FlatFileConnectionManagerLocal and FlatFileConnectionManagerProject

Instead that package, I also created a variable called SomeLocalVariable.

This screenshot shows me applying an expression to the ConnectionString property of FlatFileConnectionManagerLocal. There you can see that both the package variable, SomeLocalVariable is available as well as SomeProjectParameter

Now, if I try to apply an expression to the project's connection manager, you will only have project parameters available to you.

It's interesting to note that you can't apply an expression to a project level Connection Manager outside of the context of an SSIS package. There's simply no editor available to you until you have an open SSIS package. But, once applied, all the packages in the project will be similarly configured.

Quirk of the IDE I suppose. Also, don't be alarmed by the lack of color in these screenshots, I'm running with the 2012 version of SSDT.