Failed to decrypt XML node "DTS:Password"

Hi!
I made an SSIS package and it runs fine on my workstation, but it fails when I run it on the SQL Server.
I'm using an ODBC connection and when I run the package, I get the error message:
"Login failed. Check for valid user ID, server name or password".
I tried to load the project in Visual Studio on the Server and got the error message above:
Failed to decrypt XML node "DTS:Password"
How can I set up the permissions for decrypting the password?

Thanks for any help!

The problem is that you have the ProtectionLevel set to "EncryptSensitiveWithUserKey". This means the package can be executed only by the user that created the package and on the machine that it was created. (unless you use a domain account and use machines in the same domain)

If you import the package on your server I recomend to change the ProtectionLevel to "EncryptSensitiveWithPassword" - will ecnrypt sensitive information like passwords - or if you store the package on SqlServer you can use "Rely on server storageand rolse for access control" - this means that the package will be stored unencrypted on the SQL Server.

The package can be stored unencrypted ONLY in the SQL Server.

HTH.
Ovidiu Burlacu

|||Thanks for the explanation!
I tried to use the last option"Rely on server storageand rolse for access control", however it causes a lot of error messages in the Business Intelligence Studio and I can not create a build with a deployment manifest. The help sais, this option is not accessible from Visual Studio, but I can not figure out, how to use it.
Can you tell me, how I can use it?
|||

Are you saving the package on a SQL Server or to a file? You can use ServerStorage ONLY if you save the package on SQL Server

To save a package from BIDS directly to SQL Server use File-> "Save Copy of <package_name>.dtsx As..." and in the dialogs that appears you can specify a SQL Server

If you want to create a deployment manifest encrypt with a passord and when you run the Deployment wizard and specify the destination to be a SQL Server the you can choose the "Rely on server storage and role for access control".

HTH,
Ovidiu

|||Thanks alot!

Failed to decrypt protected XML node

Failed to decrypt protected XML node ... blah blah.

Looks familiar, right?

Package Protection mode is : EncryptSensitiveWithPassword

Package runs fine from within BIDS. Close package. Open package - asks for password. So far so good.

When running from command prompt with:

dtexec /File "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\MyPackage.dtsx" /De mypassword

Get that cryptography error.

Doesn't happen for other packages... what can I be doing different/wrong?

Thanks,
NiteshDoes your password contains any of the following:
a) space characters - use the format /De "your_password"
b) punctuation characters - use the format /De "your_password"
c) " - double any "
d) ; - use the format /De "\"your_password\""

Hope this helps

Regards,
Ovidiu Burlacu|||

Ovidiu Burlacu wrote:

Does your password contains any of the following:
a) space characters - use the format /De "your_password"
b) punctuation characters - use the format /De "your_password"
c) " - double any "
d) ; - use the format /De "\"your_password\""

Hope this helps

Regards,
Ovidiu Burlacu

Nopes.
Password is same as that for other packages, all lowercase alphabets, 8 characters.

thanks,
Nitesh|||

Since your protection is only EncryptSensitiveWithPassword then only sensitive information will be encrypted, and my guess is that this package has been encrypted with a different password (mistyping).

Can you try to open package in BIDS and then go to View -> Error List and see if the same error has been posted?

I'm guessing that the same error happens in the designer, but we should not fail open the package just because we cannot retrieve the Password in a ConnectionString, so instead we post an error.

HTH,
Ovidiu Burlacu

failed to connect to the server

When I have a subscription built for a report it runs at the time expected, but the report send via e-mail fails. I can run the report from a client workstation just fine. I can also run it from the admin tool fine too, it just will not run as part of a subscription. The full context of the error message is as follows:

Failure sending mail: The transport failed to connect to the server

This is not my first Reporting services report, but I am trying to replace all Crystal reports with Reporting services reports and this functionality is greatly needed.

Thanks! - Eric-

The error is telling you the RS server cannot connect to the Mail server. Make sure that the unattended execution account is setup and is a user that has access to the mail server.

Here is a link to help you.

http://msdn2.microsoft.com/en-us/library/ms155851.aspx

Failed integrity check

The accounting system we support runs on SQL 2000. We have setup a SQL Job that checks the integrity of a each accounting database each evening. After running successufully for a few weeks the job started retuning a failed message. I checked the job log and the reason for the failure was because one of the databases couldn't be set to single user mode. When I first saw this I figured OK someone forgot to logout before leaving for the night. When discussing this with the accounting staff, they swear this couldn't be the case with this particular database since only 1 person accesses it and then only rarley, and he says he hasn't been in for days. The amount of activity in this database is minimal
The server was cycled and eveything ran fine for about 3 weeks. Then the same thing happned again over a span of a few nights consecutive. The are no overnight business processes. There are database backups that are scheduled in the evening, including one for this particualr database. Nothing is showing in the active processes during the day to this database, i.e. no run away processes. Any ideas on what is causing the SQL Server to think there is a open connection to this database?
Thanks much for any ideas!If these jobs are set up through maintenance plans, remove
the option for Attempt to repair any minor problems.
You really don't want this to run automatically anyway. If
you have problems with DBCCs, you should be investigating
the cause first before taking any action.
-Sue
On Sat, 18 Oct 2003 12:41:04 -0700, "DL"
<anonymous@.discussions.microsoft.com> wrote:
>The accounting system we support runs on SQL 2000. We have setup a SQL Job that checks the integrity of a each accounting database each evening. After running successufully for a few weeks the job started retuning a failed message. I checked the job log and the reason for the failure was because one of the databases couldn't be set to single user mode. When I first saw this I figured OK someone forgot to logout before leaving for the night. When discussing this with the accounting staff, they swear this couldn't be the case with this particular database since only 1 person accesses it and then only rarley, and he says he hasn't been in for days. The amount of activity in this database is minimal
>The server was cycled and eveything ran fine for about 3 weeks. Then the same thing happned again over a span of a few nights consecutive. The are no overnight business processes. There are database backups that are scheduled in the evening, including one for this particualr database. Nothing is showing in the active processes during the day to this database, i.e. no run away processes. Any ideas on what is causing the SQL Server to think there is a open connection to this database?
>Thanks much for any ideas!

Fail Execute Process Task based on batch file ERRORLEVEL?

I am new to this, but have scoured the web and not found an answer to my question...

I have an execute process task that runs a simple batch file. When this batch file completes with an ERRORLEVEL greater than 0, I would like the task to fail. I thought this simply meant setting the "FailTaskIfReturnCodeIsNotSuccessValue" property to true, and setting the SuccessValue to 0. However, this does not appear to work.

Even with a simple batch file forcing the errorcode to 1 as follows, the task still completes "successfully".

SET ERRORLEVEL = 1

Any ideas? Thanks!

have you tried using the script task instead?|||

I am not clear on how I would use the script task to accomplish this, though would welcome suggestions. Also, just for completeness sake, can anyone tell me why a batch file ERRORLEVEL has no effect on the execute process task?

Thanks!

|||

David Kreps wrote:

I am not clear on how I would use the script task to accomplish this, though would welcome suggestions.

you would need to use the System.Diagnostics.Process class within the script task.

|||

The option to fail the task if program result code is not the expected one works with batch file.

The problem is that the command

SET ERRORLEVEL = 1

creates an environment variable ERRORLEVEL, it does not actually affect the batch script error code.

D:>SET ERRORLEVEL = 1
D:>if ERRORLEVEL 1 echo 1
D:>COLOR 00
D:>if ERRORLEVEL 1 echo 1
1

(COLOR 00 is common way to actually set the error level is batch scripts).

|||Perfect. Thank you.|||

Turns out the above did not actually work. While it correctly set the ERRORLEVEL to 1, the package still registered a success. That said, I found the solution: the batch file EXIT command:

EXIT [ERRORLEVEL]

eg: EXIT 1

As the syntax implies, this exits a batch file (or, more accurately, the instance of command.exe it is running in), returning the ERRORLEVEL specified. The Execute Process Task correctly recognizes this ERRORLEVEL as the return code, and fails appropriately.

Extremely slow runtime running SSRS on a cube

Hi everyone:

I am developing an SSRS report over a cube. When I drag and drop fields, it works fine. it runs in a few minutes. I am selectinng only from a single day - about 10,000 records. However, when I add some calculated fields it takes much longer. It's been running for 7 hours. The calculated fields fields are pretty simple. Some are selection of one field over another depending upon the value of a 3rd field. One is two fields multiplied together. One is a constant times a field. Something's obviously wrong here. Anybody seen this or have a solution?

Barry

If you use the MDX query designer (the GUI) in SSRS, I believe it puts the NON EMPTY keyword on both axes. So as soon as you put calculated measures into the query, it now has to do a non-empty against the calculation. This is MUCH more expensive unless you've properly defined a NON_EMPTY_BEHAVIOR.

Option 1: Do a search for NON_EMPTY_BEHAVIOR and add that to all your calculated measures.

Option 2: Flip over to the advanced tab where you can manually control the MDX query and start using the NonEmpty function instead of the NON EMPTY keyword. If you use the NonEmpty function, then you can specify which (physical) measure it should check.

That make sense?

|||

Thanks so much! That worked like a charm.

I am new to MDX, been using Oracle and am pretty helpless without the query builder.

|||

Well, it ran pretty fast, but my calculated field disappears when I switch to design mode. Here's what the qury looks like:

WITH MEMBER [Measures].[Fill Price]

AS 'IIF( [Dim Trade Info].[Hold Type]="CASH", [Dim TradeId].[Spot 3 Month], [Dim TradeId].[Price All In] )'

SELECT NONEMPTY ( { [Measures].[Slippage Tick Open Price], [Measures].[Slippage Open Price], [Measures].[Slippage G1 Bank], [Measures].[Quantity], [Measures].[Slippage Contract Open Price], [Measures].[Fill Price], [Measures].[Slippage Contract TWAP], [Measures].[Slippage Tick TWAP], [Measures].[Slippage TWAP], [Measures].[Slippage G1 Model] } )

ON COLUMNS, NONEMPTY ( { ([Fill Date].[Full Date].[Full Date].ALLMEMBERS * [Dim Team].[Team].[Team].ALLMEMBERS * [Dim Trader].[Trader Name].[Trader Name].ALLMEMBERS * [Dim Trade Info].[Hold Type].[Hold Type].ALLMEMBERS * [Dim Model].[Model Name].[Model Name].ALLMEMBERS * [Dim Market].[Report Name].[Report Name].ALLMEMBERS * [Dim TradeId].[Direction].[Direction].ALLMEMBERS * [Dim TradeId].[Spot 3 Month].[Spot 3 Month].ALLMEMBERS * [Dim TradeId].[Price All In].[Price All In].ALLMEMBERS * [Dim TradeId].[Open Price].[Open Price].ALLMEMBERS * [Dim TradeId].[TWAP].[TWAP].ALLMEMBERS ) } )

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Fill Date].[Full Date].&[2007-08-08T00:00:00] : [Fill Date].[Full Date].&[2007-08-09T00:00:00] ) ON COLUMNS FROM [Trade]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The part referenced by 'IIF( [Dim Trade Info].[Hold Type]="CASH", [Dim TradeId].[Spot 3 Month], [Dim TradeId].[Price All In] )' Is what I want to select. It disappeared, though.

Barry

Extremely slow runtime running SSRS on a cube

Hi everyone:

I am developing an SSRS report over a cube. When I drag and drop fields, it works fine. it runs in a few minutes. I am selectinng only from a single day - about 10,000 records. However, when I add some calculated fields it takes much longer. It's been running for 7 hours. The calculated fields fields are pretty simple. Some are selection of one field over another depending upon the value of a 3rd field. One is two fields multiplied together. One is a constant times a field. Something's obviously wrong here. Anybody seen this or have a solution?

Barry

If you use the MDX query designer (the GUI) in SSRS, I believe it puts the NON EMPTY keyword on both axes. So as soon as you put calculated measures into the query, it now has to do a non-empty against the calculation. This is MUCH more expensive unless you've properly defined a NON_EMPTY_BEHAVIOR.

Option 1: Do a search for NON_EMPTY_BEHAVIOR and add that to all your calculated measures.

Option 2: Flip over to the advanced tab where you can manually control the MDX query and start using the NonEmpty function instead of the NON EMPTY keyword. If you use the NonEmpty function, then you can specify which (physical) measure it should check.

That make sense?

|||

Thanks so much! That worked like a charm.

I am new to MDX, been using Oracle and am pretty helpless without the query builder.

|||

Well, it ran pretty fast, but my calculated field disappears when I switch to design mode. Here's what the qury looks like:

WITH MEMBER [Measures].[Fill Price]

AS 'IIF( [Dim Trade Info].[Hold Type]="CASH", [Dim TradeId].[Spot 3 Month], [Dim TradeId].[Price All In] )'

SELECT NONEMPTY ( { [Measures].[Slippage Tick Open Price], [Measures].[Slippage Open Price], [Measures].[Slippage G1 Bank], [Measures].[Quantity], [Measures].[Slippage Contract Open Price], [Measures].[Fill Price], [Measures].[Slippage Contract TWAP], [Measures].[Slippage Tick TWAP], [Measures].[Slippage TWAP], [Measures].[Slippage G1 Model] } )

ON COLUMNS, NONEMPTY ( { ([Fill Date].[Full Date].[Full Date].ALLMEMBERS * [Dim Team].[Team].[Team].ALLMEMBERS * [Dim Trader].[Trader Name].[Trader Name].ALLMEMBERS * [Dim Trade Info].[Hold Type].[Hold Type].ALLMEMBERS * [Dim Model].[Model Name].[Model Name].ALLMEMBERS * [Dim Market].[Report Name].[Report Name].ALLMEMBERS * [Dim TradeId].[Direction].[Direction].ALLMEMBERS * [Dim TradeId].[Spot 3 Month].[Spot 3 Month].ALLMEMBERS * [Dim TradeId].[Price All In].[Price All In].ALLMEMBERS * [Dim TradeId].[Open Price].[Open Price].ALLMEMBERS * [Dim TradeId].[TWAP].[TWAP].ALLMEMBERS ) } )

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Fill Date].[Full Date].&[2007-08-08T00:00:00] : [Fill Date].[Full Date].&[2007-08-09T00:00:00] ) ON COLUMNS FROM [Trade]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The part referenced by 'IIF( [Dim Trade Info].[Hold Type]="CASH", [Dim TradeId].[Spot 3 Month], [Dim TradeId].[Price All In] )' Is what I want to select. It disappeared, though.

Barry