Package wont run in a scheduled job

Hello,

I have put together a simple SSIS package that runs fine both via BIDS and via Mgt Studio.

The problem I have is that I can't get a scheduled job to run it. My scheduled job has only 1 step - and that step runs the package.

When I right click in Mgt Studio (SQL Agent) and choose "Start Job" I get this error :

Executed as user: NMR044BRASQL886\SYSTEM. ....3033.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 14:10:56 Error: 2007-01-09 14:10:56.50 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" 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-01-09 14:10:57.04 Code: 0xC0202009 Source: B2B_Sales_Territory_Send_Back Connection manager "b2b_datamart" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". End Error Error: 2007-01-09 14:10:57.04 Code: 0xC020801C... The package execution fa... The step failed.

Can anyone help please?
I'm new to SSIS so clear+simple answers appreciated.

Thanks in advance
Mgale1

Check the ProtectionLevel proeprty of your package. My guess is that it is set to EncryptSensitiveWithUserKey. Is that correct?

This means that all passwords and such are encrypted with the username of the person that built the package hence when you run it as another user (which you are attempting to do here), it doesn't work. Read in BOL about the ProtectionLevel property to get a full understanding and then reply here with any problems.

-Jamie

|||

Hi Mgale1,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey is most likely flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

|||

You may want to check this KB article:

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/kb/918760

|||

Admin,

This topic is a good candidate for a "sticky" for the forum.

This question is asked once a day... we (the forum) could come up with a synopsis on package encryption.

|||

I always use ProtectionLevel=DontSaveSensitive. Without exception.

This actually forces you to use configurations - and that is no bad thing in my opinion.

-Jamie

Package structure in SSIS

Hi

I have been working with DTC packages together with analysis service, and are

now begining on SSIS.

But I have some demands that's been giving me headache before, so I hope

there's a solution in the SSIS for the following.

My DTC packages was build to be executed i 5 steps/Packages

1. Transfeer data from my Oracle 9.2.0.4 to SQL 2000

2. Transform data

3. Quality check

4. Process dimension

5. Process cubes

BUT in 2005, I want a little more flexibilty. I want to execute smaller

packages, let's say, only my "general ledger".

This way I will be able to update some of my important cubes quicker. And

not wait until all data

is transferred.

So what I have done until now is that I have created SSIS packages per module. I

have a General_ledger.dtsx and a Sales.dtsx. Which I can execute separately.

I also have a MasterFlow.dtsx which contain an "Execute package Task"

for each package.

I can run the MasterFlow and it will run through all packages once every night.

Each package contains

1. Truncate package tabels

2. Transfer package

tabels

3. Execute dimensions

data

4. Execute cubes

5. Process

Dimensions

6. Process Cubes

Is this the best way?

I'm not satisfied

myself because,

when I run MasterFlow. I would like to execute all

"step 1" in all packages, before "step 2" and so forth.

Then I would be sure that all data is OK when all Cubes are processed, if I

have a virtual cube that contains data from 2 packages.

How would you design this kind of setup?

Thanx in adv.

Best Regards

Bjarne R Mikkelsen

A-TEX A/S

Why not have a third child package that processes the cubes? then you only have to execute the cube process upon successful completion of everything else.

Or, in each child package you could process the measure group (I'm assuming you are using AS2005) relevant to teh data that you have just transformed.

Your approach up to now seems pretty sound to me.

-Jamie

|||Thank you.

The processing of dimension and cubes are the least time consuming, so it would be a possibility, simply to arrange these in a 3. child package, which will be executed everythime a single package is run.

/Bjarne