Putting the SSIS Package Password in SQL Job Step

Question

Putting the SSIS Package Password in SQL Job Step

Aaron Edwards

SSC Eights!

Points: 834
More actions
November 8, 2007 at 12:51 pm

#118806

I have a File-based SSIS job I created. It's Protection Level is set to EncryptSensitiveWithPassword and I supplied a password. How do I then pass the password from SQL Server Agent so I can get the thing to run?
Thanks.
Aaron

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Welch SSC Veteran Points: 255 More actions · Answer 1

If you are using a SQL Server Integration Services job step type, go to the Configurations tab, and enter it in the Package Password box.

If you are using a CmdExec step and calling DTEXEC, use the /DECRYPT switch.

Aaron Edwards SSC Eights! Points: 834 More actions · Answer 2

Thanks John,

That did it. It was a SQL Server Integration Services job step type.

Now of course I'm getting a new error. This transition to 2005 has been a lot tougher than I thought. The SSIS package behind this step runs fine in BIDS. It was originally a SQL 2000 DTS package that I migrated using the Wizard in BIDS. Here is the error if you want to look at it.

Description: The task "Insert Sales People" cannot run on this edition of Integration Services. It requires a higher level edition. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:17:44 PM Finished: 3:17:46 PM Elapsed: 1.609 seconds. The package execution failed. The step failed.

I don't think this is because SSIS isn't installed, because I have another SSIS package that I'm able to run as part of an Agent job just fine.

Thanks!

Aaron Edwards SSC Eights! Points: 834 More actions · Answer 3

No, I was wrong. It was, in fact, that SSIS wasn't installed. I installed it and now I get a different error:

Executed as user: WWDB\SYSTEM. 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: 3:48:29 PM Error: 2007-11-08 15:48:30.59 Code: 0x00000000 Source: Copy Data from AR1_CustomerMaster to Mas90Reporting dbo AR1_CustomerMaster Task Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:48:29 PM Finished: 3:48:30 PM Elapsed: 1.391 seconds. The package execution failed. The step failed.

It's weird, because there's no ExecuteDTS step in there. Every step during the migration was successfully converted to SSIS.

Aaron

John Welch SSC Veteran Points: 255 More actions · Answer 4

Have you opened the generated package in Business Intelligence Developers Studio (BIDS)? It's not uncommon for the migration tool to "convert" DTS packages by creating an Execute DTS Package task inside the SSIS package.

Aaron Edwards SSC Eights! Points: 834 More actions · Answer 5

Oh, you're right. Looks like it has. So what's the deal? What am I supposed to do here? Should I just re-create everything directly in SSIS?

Aaron

John Welch SSC Veteran Points: 255 More actions · Answer 6

Basically, it means there was something in the package that couldn't be converted. I would recommend recreating it in SSIS, since that will take more advantage of the new features. However, that will also mean more of a redesign, so it will take more time.