Cannot get data during ssis

Question

Cannot get data during ssis

David Kuhn-248064

Newbie

Points: 1
More actions
August 21, 2006 at 8:23 am

#114614

I have a SSIS package that transfers data from a Progress database (Held on another server) into a SQL Server database. The connection to Progress uses the Merant driver.
The package drops a table in SQL Server, recreates that table in SQL Server, and then transfers the data from Progress to SQL Server.
The package works perfectly fine when running in SSIS development.
When i set it up as a job and run it, it gives me an error about the user (the SQL Agent services user) does not have access.
What would be causing this. Could it be that the user for the SQL Agent does might not have access to the Server that holds the Progress database? I'm at a loss...not really sure what to do about this.
Any help is very welcome...
Thanks,
David

Viewing 5 posts - 1 through 4 (of 4 total)

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

Grant Bradley SSC Enthusiast Points: 193 More actions · Answer 1

Hi

I am having the same problem. I have gone thru and checked all the permissions that I can find including the agent and still no luck. I am doing this test to get ready to upgrade to 2005 and this it the one hicup.

Help

Thanks

Grant

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 2

While I don't know anything about Progress, it does sound like you need to check that it had access rights.

Is the Progress DB on a different server?

What user is specified in the connection to the Progress database inside the SSIS - is it using integrated security? If so, then when you run as BIDS, it uses your user - so start trying to see what the differences between your users and the Server Agent user account are.

Is the SQL Server Agent running on a domain account or a local one?

Grant Bradley SSC Enthusiast Points: 193 More actions · Answer 3

I finally got my problem solved. I ended up changing the default setting of save encrypted password on the ssis package to use storage level security.

Why this has to be so hard I don't know.

Thanks

Grant

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 4

Welcome to the fairly NON-exclusive club of people who have used SSIS, and had struggles with ProtectionLevel.

In years to come, we'll look back and laugh ... or maybe cry...