Cannot Access Expression Builder Via Variable Properties Window (no Ellipsis)
Sep 5, 2007
Has anyone encountered this before?
My colleague opens up an SSIS package with variables, many of which use expressions built with the Expression Builder. She cannot bring up the Expression Builder by clicking the ellipsis (...) in the "Expression" Property of the Property Window, regardless of whether "EvaluateAsExpression" is set to True or False.
I can open up this same package and use the Expression Builder just fine. Any ideas?
Apologies if this has been answered already, I did many searches to try to find the solution before posting here.
Thanks much!
Brian Pulliam
View 14 Replies
ADVERTISEMENT
Jan 23, 2008
Hello,
I am using the following expression to access a daily file.
"D:\importdata\peregrinedata\ACD_DATA_" + (DT_WSTR, 4) YEAR( GETDATE() ) + (DT_WSTR, 2)MONTH( GETDATE() ) + (DT_WSTR, 2) DAY( GETDATE() ) + ".txt"
output: D:importdataperegrinedataACD_DATA_2008123.txt
I need to have this file by current day -1. I've tried dateadd and can't figure out how to get it to work.
Thanks
View 3 Replies
View Related
Jan 18, 2007
"InteractiveHeight is only supported on RS 2005. Opening a report in BI Development Studio of SQL Server 2005 will automatically add this property. You can find the property on the Report element. In report designer (properties window) it is also on the report element / Layout / Interactive Size / Height."
Why don't I see the Report Properties screen that includes the Interactive Size elements? My Report Properties screen looks the same as it did in VS2003.
I need to modify the InteractiveHeight element. It has been added to the RDL source.
Here's what's installed:
Microsoft Visual Studio 2005
Version 8.0.50727.762 (SP.050727-7600)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
Microsoft Visual Basic 2005 77626-009-0000007-41496
Microsoft Visual Basic 2005
Microsoft Visual Web Developer 2005 77626-009-0000007-41496
Microsoft Visual Web Developer 2005
Microsoft Web Application Projects 2005 77626-009-0000007-41496
Microsoft Web Application Projects 2005
Version 8.0.50727.762
Microsoft Visual Studio 2005 Professional Edition - ENU Service Pack 1 (KB926601)
This service pack is for Microsoft Visual Studio 2005 Professional Edition - ENU.
If you later install a more recent service pack, this service pack will be uninstalled automatically.
For more information, visit http://support.microsoft.com/kb/926601
SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00
SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00
View 1 Replies
View Related
Apr 4, 2007
I am trying to set up SQL Server 2005 to use a test certificate from Thawte for SSL encryption. I have installed the certificate in the local computer, current user and service account's personal certificates folders. I have also installed the root certificate in the Trusted Root Certification Authorities folders of each. All this was done using MMC.
However, when I go to tell SQL Server to use the certificate, no certificates show up in the drop down box. I am using SQL Server Configuration Manager and doing the right-clickPropertiesCertificate steps shown in multiple KB and forum articles.
I have also checked the registry entry at HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerSuperSocketNetLibCertificate. It is blank, which tells me SQL Server should be looking in the certificate store.
If I simply set the ForceEncryption flag, SQL Server starts up OK and generates a self-signed certificate. Using that, the session does get encrypted. However, I need to use a third party certificate.
I have hit a brick wall and am at a loss. Any help would be greatly appreciated.
View 10 Replies
View Related
Jan 8, 2007
I installed the hot fix RS2005-KB920794-x86-ENU.exe, what supposes to solve the problem that "selection of multiple values in parameters of a report" generated in Report Builder is possible. Unfortunatly, this issue is not solved by the hot fix.
Could you please help me.
Thank you very much for your help in advance.
Best reagards,
Olga Jaufman
View 3 Replies
View Related
May 25, 2015
If, in an SSIS package, you put an instance of an 'Execute SQL Task' task in the Control Flow, in the Properties window, you can see the properties of the task, for example CodePage.
If you double click on the task, the Execute SQL Task Editor appears, with several of the properties which are also in the Properties window, including CodePage.
If, in the Editor, you update the value of CodePage, then click OK, the value of CodePage in the Properties window is updated immediately.
I have written a custom SSIS task, which also has the same properties in the Properties window and in the Editor. The Editor also has an OK button. When OK is clicked, the values of the task properties are updated. An example property is FolderToArchive. If I open the Editor, change the value of FolderToArchive and click the OK button, the value of FolderToArchive in the Properties window is NOT immediately updated.
If, however, I select the FolderToArchive field in the Propertiesd window, it is then updated with the value I entered in the Editor.
How do I get my task to update the values in the Properties window, after changing a value in the Editor, when I click the OK button?
I would have thought I would need something like, in pseudo-code,
   Task.Parent.PropertiesWindow.Refresh
   where task is of type Microsoft.SqlServer.Dts.Runtime.Task and Task.Parent is of type Microsoft.SqlServer.Dts.Runtime.Package.
View 9 Replies
View Related
May 16, 2007
Here's a weird one:
We are setting up a job for the SQL Server Agent via SSMS. The Job Step Type is SSIS.
In the Job Step Properties window, on the Set values tab, you can enter Values to override your package variables - normally all well and good.
However in this particular case, the variable Value contains semicolons ( - it is a Connection String for an ODBC driver. Eg: Driver={Client Access ODBC Driver (32-bit)};system=MYSERVER;...
The behaviour for this Value is weird:
If the Value is not surrounded with double quotes ("), the job fails with "The command line parameters are invalid."
If the Value is surrounded with double quotes ("), the job will run as intended. The catch is: that entry and any subsequent "Set Values" entries disappear next time the Job Step Properties window is opened.
This looks like a bug with the parsing of those strings by the Job Step Properties window?
Or am I missing something?
Mike
View 5 Replies
View Related
Jan 10, 2007
Hi,
i am facing problem to dervie one column value from another column using either if and case statements in expression window of dervived columns transformation.
let me give the exapmle. i get 1 column from source system name as "col a" and i want to insert 2 columns into my destination as col A and col B. based on the values of col A i want to derive the values of col B,like if col A value is 0 then col B value is Good else BAD.
Can any one asssit in this regard how to achive it?? and is it possible to use IF and CASE statement in this dervived column tranformation???
Sreenivas
View 1 Replies
View Related
Mar 3, 2008
I have a matrix with three subtotals -- I want to be able to have the farthest column go from this with the far right columns in black
MUNCH$
MEAL-15
5
0
1
8
9
45.00
MEAL-19
5
0
2
4
6
30.00
MEAL-19 TT
5
0
1
0
1
5.00
MEAL-BLOCK155
5
0
1
0
1
5.00
Tender Total
0
5
12
17
85.00
Day Total
0
375
400
775
843.00
Terminal Total
223
1274
973
2470
2,615.68
to this with the far right columns the same color as the subtotal-- I now I probobly need to use the inscope function but cant seem to get it right
MUNCH$
MEAL-15
5
0
1
8
9
45.00
MEAL-19
5
0
2
4
6
30.00
MEAL-19 TT
5
0
1
0
1
5.00
MEAL-BLOCK155
5
0
1
0
1
5.00
Tender Total
0
5
12
17
85.00
Day Total
0
375
400
775
843.00
Terminal Total
223
1274
973
2470
2,615.68
thanks in advance
kam
View 3 Replies
View Related
Dec 1, 2007
Hi All,
I want to build following code into expression builder . I don't know how to write that things into expression builder. so can you help me out from this problems.
1.Daily stock Inventory by stores " & FormatDateTime( date() , vbshortdate )
2. " Weekly stores Queue Productivity for " & date()-8
3.Customer Data Entry Monthly Report for stores " & Month( now()-27) & "/" & Year(now()-27)
4."Customer of stores " & FormatDateTime( now() , vbshortdate ) & " - stock"
5.customers opens account " & FormatDateTime( now() -8, vbshortdate ) & " (STOCK1)
Thanks
Aric
View 6 Replies
View Related
Oct 22, 2007
Ive been using SSIS for a month or two and now find I need to create some custom tasks to perform some performance logging. in the the overloaded ProviderComponentProperties section I am trying to create a property which has the same look as the Expressions properties you find elsewhere (Little + on the left and a group of sub properties when expanded).
Ive have played with creating a IDTSCustomPropertyCollection90 collection then adding my sub properties to it but I cant seem to then add my new collection to the ComponentMetaData.CustomPropertiesCollection.
Im assuming the Expressions parameter is a collection added to the properties collection but I cant figure out how. Any help would be much appreciated.
View 4 Replies
View Related
Apr 18, 2008
am trying to do the following in SSIS.
I have Execute Process Task, in which I have the values are set for Executable, Arguments and working Directory manually. I keep changing this if the Connection Manager points to Dev or when it points to QA or to Prod. Instead I want to do like this.
If my Connection Manager points to Dev, the Executale , Arguments and Working Directory should automatically change. Else if it points to QA, the Executale , Arguments and Working Directory should automatically change and similarly for Prod.
Is this possible in Expression Builder in Execute Process Task. If so, how?.
Thank you
View 1 Replies
View Related
Feb 19, 2008
I ran in to a strange problem in Report Builder. I drop a few fields from my Report Model on a simple talbe report. Some of them are straight forwards attributes and some of them are expressions.
Now in the table layout of a report I have say a attribute named as "Account Number". This attribute is actually a field from a table. I change the column header to a two line column header with €œAccount€? on one line and €œNumber€? on second line. No problem, piece of cake.
Now tried the same thing on an expression type attribute, say "Principal Balance". When I split the column header on two line I get this following error.
==============================================================
Semantic query compilation failed: e MeasureNotFound One of the SubtotalMeasures.MeasureName properties of the SemanticQuery refers to the Measure Expression 'Principal
Balance', which does not exist. (SemanticQuery '').
----------------------------
An error has occurred during report processing.
==============================================================
If you read the error you will realize that the query is trying to find an expression named "Principal" (newline char) "Balance" and it fails.
This expression is nothing but a sum aggregation on a field in the underlying table. If I put the "Principal Balance" back on one line report runs like a charm. Also note that exact same report in Designer no issues at all.
FYI: I am in SQL Server 2005 with SP2. (tried it both on Standard and Enterprise versions.)
View 5 Replies
View Related
Apr 18, 2008
Nobody answered my previous question. So, I am putting it this way.
How to write an IF condition in Expression builder. I want to do something like this.
IF (variable1='ABC' then
value = '.......'
else variable1 ='DEF' then
value = '.......'
else variable1='GHI' then
value = '.......'
END
Thank you.
View 3 Replies
View Related
Aug 9, 2007
Hi all of you,
When you're making expressions with your own variables and system variables and so on. How could I see the contents for each variable?
When you have an expression like this:
"Cargas de Hacienda. " + SUBSTRING( @[System:ackageName], 7,20) + " "
You can perfectly click above "Evaluated value" and see the resutl but I mean, individually over each variable on the variables listview is not possible.
Thanks for any input or advice,
Enric
View 5 Replies
View Related
Jun 6, 2007
If you look at the General Properties for a report in Report Manager, there are 4 properties listed at the top:
Modified Date:
2/26/2007 6:37 PM
Modified By:
DOMAINusername1
Creation Date:
2/14/2006 5:19 PM
Created By:
DOMAINusername2
What I'm looking for is if there's a way to retrieve these properties via an expression from a textbox within a report. For instance, there's already an expression that is:
=Globals!ReportName
I know it won't be that easy for the CreatedBy, CreationDate, ModifiedBy, and ModifiedDate properties, but I thought I'd ask if there was a workaround or hack.
We're wanting to put that info into the footer of all our reports. (Actually this is for Report Builder reports, but you can still use expressions in Report Builder reports, so it should apply as it would to normal reports.)
View 1 Replies
View Related
Oct 10, 2007
I can't figure this one out. I don't have enough knowledge of the string functions I guess.
I need to pull a value out of a variable I setup in a for each loop. The value is the filename/path of each source file being processed. Let's say the variable that has the source file path is called VAR1.
One sort of off topic thing I've noticed is when watch the variable in bebug mode and I look at the value of VAR1 it has double back slashes. Here's an example of the value of VAR1:
"\\L3KRZR6.na.xerox.net\C$\Documents and Settings\ca051731\Desktop\Project4\DPT_20070926.ver"
How come the back slashes have been doubled? And do I need to account for that when I start parsing the string value?
Anyway, I need to grab part of the filename from VAR1 and I need the value populated at the start of the for each loop container - ideally when I capture VAR1 in the for each container. I'll be using the string in drop table, create table and create index statements before the actual Data Flow task within the overall package
In the above example I need to grab the characters before the underscore and after the last \. So I'd need the string "DPT" captured in this example.
The actual string could be 1 to 3 characters long, even though this example has it as 3 long.
Underscores could exist anywhere in the actual UNC path once this package is moved to our actual system environments so I can't key off of the underscore.
Because I can't count on the string being a fixed lenght I can't just use a positional string function and grab specific text starting/ending at specific points.
Is there a way to use the various string functions in the expression builder to grab the text between the right most underscore and the right most back slashes or something like that? Ideally I'd like to setup a new expression based packed scope variable called VAR2 and build it using string functions applied to VAR1.
View 1 Replies
View Related
Oct 25, 2007
Hello,
I am attempting to create an expression for a package variable in a SQL Server Integration Services project, but the elipse button in the expression property for the variable does not appear (Visual Studio 2005). I've tried two different screen resolutions, no luck.
Why would the elipse not be there? Is there a keystroke I can use to open the exrpression builder?
Thank you for your help!
cdun2
View 3 Replies
View Related
Dec 22, 2006
Anyone know the reaon the Left function was left out of the list of string functions in the Expression Builder?
Danno
View 6 Replies
View Related
Jul 26, 2007
This works:
len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate())
This doesn't:
"dataware/"+ len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate())
I tried a couple of different things at this point, where am I missing the cast? Before len? Cast to what?
View 4 Replies
View Related
Mar 22, 2008
I would like to add additional string functions and other types of functions to the expression builder in SQL Server Integration Services. Right now the list of functions is relatively limited to such things as FINDSTRING, RIGHT, LEN, etc.
Is this possible?
Matt
View 6 Replies
View Related
Dec 3, 2007
I have a co-worker who applied build 3161 to SQL Server 2005. The original problem was that the print preview was showing up as black.This is a documented issue with Microsoft here:
http://support.microsoft.com/kb/935436
Now, when she goes into expression builder, the foreground AND background color for what you type in there appears to be white... so everything shows up as invisible. You can highlight the text you type and it shows up but otherwise it is obviously invisible.
Anyone know how to fix this?
View 1 Replies
View Related
Dec 15, 2006
hi
I have installed SQL Server Reporting Service on window xp. everything working fine except one thing.
I have installed sql server with my a/c. my a/c have admin rights. when i giving http://dineshpatel/reports it is displaying page but Report Builder and other option are not displaying. I hope i don't have admin rights.
I have checked with local administrator login also but same problem.
what additional setting are require for admin rights?
Dinesh Patel
View 10 Replies
View Related
May 7, 2012
Having trouble casting GETDATE() to a string value (DT_STR) using expression builder? I want to concatenate the current date to some text used in a send mail task.
View 5 Replies
View Related
Mar 13, 2007
Hi,
I have implemented a custom source component that can be used as the data source in the Data Flow task.
I have also created a custom UI for this component by using the IDtsComponentUI .
But my component does not have the capability of setting the custom properties via the DTS Variables using the Expression Builder.
I have looked around for samples on how to do this, but I can only find samples of how to do this for custom Control Tasks, i.e. IDtsTaskUI.
My question is, How can implement the Expression Builder in my custom Source component + custom Source UI. Or do you know of any samples which I can look at.
Thank you,
Jameel.
View 1 Replies
View Related
Oct 7, 2015
How do you write an expression in report builder to only display the results that start with a certain string value..eg. Project Number - CUP1501, DPR1502, ENG1507 etc. These values will all get returned but I only want the Project Number that has CUP and DPR in it?
View 2 Replies
View Related
Apr 17, 2007
Hi,
I have a dts package that currently uses a dynamic properties task to set the values of global variables. Each variable is based on the value of a query to the database.
I am in the process of migrating this dts package to SSIS but cannot find an equivalent function. I have looked at property expressions but cannot get this working the same way.
Any help would be appreciated.
Thanks
Lyn
View 11 Replies
View Related
Aug 24, 2010
How to use variables in Connection Manager's properties? I see some replies through Configuration Package. But what if, it is still in development stage? I mean, can I use the Variable tab and create some variables like
User::DBUserNameSource, User::DBPasswordSource,
User::DBuserNameDestination, User::DBPasswordDestination,
Then put them in Password and UserName property of Connection Manager? If this is possible, how and how can I set the values of those variables I mentioned when I am going to deploy the package in the Production?
View 26 Replies
View Related
Aug 14, 2007
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
View 6 Replies
View Related
Jun 27, 2006
Hi
I have problem in configuring my web project on Window server 2000. I have created the web application using Visual Studio 2005 tools. I have configured Window server 2000 with the .Net Framework Version 2.0. I can access the page which doesn't have database access.
The connection string in the web config file is giving me the problem that connection string can't be found. Does any one have any idea?
My database connection string looks like this.
<connectionStrings>
<add name="etzAusBldConnectionString" connectionString="Data Source=CASCADE;Initial Catalog=etzAusBldBaseData1;User ID=sa1;Password=****"
providerName="System.Data.SqlClient" />
</connectionStrings>
Moe
View 1 Replies
View Related
Aug 30, 2006
Hi,
I am new to SQL Server and I am trying to install SQL Server 2005 Express or Stnadard Edition on a computer runing XP pro on a XP's workgroup network using a router. Can I access the the database from another computer running XP pro on the same network? If I can, what setting do I need when installing SQL 2005 in order to do this?
Any help is appricated.
View 1 Replies
View Related
Nov 11, 2015
Every day between 1AM to 2AM client disconnect happens and it's not too long also...it's about 30secs...nothing is recorded from SQL side and application server side and then we checked the network...there is no disconnect from network side too. How can i find the cause for disconnect.Â
View 4 Replies
View Related
Feb 24, 2005
Does anyone know what the different values are (and what they mean) on the "Database Locking Mode" column off of the advanced button on a microsoft access connection within a DTS package on SQL server 7.0 ?
It's a 4 byte signed integer type with a default value of zero
Cheers,
Duncan
View 1 Replies
View Related
