IS Package wont work for anyone but me. Why? 1

[GrandMauler]

When I save a copy of the IS Package to the MSSQL Environment, as a package on the IS server, and someone other than me tries to run it, they get the following message:

Unable to cast object of type 'Microsoft.SqlServer.DTS.ObjectExplorerUI.ExecutePackageAction'
to type 'Microsoft.SQLServer.DTS.ObjectExplorerUI.ISimpleAction

I've tried different things, like change the Protection Level to "Rely on Server Storage and roles for access control", but nothing works.

What am I doing wrong?

 

[GrandMauler]

Sorry, but I forgot to mention a few pertinent facts:

The SSIS resides on a x64 Server. I'm having people actually remote into the server and open an IS Connection for that same server.

The Sys Admin himself cannot run the package.

 

[mrdenny]

What part of the package is being run when this error comes up?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[GrandMauler]

Sorry to take so long for the response. I've battling this for a full week now.

To answer your question, all of it. It is a deployed package under package location: SQL Server.


Here's a new twist: After the Sys Admin, myself, and another user tried to execute the package, I, too, was being denied access and getting the same message. The Sys Admin offered that the registration tree was corrupted so we rebooted the server.

After we rebooted the server, I could once again run the IS package directly, but only me.


So I tried another approach. I had noticed that there was another IS package that was exposed via SQL Server Agent Job. In other words, I had created a job that pointed to the IS Package and defined the Job Type as "SQL Server Integration Package". Users could run that package no problem.

But since this other package involved accessing an EXCEL file, and since there are no 64 bit OLE drivers for Integration Services, I had use a command line Job, in other words of Type: Operating System (CmdExec)


Now get this: I tested the command line directly from the command line editor and the dtsx package works beautifully.
But when i put the same exact command on the Job's Command Window and run it, it comes back as being run successful but does absolutely nothing!


Here is the command line command:

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /DTS "\MSDB\SWAT Staging\Tariff Imports" /SERVER GORDON /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

I am totally stumped on this one.

Please help.

 

[GrandMauler]

Here's what I'm currently trying -

I'm try to execute the DTExec command through the SQl command shell since I'm figuring it is basically the same thing as a Server Ageng Job of Type Operating System (CmdExec):

EXEC xp_cmdshell 'C:\"Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /DTS "\MSDB\SWAT Staging\Tariff Imports" /SERVER GORDON  /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'

The result comes back as "Success" but does absolutely nothing... same thing as the Server Agent Job.

 

[mrdenny]

Put some logging into the SSIS package and see if it's actually launching or not.

Try moving the SSIS package from the SQL Server to the file system and see if that helps.

What patch level are you running your SQL Server and SSIS service at?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[GrandMauler]

Here's the log:

Date 9/13/2007 4:02:35 PM
Log Job History (Tariff Imports)

Step ID 1
Server GORDON
Job Name Tariff Imports
Step Name Update it
Duration 00:00:18
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 4:02:36 PM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 4:02:36 PM
Finished: 4:02:53 PM
Elapsed: 16.719 seconds

I tried Moving the package from SQL to File system and changed the command line to the following:

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /f "E:\SSIS\SWAT_MANAGER\SWAT_Tariff_Import\SWAT_Tariff_import\bin\TARIFF RATES UPDATE.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

But before I ran this, I first created a proxy account complete with my credetials. The job did run with a return of "success" but did absolutely nothing.
I had another user run this from their computer and it came back with the same result.


I then had the user, under my proxy account run the package directly by remoting into the server and launching Intergration Services,
only to have it error with the orignal error message of this thread.

The sys admin did find something interesting: If sQL 2k5 is completely installed on the client computer, the error message does not appear.
So I will install SQL 2k5 on the user's client computer and see what happens.

By the way, to answer your patch level question: Service Pack 2.

 

[GrandMauler]

Ok, just wanted to appraise anyone reading this of my progress. I found a thread that is very similar to my problem and I'm about to investigate:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2136850&SiteID=1

Cheers

 

[GrandMauler]

Ok, now I understand why I am getting a "Success" return on the Agent Job when nothing actually happens.

I ran the dtsx file directly from a Query window:

EXEC xp_CmdShell
'"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /f "E:\SSIS\SWAT_MANAGER\SWAT_Tariff_Import\SWAT_Tariff_import\bin\TARIFF RATES UPDATE.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'

This is the output I got:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:40:54 AM
Error: 2007-09-14 09:40:55.70
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "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.
End Error
Error: 2007-09-14 09:40:59.18
Code: 0xC0016016
Source:
Description: 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 t
hat the correct key is available.
End Error
Error: 2007-09-14 09:40:59.18
Code: 0xC0016016
Source:
Description: 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 t
hat the correct key is available.
End Error
Error: 2007-09-14 09:40:59.20
Code: 0xC0016016
Source:
Description: 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 t
hat the correct key is available.
End Error
Error: 2007-09-14 09:40:59.20
Code: 0xC0016016
Source:
Description: 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 t
hat the correct key is available.
End Error
Error: 2007-09-14 09:40:59.20
Code: 0xC0016016
Source:
Description: 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 t
hat the correct key is available.
End Error
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 9:40:54 AM
Finished: 9:41:04 AM
Elapsed: 9.734 seconds
NULL

Basically, it's not decrypting the secret info. So the only solution I can think of is to create a config file and have the package point to it. I'll need to look up how to do that, expecially since I have the main dtsx pacakge calling other dtsx packages.


If you guys have any links handy on this, please pass it on.

I'll let you know if this works.

Thanks.

 

[mrdenny]

Good information to have. That's stupid that if it fails to decrypt the password information the package still succeeds. You would think that, that would cause a failure.

Try reducing the secerity level of the package.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[GrandMauler]

When I compile the pkg, I set the protection level to "DontSaveSensitive", hence the need the config file.

I tried pointing the pkg to a config file, using the /CONFIG switch of the DTExec command (ran from the SQL Query window), and it works... sort of.

It *does* successfully access the databases as well as the Excel file

However, now the pkg has a problem with the ForEachLoop container which loops through files on a folder located on a remoted drive.

I would like to post that problem on a separate thread so as to keep things simple, should this info prove important to someone else. From my research so far, it appears others are having this thread's problem as well.

 

[TeamGD]

I’ve had a job scheduled in SQL Server Agent that has been working for months. Now a number of the steps, which are IS packages, are not working and the error message is exactly the same as discussed in this thread. The packages have not changed. The Network Administrator assures me nothing has changed. But of course something must of changed. Can anyone suggest what could have changed to cause a scheduled job, that had been working for months, to then get the same error message as discussed above.

All packages are saved to the file system. The job in SQL Server agent calls these packages direct.

cheers

 

[SQLBill]

THis is my understanding...Microsoft, in their infinite wisdom, has chosen to force connection and password strings to be encrypted. This is based on your login id, password, and machine information. So, when you change machines or someone else tries to run the package, it will fail. Another issue is that SQL Server 2005 can be set to use and enforce the Windows security policy. So if you have been using an 8 character password and the System Admins change the policy to be 10 characters, your password will fail. That includes passwords within SSIS packages. Right click on the database, choose Properties, then go to Security. I think that is where you choose to enforce the password policy.

I don't know that either of those are what is causing your issue, but it's something to check into.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[TeamGD]

Thanks Bill ... that's great

 

[TeamGD]

As footnote … I resolved my issue by deploying all packages to SQL Server (previously they were on the file system) and selecting “Rely on server storage for encryption” in the Package Installation Wizard. I do not know what caused the issue in the first place ... I'm just glad to move on from this problem!

Greg