Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-01-11
4
Medium Priority
?
2,758 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 27

Accepted Solution

by:
Chris Luttrell earned 1200 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 400 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 400 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

636 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