Solved

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

Posted on 2013-01-11
4
2,673 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 43

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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 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