Dynamically configure CacheType?

I'm looking for a way to dynamically set the CacheType property of a Lookup transformation via a configuration variable. Is this possible? The CacheType does not appear to be a selectable property when choosing which properties to export to a configuration file. Can this property be manipulated through a script task inside the package?

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

|||

Ravi G wrote:

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.|||

Phil Brammer wrote:

Ravi G wrote:

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Took the words right out of my mouth Phil In case anyone is interested, expressions on data-flow components was virtually the very last feature that was added to the product prior to RTM. All components CAN support expressions on their custom properties, the component developer decides whether or not it WILL by setting IDTSCustomProperty90.ExpressionType which is set to one of the values of the DTSCustomPropertyExpressionType enumeration.

In answer to the OP, CacheType of the Lookup component does not allow its value to be set by an expression, so you can't do what you want I'm afraid. If you want this behaviour changing then go to http://connect.microsoft.com/sqlserver/feedback

-Jamie

|||

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

|||

Ravi G wrote:

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

That's because not all components allow you to set their properties using expressions. if they do, then those properties will show up (off the top of my head I know that the SQLCommand property of the Datareader Source component can be set this way so take a look at that).

Ravi G wrote:

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

The path syntax allows for it - as you shall see.

Sounds like good blog material

-Jamie

|||

Ravi G wrote:

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]|||

Phil Brammer wrote:

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]

Cool. I see it now.

In my package, the derived column was the only component that supported this.

This is how it shows up:

[DF component name].[Derived Column Output].[Column Name].[FriendlyExpression]

What does FriendlyExpression mean?

|||

Ravi G wrote:

Phil Brammer wrote:

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]

Cool. I see it now.

In my package, the derived column was the only component that supported this.

This is how it shows up:

[DF component name].[Derived Column Output].[Column Name].[FriendlyExpression]

What does FriendlyExpression mean?

That's the name of the property. Don't worry too much about why its called that, just know that it is a custom property that can be affected with an expression. More here: http://msdn2.microsoft.com/en-us/library/ms141069.aspx

Admittedly this takes a bit of getting your head around. Basically you can set an expression using an expression. Or to put it another way, the result of your expression is, in itself, another expression.

-Jamie

|||

I've found this useful reference for all the custom properties of the stock components.

http://msdn2.microsoft.com/en-us/ms136014.aspx

-Jamie

Dynamic Tooltip for TextBoxes in Reports?

Hi,

I want to add dynamic tooltip for the textboxes on a report where the tooltip text comes from a database lookup or maybe a resource file?

How can this be done. This is a required feature for our client and we do not want to add "Constants" as tooltip text which can be done in min as the drawback would be if the value for the tooltip changes, it has to be changed in all the reports wherever it appears.

Thanks again.You can do this by creating a second data set and binding the tooltip to aggregate expressions, i.e. =First(Fields!Tooltip1.Value, "TipQuery"). Or you could write a cusom assembly and get them from a custom resource file (would require additional permission in the assembly on the server).

Dynamic SQL within a function

Hello,
I need to create a view on a database based on tables from other databases.
I'm creating a function to lookup the data in the other dbs and use the
function on the view. This is the function.
CREATE FUNCTION CTI_InventoryView ()
RETURNS @.CTI_Inventory TABLE
(INTERID VARCHAR(5), CMPNYNAM VARCHAR(65),
ITEMNMBR VARCHAR(31), ITEMDESC VARCHAR(101),
LOCNCODE VARCHAR(11), RCRDTYPE SMALLINT,
PRIMVNDR VARCHAR(15), LSORDQTY NUMERIC(19,5),
LRCPTQTY NUMERIC(19,5), LSTORDDT DATETIME,
LSORDVND VARCHAR(15), LSRCPTDT DATETIME,
QTYRQSTN NUMERIC(19,5), QTYONORD NUMERIC(19,5),
QTYBKORD NUMERIC(19,5), QTY_Drop_Shipped NUMERIC(19,5),
QTYINUSE NUMERIC(19,5), QTYINSVC NUMERIC(19,5),
QTYRTRND NUMERIC(19,5), QTYDMGED NUMERIC(19,5),
QTYONHND NUMERIC(19,5), ATYALLOC NUMERIC(19,5),
QTYAVAIL NUMERIC(19,5), QTYCOMTD NUMERIC(19,5),
QTYSOLD NUMERIC(19,5))
AS
BEGIN
DECLARE
@.INTERID VARCHAR(5),
@.COMPANYNAME VARCHAR(65),
@.SQL NVARCHAR(1000)
DECLARE CTI_Companies CURSOR FOR
SELECT INTERID, CMPNYNAM
FROM DYNAMICS..SY01500
OPEN CTI_Companies
FETCH NEXT FROM CTI_Companies
INTO @.INTERID, @.COMPANYNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'USE ' + @.INTERID + 'SELECT ' + ''''+ @.InterID + '''' + ', '
+ '''' + @.CompanyName + ''''
SELECT @.SQL = @.SQL + 'IV00102.ITEMNMBR, ITEMDESC, IV00102.LOCNCODE,
RCRDTYPE, PRIMVNDR, '
SELECT @.SQL = @.SQL + 'LSORDQTY, LRCPTQTY, LSTORDDT, LSORDVND, LSRCPTDT,
QTYRQSTN, QTYONORD, QTYBKORD, '
SELECT @.SQL = @.SQL + 'QTY_Drop_Shipped, QTYINUSE, QTYINSVC, QTYRTRND,
QTYDMGED, QTYONHND, ATYALLOC, '
SELECT @.SQL = @.SQL + 'QTYONHND - ATYALLOC, QTYCOMTD, QTYSOLD '
SELECT @.SQL = @.SQL + 'FROM IV00102 JOIN IV00101 ON IV00102.ITEMNMBR =
IV00101.ITEMNMBR '
INSERT INTO @.CTI_Inventory
EXECUTE sp_executesql @.SQL
FETCH NEXT FROM CTI_Companies
INTO @.INTERID, @.COMPANYNAME
END
CLOSE CTI_Companies
DEALLOCATE CTI_Companies
RETURN
END
GO
I found that I cannot call the sp_executesql stored procedure inside the
function. I receive this error
Msg 443, Level 16, State 14, Procedure CTI_InventoryView, Line 40
Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC'
within a function.
I'm running out of ideas to do this, any ideas how to do this?,
Thanks,
Sandra.Sandra Parra (sparra@.citrinetech.com) writes:
> I need to create a view on a database based on tables from other
> databases.
> I'm creating a function to lookup the data in the other dbs and use the
> function on the view. This is the function.
>...
> I found that I cannot call the sp_executesql stored procedure inside the
> function. I receive this error
> Msg 443, Level 16, State 14, Procedure CTI_InventoryView, Line 40
> Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC'
> within a function.
> I'm running out of ideas to do this, any ideas how to do this?,
Why are there so many databases, and why do need a view over all them?
I ask this question to possibly be able to give a better response.
From what I see here, I can think of two ways:
1) The number of databases is so dynamic, that you need a iterate over
all databases for every query. In such case, write a stored procedure
and get data into a temp table.
2) The number of databases is static enough, so you can set up a
job that defines a view every night, so that run can run queries
on this view during the day.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx