Post reply

Cannot get data during ssis

  • 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

  • August 22, 2006 at 8:14 am

    #656114

    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

  • August 23, 2006 at 3:56 am

    #656304

    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?

  • August 23, 2006 at 7:36 am

    #656358

    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

  • August 23, 2006 at 8:19 am

    #656369

    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...

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

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