20

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 step one

Then in Property Expression Builder choose Connection String property and click in Expression option enter image description here

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 enter image description here

Am I missing something?

jorgehmv
  • 3,633
  • 3
  • 25
  • 39
  • The referenced question seems to have a number of things going on in it. What specifically is your question? – billinkc Mar 21 '13 at 19:11
  • you can refer to user CUQSY0's question and his first reply (the one with the screenshots) those 2 posts describe the problem – jorgehmv Mar 21 '13 at 19:17

3 Answers3

39

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

project parameter

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

expression builder on local CM with parameter and local variable

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

project CM and only parameters available

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • 4
    awesome answer, just for the record I'm following the tutorial http://msdn.microsoft.com/en-us/library/ms169917.aspx, so anyone following this tutorial with this data tools version will face the same problem – jorgehmv Mar 21 '13 at 20:08
  • 3
    @jorgehmv Excellent, good to know. Feel free to ask all the SSIS questions you want over here. We've got a pretty good crew here that knocks them out. – billinkc Mar 21 '13 at 20:19
  • 2
    Excellent Answer billinkc. I was having the same problem with the same tutorial and your article ended a full day of absolute frustration. Just one suggestion: you might include a few words on how to create a package level connection or change a project level connection to a package level connection. – DRiggins Jul 09 '13 at 16:01
  • 2
    This post helped another user trying that tutorial! For me, the tutorial was not at all clear that you should not (as far as I now understand it) be creating your Connection Managers in the Solution Explorer view, but the Connection Managers under the Control Flow and DataFlow windows in the package design view. – Nij Sep 02 '13 at 21:13
13

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.

Initial flat file source

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

Convert to package connection

Maybe this will help.

Themba Mabaso
  • 411
  • 4
  • 12
0

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!
Gregg Taylor
  • 11
  • 1