ADO.NET Source Custom Properties - Documentation Wrong?

Ok, so I've looked near and far and have found nothing but info that says data flow properties can not be changed at runtime....then I see in this in the SSIS documentation under ADO.NET Source Custom Properties:

SQLCommand

String

The SQL statement that the ADO.NET source uses to extract data.

The value of this property can be specified by using a property expression.

Pray tell me how this property can be specified using a property expression, or any other dts variable for that matter?
Thanks,
Adrian Crawford
Adrian,

You would never find this on your own, and I realize now that we need to document this more clearly in several places in BOL.

Property expressions for components within the Data Flow task are visible and available only on the containing Data Flow task itself at the present time. Select the Data Flow task on the Control Flow tab, or an empty spot on the Data Flow designer surface, go to the Properties window (it doesn't have a Properties dialog box), select Expressions, click the ellipsis, and in there you'll be able to drop down a list, select the SqlCommand property of your DataReader source, and set the expression.

Note that nothing will show up in the DataReader editor to indicate that you've set a value (with an expression) for the SqlCommand property...it will appear blank. I trust that the "usability" of this feature will be enhanced in the future. For now, however, it's better to have awkward access to data flow properties via property expressions, than none at all.

Best regards,

-Doug
|||Doug,
Thank you so much for your post. This is the holy grail I've been looking for, and I'll take it any way I can get it. It had boggled my mind because I had read rumors of this feature but was never able to make it work.
Thanks,
Adrian
|||Doug,

Do you know if Microsoft intends (in future revs) on allowing Web Service Task web-parameters to be updateable dynamically at runtime? Right now this one is killing me. I mean, who calls a web service with the same parameter every time?!?|||

Adrian,

Glad to hear it. I apologize for overlooking your earlier post on the same subject.

I've just finished updating and adding links to multiple BOL topics so that this not entirely intuitive how-to information will be easier to locate.

As I assume you've discovered, the Property list in the Property Expressions Editor only displays available properties for data flow objects that you've already placed on the Data Flow surface of the designer. (As a result, you can't use the Property list to view all the properties of data flow objects that support property expressions.) For example, if you've placed a DataReader Source on the designer surface, the Property list contains an entry for the [DataReader Source].[SqlCommand] property. The list also displays many properties of the Data Flow task itself.

Enjoy your expressions,

-Doug

|||I don't know the answer to that, but I understand why it would be useful. You can make your request known, as always, by using sqlwish@.microsoft.com and BetaPlace http://msdn.microsoft.com/sql/bugs/default.aspx.

Best regards,

-Doug
|||Just sent the request. I will feel so special if I, personally, effect the direction of Microsoft. :)

Just one more step in my eventual goal: Total global domination.|||A mere email to sqlwish may not achieve your entire goal, but Microsoft is more responsive to customer input than the average person gets to see. Especially the documentation team (have I mentioned those Feedback links in BOL?), where improving a BOL topic runs less risk of having unforeseen side-effects than code changes.|||Doug

I have the same problem as Adrian. I need to set the SQL Command of the Data Reader from a Variable. Trying your approach, the Property Drop Down List of the Property Expression Editor does not list an SqlCommand property.

What could be the problem?

Torsten|||Not many of the stock components implement expressions. There is a list in Books Online -

Using Property Expressions to Specify Data Flow Property Values
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/cd0e171a-08be-45d6-81dc-ed94f37698b8.htm

Seems like a reasonable request though so why not log it-
http://lab.msdn.microsoft.com/productfeedback/default.aspx|||If the ADO.Net source is in the dataflow, and you open the Expressions dialog for that dataflow, you should indeed find an entry in the properties drop down. It will likely be called "[DataReader Source].[SqlCommand]"

Thanks
Mark|||

I have a similar problem

want to use the property expression on data reader source

it loks like this

select @.var1, @.var2 from @.var3 inner join @.var4

where all the variables are from the foreachloop container > variable mapping

just tell me if this is correct

|||

You should be able to use aproperty expression (set it at the data flow level though, Expressions), but your syntax is not valid. Have a look at the BOL reference for the expression syntax. It should look more like this -

"SELECT " + @.var1 + ", " + @.var2 + " FROM " + @.var3 + " inner join " + @. var4 + " ON some columns!!!"

The evaluated expression result should give you a vali SQL statement that you could copy into any query tool and run, if not make it so that it can.