DTSX: to run or not to run:

Question

DTSX: to run or not to run:

John Esraelo-498130

SSCertifiable

Points: 5914
More actions
February 12, 2008 at 11:06 am

#154124

I have a dtsx that runs fine from command-line but not from a scheduled job [task] and I was wondering if there is anyone who might have this errors at some point.
The error in GUI as follows:
Source: Data Flow Task DTS.Pipeline Description: One or more component failed validation
End Error Error: 2008-02-12 09:49:35.84 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error
thx
John Esraelo
Cheers,
John Esraelo

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

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

Daniel Forrester 123 SSCrazy Points: 2276 More actions · Answer 1

Hi,

This is probably a security problem. When you run the DTS from the command prompt it will run under your user account. When you schedule a package to run as a scheduled job I could run as a different user and quite possibly the user account the SQL Server Agent is running as.On the Step in the scheduled job check the 'Run As' option and go from there.

Regards

Daniel

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 2

Hi Daniel,

You totally make sense, but, the agent is running under a service account that some half a dozen other tasks are running under.

Additionally, when the "type" is a "sql server integration services package" then automatically the "run as" changes to "sql agent service account".

🙁

John Esraelo

Cheers,
John Esraelo

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 3

One more thing.

The XML code shows (in BI) that the encryption was set to 1 but I cannot find that anywhere in the GUI so I can change that, and, I don't want to make the change in the code.

Make sense?

John Esraelo

Cheers,
John Esraelo

Daniel Forrester 123 SSCrazy Points: 2276 More actions · Answer 4

Hi John,

This makes perfect sense now. In there is a ProtectionLevel property which by default (I think) encrypts the sensitive data (ie passwords) with a user specific key. The package was created by your user account so the 'Key' is available to decrypt the sensitive data when you run it from the command prompt. When the packge runs as a scheduled job as the Sql Server agent the 'User key' isn't available and therefore SSIS can't decrypt the passwords saved in your packages to connect to the databases.

The way to get arround this is to either use Integrated login to connect to the database or what I usually which is the set the connection string at runtime using a package variable which I pickup from a configuration.

Cheers

Daniel

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 5

Alright, so, since I am not as good as you are in that area, I will have to read your posting couple of more times in different speeds + google a few + upgrade my sql for dummies and perhaps at that point I will successfully do what you just recommended..

thx a bunch..

John Esraelo

Cheers,
John Esraelo

Daniel Forrester 123 SSCrazy Points: 2276 More actions · Answer 6

Hi John,

I think everybody hits this issue at some point when deploy their packages. The only option under PackageProtection which I have found useable is the DoNotSaveSensitive. I just did a quick search on google and found this which almost identical to how I do it.

http://www.mssqltips.com/tip.asp?tip=1405

Good luck

Daniel

Half Abude SSC Enthusiast Points: 158 More actions · Answer 7

Hi Daniel,

in case you don't know, you can add a Credential and a Proxy to SQL Server, so you can run a package (within a job) under an account other than SQL Service Account.