Hello friends'
I am new to integration services,first time I'm using XML Task Control in SSIS,
I am stuck with problem saying....
"[XML Task] Error: An error occurred with the following error message: "Expression must evaluate to a node-set.".
I am trying to perform XPath operation type and accumulating values in a variable.
I have no clule about error if u can help to come around the problem.
Hi,
I have a table which has expression as a column and the data looks similar to the following .
ID (int) Expression (nvarchar data type)
1 8*(1/2)-6
2 278*(1/4)-2
3 81*(3/5) +4
I now have the expression as an nvarchar. (Ex: 8*(1/2)-6)
Now I need to evaluate this expression and output the result (which is -2 in above Ex) .Can someone give me idea on how do I evaluate the expression which is available as a string. I need to write a procedure for this.
I am unable to cast/convert nvarchar to float/int
Any sample code would be greatly appreciated.
This a very urgent requirement for me.Please get back
Thanks
Swapna
I would like to evaluate expressions from within my execute function in a custom task. I saw this post from about 8 months ago detailing how it would be possible:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=117564&SiteID=1
Is there an updated response (or more detailed example) to the question? Am I really risking a lot by using this undocumented feature?
Thanks!
Is there a way to evaluate an expression (like the derived column component) in a custom component? If so where should I look first? Is there an example?
An extremely simple sample is to put in an expression and evaluate one column and then add that to another column to create a new column. i.e. newcolumn = column1 + column2.
I realize that the derived column allows me to do this but I'm trying to figure out if it is possible to do this in a custom component without having to build my own expression evaluator.
Thanks!
-Thames
Hi all,
I'm wondering if anyone's accomplished this before - I've been unable to find a whiff of info on how to do this so far.
I'm creating a custom component that I'd like to give a "Derived Column" type of ability to. By that, I mean I'd like to populate a property of my component with an expression (including references to input columns, package variables and functions) and be able to evaluate it at runtime - per row processed by the component.
I would also appreciate any information as to how to provide the interface to allow the user to build such an expression as well - is there a UI function in SSIS I can call to pop up the "expression builder"?
Thanks!
Hi!
On a Reporting Services 2005 report, I have a table with a single group row and a single detail row (and about 5 columns). The details are set up to toggle visibility based on a textbox in the group header called "txt_Order_Number". The order line item details are set with their initial visibility set as hidden. So, when you click on the Plus sign next to the Order Number, the detail rows display below.
However, what I'd like to do is change the background color of the toggled group header row when it is toggled and the details are displayed. Here's what's going on . . .
The row with the Order header information has every textbox set in bold. That's good. It makes a subtle distinction between the order detail rows and the order header row, so when you're looking at the dollar amounts on each line item and trying to add them up in your head to see whether they correctly add up to the dollar amount in the order header row, you can at least more easily see the total amount. However, it makes the whole overall report look funny because all of the order header information is in bold. What I really want to do is change the background color of the order header row to light gray or some other subtle color and change the font to Bold when the details are toggled to visible in order to distinguish that row as the header row.
In order to do this, I think I somehow need to get to a certain element of the Reporting Services object model called "Textbox.ToggleState". If I can get to that piece of the puzzle in an expression or even in code within the report, that should tell me the state of the textbox's toggle switch and I should be able to use that information to conditionally set the color of the textbox.(or every textbox in the row probably.) Here's all I could find out about this on TechNet
http://technet.microsoft.com/en-us/library/microsoft.reportingservices.reportrendering.textbox.togglestate.aspx
I just can't figure out how to get to that piece of information in a Reporting Services expression or in the report's code block.
Any suggestions? I'd appreciate a response even if it's "You can't do that." Actually, the best response someone might be able to provide might be a link to a sample showing how to get to any of the textbox properties like that.
Thanks!
Karen
I'm trying to put conditional formatting on a field, that behaves as follows:
The data in the field is varchar, and sample data is either:
NULL
3.0 :0
11.7 :1 (these are ratios of a sort)
I want to evaluate the first 3 characters of the string as numbers.
Example:
Mid(fieldvalue,1,3) = "3.0" or "11."
Any data that is greater than 1.99, I want to make the background dark red, anything else including nulls, zebra formatting. I have the following expression built so far and it appears to work, except when the value is null. If the value is null, it leaves the background color white.
This is the warning: [rsRuntimeErrorInExpression] The BackgroundColor expression for the text box "Asthma" contains an error: Input string was not in a correct format.
=iif(
isnothing(Fields!Asthma.Value)
,(IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))
,(iif(mid(Fields!Asthma.Value,1,3)>1.99
,"DarkRed"
,IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))))
My logic is, if the field is null, zebra format, if mid of the value is > 1.99, dark red, everything else zebra formatting. As I said, this seems to work except for nulls.
I am trying to add a filter and sorting to a matrix, within the matrix I have a column which works out the percentage and is as follows
=(Sum(Fields!Sum_Distinct_JobBilledExVAT.Value) + Lookup(Fields!ClientId.Value, Fields!JobClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfJobClientsFees") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfClientsFeesID")) / (Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_MatchedToOtherPost.Value, "ProfClientsMatch") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfClientsCC") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value,
[Code] ....
I tried enclosing the query in Abs( ), which I have done in the past, although in the past I wasn't using Lookup functions so I don't know if that is the reason it won't work.
I have a package (PackageA) with an Execute Package Task that execs PackageB. When I run PackageA I get this error on the Execute Package Task :
Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
PackageB has 'EncryptSensitiveWithUserKey' ProtectionLevel. I'm providing passwords in the dtsConfig so I'm guessing I should change it to 'DontSaveSensitive'?
Interestingly, PackageA also has 'EncryptSensitiveWithUserKey' ProtectionLevel, but I don't get an error about PackageA, just on the task that runs PackageB.
(SP1 is installed).
Hi,
I'm trying to set the DestinationDatabaseFiles property programmatically through an expression. I keep getting the error:
SSIS package "Package1.dtsx" starting.
SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Transfer Database Task' has been hit
Error: 0xC002929C at Transfer Database Task, Transfer Database Task: The transfer file information "","","" is missing the filename.
Error: 0xC002929D at Transfer Database Task, Transfer Database Task: The transfer file information "","","" is missing the folder part.
Error: 0xC0024107 at Transfer Database Task: There were errors during task validation.
Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package1.dtsx" finished: Failure.
I'm setting the property to a variable, copyDestFiles, which I've tried numerous ways to set, the latest being in a script.
Dts.Variables("User::copyDestFiles").Value = _
"""" + stageDataName + """,""D:Program FilesMicrosoft SQL ServerData"","""";""" + _
stageLogName + """ ,""D:Program FilesMicrosoft SQL ServerData"","""""
When I view the variable at runtime it looks like this: "Prod2.mdf","D:\Program Files\Microsoft SQL Server\Data","";"Prod_log2.ldf" ,"D:\Program Files\Microsoft SQL Server\Data",""
How does one set this property using an expression?
Thanks, Nathan Vollmer
I am trying to use a XML task in SSIS to get a session ID value from this XML:
<?xml version="1.0" encoding="utf-16"?>
<AuthenticationResult xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<authenticated xmlns="[link]">true</authenticated>
<sessionId xmlns="[link]">0000-0000000-00000000000-000000</sessionId>
[Code] ....
However I cant get the task to return the Session ID
Task setup:
Operation Type: XPATH
SourceType: File connection
Source: testfile.xml
SaveOperationResult: True
DestinationType: Variable
Destination: User::SessionID
OverwriteDestination: True
SecondOperandType: Direct input
SecondOperand: //sessionId
Namespaces: (Collection)
PutResultsInOneNode: False
XPathOperation: Values
No namespaces are specified.
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
Hi,
I am getting an error when i am trying to insert some value to the sql ce database
"QP is missing and it is required to evaluate default expressions. Ensure that sqlceqp30.dll is in the same directory as the storage engine (sqlcese30.dll)"
Please help
Thanks
Nebu
Hi
Does anyone know if there is a Tutorial That I can Use, that would explain me how to use an Expression in a SQL Task, as I need to create a Table by using a T-SQL Script and pass the Table name into the Variable and cannot come right using Variables.
I have been given advice that an expression would solve my problem.
Please Help
Thanks In advance
I have not used log shipping before and find myself in a position where I need to reboot the secondary node and then the primary node and I don't actually need to failover.
Is there anything I need to be aware of. When rebooting the secondary node I assume the transactions will be held in the primary nodes log till the secondary comes back and just carry on once back up?
When rebooting the primary node nothing needs to be done and the log shipping will just start again once it has come back?
I read these instructions:
http://msdn2.microsoft.com/en-us/library/ms191545(SQL.90).aspx
But I'm not sure if I have to install SQL Server first on node 2, then add it to the cluster. Or does adding it to the cluster also install the software?
Thanks
I have a package with variables which are set to default values, in this instance table names.
I then use a script task to change these variable values, these change fine and I have looked in the watch window whilst debugging to ensure that the values have changed correctly, and they have.
My Script Task then pipes to a Execute SQL Task, where I am using Expressions to set the SqlStatementSource. The Expressions statement uses the variables I change in my script task to form a SQL query.
However, at run-time the Expression appears to be fixed with the default variable values, it does not change even though the variable values have changed. Below is an extract from my expression for the SqlStatementSource.
i.e
Code Block
"USE [DBNAme]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].["+ @[User::StagingEventLossTable] +"](
[PolicyId] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
<snip>
...
Is an Expression value fixed from the moment the package is run and is therefore not dynamic during run-time?
If this is the case, how can I get around this? As I want to be able to change this SQL Statement on the fly at run-time.
Thanks
I'm contemplating running two availability groups on a two node WSFC. The WSFC is setup with a file share witness (i.e. no shared storage). Can I safely run 1 AG on one primary node, and the other AG on the other node (as primary). Each AG would have replicas on the passive node. This would effectively allow both servers to be in use at the same time. In a failover event, I understand that both workloads would transfer to a single server - so the box needs to be sized appropriately.
View 1 Replies View RelatedWe are in the process of building a 3 node SQL Server Cluster (Server 2012/ SQL Server 2012), and we have configured the quorum so that all 3 nodes have a vote (no file share witness as we already have an odd number of nodes).
As I understand it, this should allow the cluster to run as long as 2 of the nodes remain online.
However, the validation report states that 2 node failures would be acceptable and, when we tested this by powering off two of the nodes, the cluster did indeed continue to run on a single node.
Hi
I am trying to develop a data flow task in C#. I need to add an expression tab in the task where i can write expressions on the input columns and map it to outputs. Please let me know how to go about it. I am new to SSIS coding so dnt have much idea abt it.
Thanks in advance,
Vipul
Hi
I am trying to configure the SelectedDatabases property of the Reorganize Index Task using an expression.
The Expressions property of the task provides the ability to configure the SelectedDatabases property of the task using an expression. The properties pane shows that the type of the SelectedDatabases property should be a "(Collection)" (which is edited using the 'Object Collection Editor').
How do I create an expression to configure the SelectedDatabases property? Can I build the collection in text? Or do I need to provide a variable of type System.Object that contains a collection type (and if so exactly what type should it contain)?
TIA . . . Ed
I configure Windows 2003 R2 and SQL 2005 two nodes Cluster. When I move cluster resource from one node to anther node it takes around 30 seconds to become online. So in that time if any query is running it stops responding.
So please suggest in this regard
Hi all,
I have a task as creating a SSIS package to transfer all the 10 tables from a database to another. And I have used the Transfer SQL Objects Task to select a table list and let the component do the transfering.
But my problem is that the source database have bad data and some null data, so I have to find a way to transfer only 'NOT BAD' data, and remove the bad data (lost relationship) and change NULL to "N/A".
And I can't find a way to do this. Is there anyone have experiences with this problem?
Can anyone help me?
I will very appreciate you help?
Hi,
I created a Custom Task which it has a Property called ConfigFilePath. I'm overriding the Validate() method from Task. I want to throw an error if my property ConfigFilePath is empty and if the expression for this property is empty. So far, I can check if the property is empty but I don't see how I can access the Expressions Collection of my Custom Task.
Any thoughts? I'd appreciate your help.
Hi there,
I'm creating an SSIS package that will execute legacy dts packages. The package to be executed is decided at runtime using a sql query task.
Executing a dts package statically works fine, but when I try to set the details of the dts to be run via expressions, I get the error below.
To make it dynamic, I created a variable of type string, and put the package name in here. I also have a string variable for the packageid. Then I set up an expression on the Execute DTS 2000 Package Task that sets the PackageName & PackageID property to this variable.
The PackageId is a string variable I've retrieved using:
select top 1 name,
cast(id AS varchar(50)) id,
cast(versionid AS varchar(50)) versionid
from sysdtspackages
where name = @PackageName
order by createdate desc
When I use the task to set the package id it works find (by selecting a dts, then changing the name), but when I try to provide the package id I get this message:
Error: 0x0 at Execute DTS 2000 Package Task: System.Runtime.InteropServices.COMException (0x8004040E): Invalid GUID specified.
at DTS.PackageClass.LoadFromSQLServer(String ServerName, String ServerUserName, String ServerPassword, DTSSQLServerStorageFlags Flags, String PackagePassword, String PackageGuid, String PackageVersionGuid, String PackageName, Object& pVarPersistStgOfHost)
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.PackageUtils.LoadDTS8Package(String pkgSrc, String sourceUser, String sourcePwd, Int32 srcType, Boolean bUseTrustedConnection, String packageName, String packagePassword, String packageID, String packageVersionGUID)
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.LoadPackage()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
Task failed: Execute DTS 2000 Package Task
but the method signature specifies String PackageGuid, and it is a string..
any ideas??
i tried casting the variable like so:
(DT_GUID) @[User:TSPkgId], as the versionid is a uniqueidentifier on sysdtspackages.. It didn't like that at all (can't cast from type DT_WSTR to DT_GUID error code 0xC00470C2)
Hello,
I am having a hard time setting the executable path for an Execute Process Task in SSIS. I have a variable that is initialized at package statup which holds the path to an executable in Windows. When I set the property "Executable" Path in an expression, I get a warning that the path for the executable is not set. One workaround was to try and initialize the variable with a bogus path with the hopes that the "correct" value will be written on run-time. NO LUCK. I still get the error and I cannot run the package until I put a static path.
Does anyone have a clue as to what is going on??
Mike
Hai Friends...
I am using sql server 2000. i have one table with columns
empid notnull
empfname not null
empmname null
emplname not null
while i am writing a simple procedure to get the employee middel name(empmname) it is giving the error message like this
Server: Msg 7105, Level 22, State 6, Procedure emp_statistics, Line 11
Page (1:70), slot 18 for text, ntext, or image node does not exist.
Connection Broken
plase help me Urgent
Thanks and Regards
Krishna Mangamuri
I invoke xp_cmdshell proc from inside a stored procedure on a 2-node active/passive SQL 2005 SP2 Standard cluster. Depending on which server the xp_cmdshell gets executed on I need to pass different arguments in the shell command. I thought I could use host_name() function to get the runtime process server, however, I am finding that it's not behaving correctly. In one example I know my active node is server2, but the host_name() function is returning server1. The only thing that I could possible explain this is that the MSDTC cluster group is not always on the same active node as the SQL server group and in the case I am talking about the cluster groups are in this mode (differnet nodes). Does the xp_cmdshell get executed by the SQL active node or the MDTC active node? And what is the best way to find out which server is going to run my xp_cmdshell?
Thanks.
Edit:
Perhaps another by product of this is that if I run select host_name() from the Studio Management query window i get different results depending on which server I am running the Studio Management on. On server1 I get server1 and on server 2 I get server 2, all the while server2 is the active node. I need a different function that will always let me determine the correct server that'll be running the xp_cmdshell...
Edit 2: I guess I could determine the running host inside the command shell itself, but I am curious to see if i can do it (cleaner) from SQL.
I get an error when trying to open an SSIS package from TFS using Visual studio 2014..
This is the error:
error loading package failed to decrypt protected xml node DTS:Password Key not valid for use in specified state you may not be authorized to view this information
This package has been developed by a person who left the company. I think he had admin permissions on the servers.
Hi,
View 39 Replies View RelatedWhen and why does this error message appear..
Error 1 Error loading MasterPackage.dtsx: Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
$exception {"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}
occurs
here is my code
protected void EmailSubmitBtn_Click(object sender, EventArgs e)
{
SqlDataSource NewsletterSqlDataSource = new SqlDataSource();
NewsletterSqlDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["NewsletterConnectionString"].ToString();
//Text version
NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
NewsletterSqlDataSource.InsertCommand = "INSERT INTO NewsLetter (EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress, @IPAddress, @DateTimeStamp)";
//storeprocedure version
//NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
//NewsletterSqlDataSource.InsertCommand = "EmailInsert";
NewsletterSqlDataSource.InsertParameters.Add("EmailAddress", EmailTb.Text);
NewsletterSqlDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString());
NewsletterSqlDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now.ToString());
int rowsAffected = 0;
try
{
rowsAffected = NewsletterSqlDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("NewsletterProblem.aspx");
}
finally
{
NewsletterSqlDataSource = null;
}
if (rowsAffected != 1)
{
Server.Transfer("NewsletterProblem.aspx");
}
else
{
Server.Transfer("NewsletterSuccess.aspx");
}
I have destroyed the cluster in failover cluster manager and then i am trying to remove node from the sql server installation centre.I am facing the cluster node and cluster service verification errors.I am not able to start the cluster service in services as well.
View 4 Replies View Related