SSIS package job failing..??

  • Dear DBAs,

    I have SSIS package created for 10 tables to data flow from one server to another in different domains. But when i import / export the data and Ececute Immediatly its working fine with the user name and account. Instead executing Immediately when , i tried saving the file as SSIS package in server its fine till JOB creation.

    When i run this package its failed with the below error : (Even though has Login account).

    ERROR:

    Message

    Executed as user: ADMINISTRATOR\SYSTEM. ....00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:15:51 PM Error: 2009-05-29 16:16:21.10 Code: 0xC0202009 Source: GEOIND1_STORER Connection manager "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'wmsportaluser'.". End Error Error: 2009-05-29 16:16:21.10 Code: 0xC020801C Source: Data Flow Task Source - STORER [1] Descrip... The package execution fa... The step failed.

    Keep your inputs pls....

  • If you see a failed login attempt on the destination server, it is most likely a password issue. If you do not, then I suspect the package is never making a connection with the destination server.

  • When a job is run by the SQL Server Agent it is run as this user. This means that if you are using integrated security it will take on this user and that user may not have right on the target machine. Among many ways to change this is to supply a user name and password on the connection string or to make the user that SQL Server Agent is running on has access to both machines and both domains.

  • Thanks for the replies...

    Here the issue ie like : we have two servers (UK and US), with different login names. I mean to say user accounts.

    But when i run the import wizard and execute immediately its working fine with the two different login names for two different server (Sourec and the Destination) .

    BUt instead executing immediately, i saved that as a SSIS Package and running from destination server its throwing that error.

    Source server : Login account is not ADMIN (Read permissions)

    Destination server : ADMIN

  • What is running the package? The point is just because you can log into the SQL Server does not mean the windows domains are going to allow you to connect. When you are running the package your domain credentials are being supplied and you must have authority to both machines and domains. What ever is running the package must also have these domain credentials or be a local windows user on the machine.

  • Thanks for the reply.

    Its data flow of tables from one server to another in different regions.

    We dont have windows login to the both the machines ....

    Only one of the machines have the login on the box. Am running this package through job as sql server agent service account. Giving the credentials to run the package.

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

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