For Each Enumerator

Question

For Each Enumerator

matthew.nott

SSC Veteran

Points: 209
More actions
February 4, 2009 at 4:24 am

#128449

Hi all,
I have a problem. I've created an SSIS package, the first step of which copies files over from a different server.
When I execute this in Visual Studio it runs fine setting the folder to be the server location i.e. \\IPADDRESS\f$\etc
When I build this package and import it in to integration services and select run package it also runs fine and copies the files over from the other server.
However, when I schedule this as a job on the agent, I get no error but he files are not copied over. I turned on logging in the package and it returns a message of "The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty".
The files are definitely there as they are copied over when running from Visual Studio!
I'd be really greatfull if someone could possibly suggest a reason why this happens.
Thanks in advance
Matt

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

MY guess is the operator for the Job does not have rights to the folder or folders being accessed. Sounds like you may need to lookup PROXY Accounts in BOL.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

matthew.nott SSC Veteran Points: 209 More actions · Answer 2

Hi Jack,

I've tried setting up a credential and a proxy for SSIS and executing the step under the proxy and I get an error about password:

Unable to start execution of step 1 (reason: Error authenticating proxy SYSTEMS\ADM-N7557784, system error: ConnGetProxyPassword). The step failed.

Any ideas?

Cheers

Matt

Tim Wilson-Brown Ten Centuries Points: 1395 More actions · Answer 3

It sounds like the package can't access the password because it's encrypted.

You may need to look at the Security -> ProtectionLevel on the Package.

Since you're running under a different user, Encrypt...WithUserKey will cause your passwords to be inaccessible on the server. You can't enter a password - unless you like insecure command lines - so Encrypt...WithPassword is no good to you either.

So you can either deploy to SQL Server and use ServerStorage, or deploy to a filesystem and use a package configuration to supply the password.

nmorrical Newbie Points: 3 More actions · Answer 4

Matt, did giving permission to your SQL Server Agent runtime user fix the issue you were facing?

I'm facing the same problem and would like to know if you found a resolution.

amy3672 SSC Veteran Points: 275 More actions · Answer 5

Hey Matt,

Have you got any solution for this problem. I'm facing the same issue.

Thanks,

nick.mcdermaid Hall of Fame Points: 3809 More actions · Answer 6

I read this page here in detail (rather than speed reading it like usual)

http://msdn.microsoft.com/en-us/library/ms189522.aspx

It implies that when you create your credential, if it is a windows credential, you need to put your windows password into 'secret'

I had created a credential with a blank secret via T-SQL, as SMSS wouldn't let me enter a blank one..... for good reason!

After I put my windows password in (via SMSS this time), the error no longer appeared and my job ran successfully.

There is no indication of what happens when this password changes.

steve block SSCertifiable Points: 7698 More actions · Answer 7

This may not apply to you but when I Import Packages into MSDB, I set Security to Server Storage and this can solve problems of running from a job.

Steve

nick.mcdermaid Hall of Fame Points: 3809 More actions · Answer 8

Let me clarify: the error "Error authenticating proxy SYSTEMS\ADM-N7557784, system error: ConnGetProxyPassword" has nothing to do with package security level.

There certainly are numerous other errors to do with using the wrong package security level but I don't believe this is one of them.

steve block SSCertifiable Points: 7698 More actions · Answer 9

I wasn't sure but I thought it was worth a shot just in case it might help. I think we have a tendancy that when we find a solution to an annoying problem we think it will solve all kinds of other problems. Hope you find a solution.

Steve

vani_r14 SSCrazy Points: 2843 More actions · Answer 10

sorry i guess i followed on with the post before...

nick.mcdermaid Hall of Fame Points: 3809 More actions · Answer 11

Let me reiterate

The error "Error authenticating proxy SYSTEMS\ADM-N7557784, system error: ConnGetProxyPassword" has nothing to do with package encryption levels.

There is a lot of misunderstanding and misinformation about package encryption levels and I would like to reiterate that the error above has absolutely nothing to do with package encryption.

A credential set up in SQL Server (which is what that error refers to) does not have its password encrypted via SSIS. It is totally external to SSIS and has nothing to do with SSIS encryption.

There are error messages similiar to that in SSIS which ARE due to encryption levels but that isn't one of them.