Solved

Scheduled SQL Server Agent Job "SSIS Package"  Failed.  Job Failed  Import Data from remote database

Posted on 2013-01-11
4
2,665 Views
Last Modified: 2016-02-10
Hi Experts,

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.  

Message
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 [1]     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.  

Job Failed:
Message
The job failed.  The Job was invoked by User DOMAIN\administrator.  The last step to run was step 1 (Reports).

Please help!  Thank you!

Don
0
Comment
Question by:TFHDIT
4 Comments
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 300 total points
ID: 38769282
Is this a permissions issue? => Yes
Is remote database logon/password written into the "SSIS package"? => No, not by default, you can do it but is in clear text somewhere then
Once set up as a job by default the package will run under the user that SQL Server Agent was set up with, evidently "DOMAIN\administrator" in your case.  If you can give that user access to the remote database, that might solve the problem.  There is also an option to "RUN AS" you can try playing with.
Permission issues with setting up and executing SSIS packages from Jobs always seems a little tricky to me, I have to work through issues.
0
 
LVL 42

Assisted Solution

by:Eugene Z
Eugene Z earned 100 total points
ID: 38769395
try to change the sql agent job owner to Sa
and\or
add DOMAIN\administrator to the destination sql logins with the db write\or dbo permissions
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 100 total points
ID: 38769555
Job Owner and the check REmote SQL Instance Credentials. Those have permissions to access the data or not.
0
 

Author Closing Comment

by:TFHDIT
ID: 38769602
Definitely a permission issue. All your recommendations helped me understand and successfully run the ssis package/SQL agent job.
Thank you!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question