Post reply

SSIS not running from SQL Job

  • November 23, 2011 at 4:01 am

    #251001

    Hi,

    I created a SSIS package which is running fine (if i run it from BI and if i double click the DTSX package itself) but when run it through a SQL Job it fails giving error "Unable to acuire connection", does anybody have any idea?

    "An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2011-11-23 05:19:14.32 Code: 0xC00291EC Source: Execute SQL Task(Ver1) Execute SQL Task Description: Failed to acquire connection "Ver1". Connection may not be configured correctly or you may not have the right permissions on this connection"

    thanks

    Puneet

  • November 23, 2011 at 4:17 am

    #1412053

    Puneet

    Two possibilities

    (1) The account under which SQL Server Agent is running does not have access to the server to which you're trying to connect

    (2) You are using Package Configurations, and SSIS is picking up a different connection string when you run the package from a job than it does when you run it from your desktop.

    John

  • November 23, 2011 at 4:38 am

    #1412062

    Thanks for your reply, i think point1 can be the issue.

    can you guide me how to check it if it has proper permission?

    (1) The account under which SQL Server Agent is running does not have access to the server to which you're trying to connect

  • November 23, 2011 at 6:55 am

    #1412152

    Connect to the target server with SSMS, open Security and then Logins. Do you see a login there either for the service account, or for a domain group of which it is a member? If not, you need to create one, and give it the necessary access to the database(s) that your package uses.

    John

  • November 23, 2011 at 6:58 am

    #1412156

    Thanks Again for your reply, i will try this also.

    but i did it other way, i created a batch file and use that batch file in SQL JOb and all goes well.

    Thanks

    Puneet

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

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