Scheduled SQL Server Agent Job "SSIS Package" Failed. Job Failed Import Data from remote database
Posted on 2013-01-11
I imported data into a "test database" using "Import Data" wizard, the "Data Source" is a remote database, with "read-only permissions".
>I chose the "Write a query to specify the data transfer" so I can only import certain tables.
>Then I chose to "Save" the "SSIS Package" so I can later use it to create a scheduled SQL server agent job. I also chose to "Execute Immediately".
I was able to import the data to the "test database" successfully!
But here is the problem. I apologize! I am rookie at this! My goal is to schedule an "Import Data" from the remote read-only database to the local "test database".
I created a scheduled job to run the "SSIS package". Unfortunately it failed and gave me this error.
Executed as user: DOMAIN\administrator. ...00.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:17:44 PM Error: 2013-01-11 15:17:45.07 Code: 0xC0202009 Source: Reports 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: "Login failed for the user.". End Error Error: 2013-01-11 15:17:45.07 Code: 0xC020801C Source: Data Flow Task Source - Query  Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2013-01... The package execution fa... The step failed.
Is remote database logon/password written into the "SSIS package"?
Is this a permissions issue?
To summarize this issue:
I am able to successfuly "Import Data" from a remote database into the local database.
I used the saved SSIS package from the "Import Data" to create a schedule SQL Server agent job.
The job failed. The Job was invoked by User DOMAIN\administrator. The last step to run was step 1 (Reports).
Please help! Thank you!